Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Tookum
Starting Member
2 Posts |
 Posted - 2013-05-20 : 16:26:35
|
| HiWe use an interface to run our scripts at work and the interface won't allow us to use views or other more advanced techniques.I am taking data from 3 different tables, no problem. My problem is that I have to get the department field value that is valid within work_date_f and work_date_t. The department has a current_department_date_f and current_department_date_t.Is this possible without using views or other more advanced techniques?Sometimes a person can work both a day shift and later a night shift, smae date but at different department.Thanks,Tookums SELECT DISTINCT wt.id_nr, p.last_name, p.first_name, cd.department, wt.work_date_f, wt.work_time_f, wt.work_date_t, wt.work_time_t, wt.work_time_min, wt.type_of_work FROM work_time AS wt, persons AS p, current_department AS cd WHERE wt.type_of_work = 'ZZ' AND wt.work_date BETWEEN '20130101' AND '201305017' AND ( p.id_nr = wt.id_nr AND cd.id_nr = wt.id_nr ) AND wt.work_date_f >= cd.current_department_date_f AND wt.work_date_f <= cd.current_department_date_t ORDER BY wt.work_date_f, wt.work_time_f, cd.department, wt.id_nr; |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-20 : 16:48:32
|
| Anything that you would normally do using a view can be done without using a view. That said, it is not clear to me the logic you want to use. Can you post some sample data that is representative of your problem along with the corresponding results that you are trying to get? |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-21 : 04:52:45
|
| form what i understood you need a correlated query which will be used to relate to department table based on date values to get the associated department value. You could link it to main query using APPLY operator.See examples herehttp://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
We use an interface to run our scripts...