(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 = AllDaysFROM 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:
SET @end_date = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SET @start_date = DATEADD(DAY, 1, DATEADD(YEAR, -2, @end_date))
;WITH cte_First_Monday AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(DAY, 6, @start_date)) / 7 * 7, 0) AS first_monday
),
cte_Digits AS (
SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9
),
cte_Tally AS (
SELECT ones.digit + tens.digit * 10 + hundreds.digit * 100 AS tally
FROM cte_Digits ones
CROSS JOIN cte_Digits tens
INNER JOIN cte_Digits hundreds ON
hundreds.digit <= (DATEDIFF(DAY, @start_date, @end_date) + 1) / 7
WHERE
ones.digit + tens.digit * 10 + hundreds.digit * 100 BETWEEN 0 AND (DATEDIFF(DAY, @start_date, @end_date) + 1) / 7
)
SELECT
lt.Employee_Nbr,
DATEADD(DAY, ct.tally * 7, cfm.first_monday) AS Monday_Date,
CASE WHEN lt.date_hire <= DATEADD(DAY, ct.tally * 7, cfm.first_monday)
AND (lt.term_date IS NULL OR lt.term_date >= DATEADD(DAY, ct.tally * 7, cfm.first_monday))
THEN 1 ELSE 0 END AS BitStatus,
lt.date_hire, --for testing, to verify BitStatus, remove later
lt.term_date --for testing, to verify BitStatus, remove later
FROM cte_Tally ct
CROSS JOIN cte_First_Monday cfm
LEFT OUTER JOIN #lawson_temp lt ON
lt.date_hire <= @end_date
--SELECT employee_nbr, date_hire, term_date FROM lawson_temp
ORDER BY
lt.employee_nbr, ct.tally
I will just use what you have already done, for getting Mondays. Then combine that with Lawson_Temp table. I also assume date_hire and term_date are in datetime format. Also please note that I am using 731 days for counting two years back from today, and removed condtions for quarter=1 and year=2013. Some of these will have to be tweaked based on your requirement. For example, Do you want to consider Term_Date as 'Active' date? Date comparison will have to be tweaked in that case.
Select a.Employee_Nbr ,a.Date_Hire, a.Term_Date , AllDays ,CASE WHEN AllDays >= a.Date_Hire and ALLDays <= a.Term_Date Then 1 ELSE 0 END as isActive from Lawson_Temp a, (SELECT AllDays FROM (SELECT TOP 731 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
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
-- set number of yearsdeclare @numYears as intset @numYears = 2-- next get recent Mondaydeclare @BeginsAt as datetime-- truncate time from getdate()set @BeginsAt = dateadd(day, datediff(day,0, getdate() ), 0)-- get Monday using modulusset @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_datefrom Rangercross join lawson_tempgroup by employee_nbrOPTION ( MAXRECURSION 110 ) -- >= (@Numyears * 55)
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Having posted the above I realize you didn't want a summary (sorry, bad assumption)
So here's the table of details using the same basic structure
-- set number of yearsdeclare @numYears as intset @numYears = 2-- next get recent Mondaydeclare @BeginsAt as datetime-- truncate time from getdate()set @BeginsAt = dateadd(day, datediff(day,0, getdate() ), 0)-- get Monday using modulusset @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 , startat as [Date] , case when startat >= date_hire and startat < term_date then 1 when startat >= date_hire and term_date is null then 1 else 0 end as bitStatusfrom Rangercross join lawson_temp-- order by bitStatus, employee_nbr -- as requiredOPTION ( MAXRECURSION 110 ) -- >= (@Numyears * 55)
After looking at previous post, I realize we need to account for Term_date that can be NULL. I am using <= for comparing against ter_date, because, an employee is technically 'Active', though partly, on the day of term_date. Here is the modified code:
Select a.Employee_Nbr ,a.Date_Hire, a.Term_Date , AllDays ,CASE WHEN AllDays >= a.Date_Hire and (ALLDays is NULL OR ALLDays <= a.Term_Date) Then 1 ELSE 0 END as isActive from Lawson_Temp a, (SELECT AllDays FROM (SELECT TOP 731 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
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
most HR systems I've worked with have that field as NULL (until a termination occurs)
0
Levi MartinSenior Data AnalystAuthor Commented:
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_service_date IS NULL THEN lawson_temp.date_hire ELSE lawson_temp.adjusted_service_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
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 :-) .
0
Levi MartinSenior Data AnalystAuthor Commented:
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.
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
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 ActiveStatusFROM lawson_tempCROSS 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 MdaysORDER BY lawson_temp.employee_nbr, MDays.MondayDate
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
For example:
IF OBJECT_ID(N'tempdb..#lawso
DROP TABLE #lawson_temp
CREATE TABLE #lawson_temp (
employee_nbr int,
date_hire datetime,
term_date datetime,
)
INSERT INTO #lawson_temp
SELECT 70123, '20110112', '20130511'
UNION ALL
SELECT 72301, '20120416', '20130118'
/*
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
*/
DECLARE @start_date datetime
DECLARE @end_date datetime
SET @end_date = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
SET @start_date = DATEADD(DAY, 1, DATEADD(YEAR, -2, @end_date))
;WITH cte_First_Monday AS (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(DAY, 6, @start_date)) / 7 * 7, 0) AS first_monday
),
cte_Digits AS (
SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9
),
cte_Tally AS (
SELECT ones.digit + tens.digit * 10 + hundreds.digit * 100 AS tally
FROM cte_Digits ones
CROSS JOIN cte_Digits tens
INNER JOIN cte_Digits hundreds ON
hundreds.digit <= (DATEDIFF(DAY, @start_date, @end_date) + 1) / 7
WHERE
ones.digit + tens.digit * 10 + hundreds.digit * 100 BETWEEN 0 AND (DATEDIFF(DAY, @start_date, @end_date) + 1) / 7
)
SELECT
lt.Employee_Nbr,
DATEADD(DAY, ct.tally * 7, cfm.first_monday) AS Monday_Date,
CASE WHEN lt.date_hire <= DATEADD(DAY, ct.tally * 7, cfm.first_monday)
AND (lt.term_date IS NULL OR lt.term_date >= DATEADD(DAY, ct.tally * 7, cfm.first_monday))
THEN 1 ELSE 0 END AS BitStatus,
lt.date_hire, --for testing, to verify BitStatus, remove later
lt.term_date --for testing, to verify BitStatus, remove later
FROM cte_Tally ct
CROSS JOIN cte_First_Monday cfm
LEFT OUTER JOIN #lawson_temp lt ON
lt.date_hire <= @end_date
--SELECT employee_nbr, date_hire, term_date FROM lawson_temp
ORDER BY
lt.employee_nbr, ct.tally