Levi Martin
asked on
Employee active status for all Mondays in the year.
Interested in determining the active status of all of my employees for every Monday over a running two year (two years back from today) period.
I currently have the two code snippets:
(1) Direct selection of employees:
(2) CTE determining all Mondays for a quarter within a year:
My question is how could I get a structure that would tell me each employee's active status in a 1 or 0 bit for each Monday over the last two years looking something like:
EmployeeNo, Date, bitStatus
70123,2013-01-07 00:00:00.000, 0
70123,2013-01-14 00:00:00.000, 1
70123,2013-01-21 00:00:00.000, 1
71230,2013-01-07 00:00:00.000, 1
71230,2013-01-14 00:00:00.000, 1
71230,2013-01-21 00:00:00.000, 0
72301,2013-01-07 00:00:00.000, 1
72301,2013-01-14 00:00:00.000, 0
72301,2013-01-21 00:00:00.000, 0
THANK YOU so much for any and all help you're able to lend!!
I currently have the two code snippets:
(1) Direct selection of employees:
SELECT employee_nbr,
date_hire,
term_date
FROM lawson_temp
(2) CTE determining all Mondays for a quarter within a year:
;WITH Tally AS
(SELECT TOP 366 n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.columns)
SELECT Mondays = AllDays
FROM Tally
CROSS APPLY (SELECT DATEADD(day, n, 2013)) x(AllDays)
WHERE (DATEPART(Quarter, AllDays) = 1) AND
(DATENAME(Weekday, AllDays) = 'Monday') AND
(DATEPART(YEAR, AllDays) = 2013)
My question is how could I get a structure that would tell me each employee's active status in a 1 or 0 bit for each Monday over the last two years looking something like:
EmployeeNo, Date, bitStatus
70123,2013-01-07 00:00:00.000, 0
70123,2013-01-14 00:00:00.000, 1
70123,2013-01-21 00:00:00.000, 1
71230,2013-01-07 00:00:00.000, 1
71230,2013-01-14 00:00:00.000, 1
71230,2013-01-21 00:00:00.000, 0
72301,2013-01-07 00:00:00.000, 1
72301,2013-01-14 00:00:00.000, 0
72301,2013-01-21 00:00:00.000, 0
THANK YOU so much for any and all help you're able to lend!!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
By building a CTE backwards from the most recent Monday, of only Monday dates, for the past 2 years, then count these per employee where the Monday is after or equal to date_hire and a Monday is before term_date (or term_date is null)
if term_date itself is considered active, include <= in line 27 below
if term_date itself is considered active, include <= in line 27 below
-- set number of years
declare @numYears as int
set @numYears = 2
-- next get recent Monday
declare @BeginsAt as datetime
-- truncate time from getdate()
set @BeginsAt = dateadd(day, datediff(day,0, getdate() ), 0)
-- get Monday using modulus
set @BeginsAt = dateadd(day,(-datediff(day,0,@BeginsAt) % 7),@BeginsAt)
;with Ranger (id, StartAt)
as (
/* recursively build CTE of Mondays */
select 1 as id, @BeginsAt
union all
select (id + 1) , dateadd(day,-7,StartAt)
from Ranger
where dateadd(day,-7,StartAt) >= dateadd(year,-@numYears,@BeginsAt)
)
select
employee_nbr
/* count number of Mondays over span date_hire ... term_date */
, count( case
when startat >= date_hire and startat < term_date then startat
when startat >= date_hire and term_date is null then startat
end
) as active_mondays
, min(date_hire) as date_hire
, max(term_date) as term_date
from Ranger
cross join lawson_temp
group by
employee_nbr
OPTION ( MAXRECURSION 110 ) -- >= (@Numyears * 55)
available to try at http://sqlfiddle.com/#!3/f6859/2
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you to all three of you for your quick feedback and shared expertise! It was very, very helpful to see the two options and to educate myself from there...will defintiely make me a better T-SQL developer! THANK YOU!!
The CTEs are a great idea that work very well but more code than I was hoping to implement into my current query structures; the solution provided by DOSLover was concise. Modified the date comparison and opened query to count rolling decade as such:
SELECT a.employee_nbr,
a.date_hire,
a.term_date,
AllDays,
(CASE WHEN AllDays >= a.Date_Hire OR AllDays <= a.term_date THEN 1 ELSE 0 END) AS isActive
FROM lawson_temp a,
(SELECT AllDays
FROM (SELECT TOP 3651 n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.columns) As Tally
CROSS APPLY (SELECT DATEADD(day, -n, GETDATE() )) x(AllDays)
WHERE (DATENAME(Weekday, AllDays) = 'Monday')) MyDays
ORDER BY a.employee_nbr
The CTEs are a great idea that work very well but more code than I was hoping to implement into my current query structures; the solution provided by DOSLover was concise. Modified the date comparison and opened query to count rolling decade as such:
SELECT a.employee_nbr,
a.date_hire,
a.term_date,
AllDays,
(CASE WHEN AllDays >= a.Date_Hire OR AllDays <= a.term_date THEN 1 ELSE 0 END) AS isActive
FROM lawson_temp a,
(SELECT AllDays
FROM (SELECT TOP 3651 n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.columns) As Tally
CROSS APPLY (SELECT DATEADD(day, -n, GETDATE() )) x(AllDays)
WHERE (DATENAME(Weekday, AllDays) = 'Monday')) MyDays
ORDER BY a.employee_nbr
don't you need to cater for a NULL term_date?
most HR systems I've worked with have that field as NULL (until a termination occurs)
most HR systems I've worked with have that field as NULL (until a termination occurs)
ASKER
Yes, I added that back after my point award post this morning as such:
SELECT TOP (100) PERCENT lawson_temp.employee_nbr AS EmployeeNo,
(CASE WHEN lawson_temp.adjusted_servi ce_date IS NULL THEN lawson_temp.date_hire ELSE lawson_temp.adjusted_servi ce_date END) AS HireDate,
lawson_temp.term_date AS TermDate,
AllDays AS MondayDate,
(CASE WHEN AllDays >= lawson_temp.date_hire AND (lawson_temp.term_date IS NULL OR AllDays <= lawson_temp.term_date) THEN 1 ELSE 0 END) AS ActiveStatus
FROM lawson_temp,
(SELECT AllDays
FROM (SELECT TOP 3651 n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.columns) AS Tally
CROSS APPLY (SELECT DATEADD(DAY, -n, GETDATE() )) x(AllDays)
WHERE (DATENAME(Weekday, AllDays) = 'Monday')) MyDays
ORDER BY lawson_temp.employee_nbr
Great catch!
SELECT TOP (100) PERCENT lawson_temp.employee_nbr AS EmployeeNo,
(CASE WHEN lawson_temp.adjusted_servi
lawson_temp.term_date AS TermDate,
AllDays AS MondayDate,
(CASE WHEN AllDays >= lawson_temp.date_hire AND (lawson_temp.term_date IS NULL OR AllDays <= lawson_temp.term_date) THEN 1 ELSE 0 END) AS ActiveStatus
FROM lawson_temp,
(SELECT AllDays
FROM (SELECT TOP 3651 n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.columns) AS Tally
CROSS APPLY (SELECT DATEADD(DAY, -n, GETDATE() )) x(AllDays)
WHERE (DATENAME(Weekday, AllDays) = 'Monday')) MyDays
ORDER BY lawson_temp.employee_nbr
Great catch!
I had the NULL check for term_date in my initial query.
CTE disqualification kind of ironic, since I'm not a big fan of CTEs ... I used them only because you used one in your initial post :-) .
CTE disqualification kind of ironic, since I'm not a big fan of CTEs ... I used them only because you used one in your initial post :-) .
ASKER
Great post too; very appreciative! Perhaps I could have provided a better distro of awarded points?
Taking out the CTE wasn't an initial thought of mine but the latest post from DOSLover provided me something I hadn't thought of which placed easier into my reporting. I've had many developers lean me away from CTEs and the solution was great...every post helped me learn which I have much to do.
Taking out the CTE wasn't an initial thought of mine but the latest post from DOSLover provided me something I hadn't thought of which placed easier into my reporting. I've had many developers lean me away from CTEs and the solution was great...every post helped me learn which I have much to do.
there is another factor to consider:
CROSS APPLY (SELECT DATEADD(DAY, -n, GETDATE() )) x(AllDays)
that does not strip time back to 00:00:00 from getdate() which may produce unwanted results
you could add to that logic by:
CROSS APPLY (SELECT DATEADD(DAY, -n, dateadd(day, datediff(day,0, GETDATE() ), 0) )) x(AllDays)
for the heck of it: an alternative without CTE, using master.dbo.spt_values which returns just Mondays for the past 2 years
CROSS APPLY (SELECT DATEADD(DAY, -n, GETDATE() )) x(AllDays)
that does not strip time back to 00:00:00 from getdate() which may produce unwanted results
you could add to that logic by:
CROSS APPLY (SELECT DATEADD(DAY, -n, dateadd(day, datediff(day,0, GETDATE() ), 0) )) x(AllDays)
for the heck of it: an alternative without CTE, using master.dbo.spt_values which returns just Mondays for the past 2 years
SELECT TOP (100) PERCENT -- I am dubious about the use of TOP 100 PERCENT
lawson_temp.employee_nbr AS EmployeeNo
, (
CASE
WHEN lawson_temp.adjusted_service_date IS NULL
THEN lawson_temp.date_hire
ELSE lawson_temp.adjusted_service_date
END
) AS HireDate
, lawson_temp.term_date AS TermDate
, MDays.MondayDate
, (
CASE WHEN MDays.MondayDate >= lawson_temp.date_hire
AND (
lawson_temp.term_date IS NULL
OR MDays.MondayDate <= lawson_temp.term_date
) THEN 1 ELSE 0 END
) AS ActiveStatus
FROM lawson_temp
CROSS JOIN (
SELECT
number
, dateadd(DAY, datediff(DAY,-7, getdate() ) - (number*7) - (datediff(DAY,0, getdate() ) % 7), 0) as MondayDate
FROM master.dbo.spt_values
WHERE TYPE = 'P'
AND number BETWEEN 1 AND datediff(DAY,dateadd(YEAR,-2,getdate()),getdate()) / 7
) as Mdays
ORDER BY lawson_temp.employee_nbr, MDays.MondayDate
http://sqlfiddle.com/#!3/1fa93/7846
Open in new window