Solved

Employee active status for all Mondays in the year.

Posted on 2013-06-19
11
460 Views
Last Modified: 2013-06-20
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:
SELECT	employee_nbr,
		date_hire,
		term_date 
FROM	lawson_temp

Open in new window


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

Open in new window


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!!
0
Comment
Question by:Levi Martin
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
ID: 39261109
You really only have to calculate the first Monday date -- other Mondays can be calc'd by adding 7 day mutliples to that date.

For example:


IF OBJECT_ID(N'tempdb..#lawson_temp') IS NOT NULL
    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
0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39261362
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

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39261904
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 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)

Open in new window

available to try at http://sqlfiddle.com/#!3/f6859/2
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 39261924
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 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
  , 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 bitStatus
from Ranger
cross join lawson_temp
-- order by bitStatus, employee_nbr -- as required

OPTION ( MAXRECURSION 110 ) --  >= (@Numyears * 55)

Open in new window

this variant http://sqlfiddle.com/#!3/f6859/4
0
 
LVL 5

Accepted Solution

by:
DOSLover earned 300 total points
ID: 39262750
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 

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Closing Comment

by:Levi Martin
ID: 39263224
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39264407
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)
0
 

Author Comment

by:Levi Martin
ID: 39264414
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  

ORDER BY      lawson_temp.employee_nbr

Great catch!
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39264418
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
 

Author Comment

by:Levi Martin
ID: 39264445
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39264589
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 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

Open in new window

http://sqlfiddle.com/#!3/1fa93/7846
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
grouping logic 6 47
Need to update TableA to TableB 6 34
MS SQL Pivot table help 4 13
SQL bit field not working as expected 3 19
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now