SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date-bound SQL Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Nuke
Starting Member

5 Posts

Posted - 11/26/2013 :  07:21:31  Show Profile  Reply with Quote
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.


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

Thanks in advance of any help.

Edited by - Nuke on 11/26/2013 07:27:01

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/26/2013 :  07:46:19  Show Profile  Reply with Quote
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
Go to Top of Page

Nuke
Starting Member

5 Posts

Posted - 11/26/2013 :  07:49:46  Show Profile  Reply with Quote
Ok thanks, I wasn't sure on which join to put the date boundaries but I'll give it a stab.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/26/2013 :  07:56:12  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000