I have a table with two columns in question.
What I'm trying to do is to compare weeks to see how many times an EmpID has repeated. For example:
Week1 is my base (starting date range e.g. BETWEEN '2017-07-22' AND '2017-07-29 23:59:59.993'). I now want to compare week2 against week1. If an EmpID repeats in week2 I was to see a count of 2 and if it appears for the first time in week2 then a count of 1.
Moving on to week3. If the EmpID appears in week1, week2 and week3 then I want to see a count of 3, if it only appears in week2 and week3 then a count of 2 and if it only appears in week3 then a count of 1.
And finally for week4. If the EmpID appears in week1, week2, week3 and week4 then I want to see a count of 4. If the EmpID appears in week2, week3 and week4 then a count of 3, if it appears in week3 and week4 then a count of 2 and if it only appears for the first time in week4 then a count of 1.
Any help will be greatly appreciated.
Added what I've tried so far but not getting the desired results.
select t.emp_id,
(select count(emp_id)
from [Vacation Audit Care 2017]
WHERE ((UPLOAD_DATE BETWEEN '2017-07-22' AND '2017-07-29 23:59:59.993') or(UPLOAD_DATE BETWEEN '2017-07-29' AND '2017-08-05 23:59:59.993') or (UPLOAD_DATE BETWEEN '2017-08-12' AND '2017-08-19 23:59:59.993'))
and emp_id=t.emp_id) as counts
from [Vacation Audit Care 2017] t
group by t.emp_id
order by counts desc
sample data
╔═════════════════╦═════════╗
║ Time_Stamp ║ Emp_ID ║
║ 7/20/2017 19:40 ║ 3140340 ║
║ 7/20/2017 19:40 ║ 2000950 ║
║ 7/20/2017 19:40 ║ 3118410 ║
║ 7/20/2017 19:40 ║ 311840 ║
║ 7/23/2017 21:19 ║ 3140340 ║
║ 7/23/2017 21:19 ║ 2000950 ║
║ 7/23/2017 21:19 ║ 3118410 ║
║ 7/23/2017 21:19 ║ 3124160 ║
║ 7/30/2017 7:00 ║ 3140340 ║
║ 7/30/2017 7:00 ║ 2000950 ║
║ 7/30/2017 7:00 ║ 3118410 ║
║ 7/30/2017 7:00 ║ 311840 ║
║ 8/6/2017 12:00 ║ 3140340 ║
║ 8/6/2017 12:00 ║ 3118410 ║
║ 8/6/2017 12:00 ║ 3124160 ║
║ 8/6/2017 12:00 ║ 311840 ║
║ 8/13/2017 12:00 ║ 3140340 ║
║ 8/13/2017 12:00 ║ 3118410 ║
║ 8/13/2017 12:00 ║ 3124160 ║
║ 8/13/2017 12:00 ║ 311840 ║
╚═════════════════╩═════════╝
Expected output
╔═════════╦════════╦═══════╦════════╗
║ ║ 30-Jul ║ 6-Aug ║ 13-Aug ║
║ emp_id ║ wk2 ║ wk3 ║ wk4 ║
║ 3140340 ║ 2 ║ 3 ║ 4 ║
║ 2000950 ║ 2 ║ ║ ║
║ 3118410 ║ 2 ║ 3 ║ 4 ║
║ 311840 ║ 1 ║ 2 ║ 3 ║
║ 3124160 ║ ║ 1 ║ 2 ║
╚═════════╩════════╩═══════╩════════╝
As per the expected output using 7/23 as my base (week1) the table shows the expected data results. In week2 311840 appears for the first time so I expect a count of 1.
The following week (8/6) 311840 appears once in wk2 and once in wk3 so I expect a count of 2 while 3124160 appears for the first time so I expect a count of 1 etc.
This will give results in the format that you show in your sample. I've included an extra week before and after in the output. To remove those extra weeks just remove [29] and [33] from the pivot. Comment out the pivot line to just get the underlying data.
;WITH
weekcounts AS (
SELECT Time_Stamp, Emp_ID, DATEPART(week, Time_Stamp) AS int_week FROM sampleData
)
,counts AS (
SELECT Emp_ID, int_week, 1 AS int_count
FROM weekcounts
UNION ALL
SELECT weekcounts.Emp_ID, weekcounts.int_week, 1 AS int_count
FROM weekcounts
INNER JOIN counts
ON weekcounts.Emp_ID = counts.Emp_ID
AND (weekcounts.int_week - 1) = counts.int_week
)
,countsagg AS (
SELECT Emp_ID, int_week, SUM(int_count) AS int_count
FROM counts
GROUP BY Emp_ID, int_week
)
SELECT * FROM countsagg
PIVOT (MAX(int_count) FOR int_week IN ([29],[30],[31],[32],[33])) piv