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-22 : 11:43:34

Hi All,

I have a Staff_ID that is mapped against a Team_ID in a table, with a start and end date. I maintain a team hierachy by also having a table with Team_ID matched to Team_ID with start and end dates.

There is no future end dates so any entry with no end date is current.

Can someone help me form a SQL query to get an individual's current Team Leader?

I think the logic is to find the ID of the staff member who is currently mapped against the team to which the subject staff member's current team currently reports.

I'm getting a bit tangled with the words, let alone the SQL.

Thanks in advance.

tblStaff - ID / Name
tblTeams - ID / Name
tblStaffMap - ID / Staff_ID / Team_ID / Start / End
tblTeamMap - ID / Team_ID / Parent_Team_ID / Start / End

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-22 : 13:01:35

quote:
Can someone help me form a SQL query to get an individual's current Team Leader?

Where are the Team Leaders listed? I see Staff_ID, which I assume is a team member. Is there another table that lists Team Leaders, or is there another column that identifies a Staff_Id as a Team Leader?

Nuke
Starting Member

5 Posts

Posted - 2013-11-23 : 04:47:24

The team map gives a very vertical structure and therefore I think I'm trying to get the staff member who belongs to the team that parents the team to which the subject staff member belongs. For example, in the Team Map, Ops1 parents Team1. If Staff1 is a member of Team1, and if Staff2 is a member of Ops1, then Staff2 is Staff1's team leader.

I may have set this up wrong, but I need the date-bound flexibility to move teams, and people around, and I didn't think I needed a seperate 'leadership' map?

Is there a better way of doing this?

Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-23 : 09:30:29

quote:
Originally posted by Nuke

The team map gives a very vertical structure and therefore I think I'm trying to get the staff member who belongs to the team that parents the team to which the subject staff member belongs. For example, in the Team Map, Ops1 parents Team1. If Staff1 is a member of Team1, and if Staff2 is a member of Ops1, then Staff2 is Staff1's team leader.

I may have set this up wrong, but I need the date-bound flexibility to move teams, and people around, and I didn't think I needed a seperate 'leadership' map?

Is there a better way of doing this?

Thanks for your help.

That will not work. As there might be multiple employees within each of the teams. So does that imply each of the employee in parent team will be team leader of every employee of child team? Ideally you should have atleast have TeamLeadID inside StaffMap table i guess.

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

Nuke
Starting Member

5 Posts

Posted - 2013-11-23 : 11:02:49

Ok, thanks for the advice. I'll look to re-do the tables when I get back to work on Monday, to include a team leader ID and team leader map. Are you able to help with the SQL query regardless, even if it returns more than one leader ID? - I'm particularly interested in how to make the date boundaries work where there are two elements with start and end dates.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-23 : 11:35:50

quote:
Originally posted by Nuke

Ok, thanks for the advice. I'll look to re-do the tables when I get back to work on Monday, to include a team leader ID and team leader map. Are you able to help with the SQL query regardless, even if it returns more than one leader ID? - I'm particularly interested in how to make the date boundaries work where there are two elements with start and end dates.

Thanks in advance.

something like this


SELECT s.Name AS StaffName,s1.Name AS LeaderName
FROM tblStaff s
INNER JOIN tblStaffMap sm
ON sm.Staff_ID = s.ID
INNER JOIN tblTeamMap tm
ON tm.Team_ID  = sm.Team_ID 
AND (sm.Start BETWEEN tm.Start AND tm.End
OR sm.End BETWEEN tm.Start AND tm.End)
INNER JOIn tblStaffMap sm1
ON sm1.Team_ID = tm.Parent_Team_ID
AND (sm1.Start BETWEEN tm.Start AND tm.End
OR sm1.End BETWEEN tm.Start AND tm.End)
INNER JOIN tblStaff s1
ON s1.ID = sm1.Staff_ID

------------------------------------------------------------------------------------------------------
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