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

Author  Topic 

jmhc101
Starting Member

3 Posts

Posted - 2013-01-08 : 12:05:31
I have a table of employees with start and end dates. The end date is null if the employee is active. I need to summarise this data to show me the number of active employees at a given point in the month (eg 1st).

Source Data:
employeeID, startDate, endDate
1,20/12/2010,20/03/2011
2,17/01/2011,NULL
3,01/02/2011,20/02/2011

Desired output:
Date, volumeActive
01/01/2011,1
01/02/2011,3
01/03/2011,2

What's the most efficient method of querying this data in TSQL? The source table is large (c.1m records).

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-08 : 12:13:49
select *
from tbl
where d >= startdate and (d < enddate or enddate is null)

then you need the dates

with cte as
(
select d = convert(datetime,20110101')
union all
select d=dateadd(mm,1,d) from cte where d < '20110103'
)
select d, count(*)
from tbl
join cte
on d >= startdate and (d < enddate or enddate is null)
group by d

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-08 : 23:46:06
use a calendar table like

http://visakhm.blogspot.in/2010/02/generating-calendar-table.html



then do query like

SELECT f.[Date],t.cnt
FROM dbo,CalendarTable(@StartDate,@EndDate,0,1)f
CROSS APPLY (SELECT COUNT(*) AS cnt
FROM table
WHERE f.[Date]>= startDate
AND (f.[Date] <= endDate OR endDate IS NULL)
)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jmhc101
Starting Member

3 Posts

Posted - 2013-01-18 : 08:37:10
Thanks - both solutions worked and I've learnt something new.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-18 : 13:09:58
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -