Date-bound SQL Query - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date-bound SQL Query

Author

Topic

Nuke
Starting Member

5 Posts

Posted - 2013-11-26 : 07:21:31

Hi All,

I have the following query which returns staff-level non-productive activities by team. The StaffMap table simply maps staff IDs to team IDs (or groups), but has start and end dates.

Can someone help me modify the SQL to account for these start and end dates? Let's say I wanted activities between Jan-1 and Jan-10, but only for staff that were in the team at the time, with the possibility that the staff could switch teams in the reporting period.

[CODE]
SELECT
tblStaffMap.Staff_ID,
tblStaff.Name,
tblStaffMap.Group_ID,
tblGroups.Group_Name,
tblStaff.Minutes,
tblActivityLog.Start_Date,
tblActivities.Activity_Name,
tblActivityLog.Minutes
FROM
(((tblStaffMap INNER JOIN tblStaff ON tblStaffMap.Staff_ID = tblStaff.ID)
INNER JOIN tblActivityLog ON tblStaffMap.Staff_ID = tblActivityLog.Staff_ID)
INNER JOIN tblActivities ON tblActivityLog.Activity_ID = tblActivities.ID)
INNER JOIN tblGroups ON tblStaffMap.Group_ID = tblGroups.ID
WHERE (((tblActivities.Is_Productive)=False))
ORDER BY tblActivityLog.Start_Date
[/CODE]
Thanks in advance of any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-26 : 07:46:19

same as how I suggested here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=189849
See how date conditions were added to the joins

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Nuke
Starting Member

5 Posts

Posted - 2013-11-26 : 07:49:46

Ok thanks, I wasn't sure on which join to put the date boundaries but I'll give it a stab.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-26 : 07:56:12

quote:
Originally posted by Nuke

Ok thanks, I wasn't sure on which join to put the date boundaries but I'll give it a stab.

you can add it to joins or you may just add it to WHERE clause at the end as joins used here are all INNER JOINs.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources