SQL Server to count how many times a value appears between multiple date ranges and compare to previous weeks

Question

I first asked a similar question at

SQL Server to count how many times a value appears between multiple date ranges

and wasn't sure if I should post a new question or modify the original post. Though it's building on the original post I felt it was different enough to render a new post - please advise if this was the correct thing to do.

I wasn't given a few solutions that worked well but unfortunately a caveat was thrown at me and I need to make some modifications based on an additional criteria and not able to solve it.

First off here again are some sample data and the expected output

|   Time_Stamp    | Emp_ID  | Balance | Hours |
|-----------------|---------|---------|-------|
| 7/16/2017 19:40 | 3140340 |    2250 |  37.5 |
| 7/16/2017 19:40 | 2000950 |    4050 |  67.5 |
| 7/16/2017 19:40 | 3118410 |     400 |   6.7 |
| 7/16/2017 19:40 |  311840 |   11700 |   195 |
| 7/23/2017 21:19 | 3140340 |    2250 |  37.5 |
| 7/23/2017 21:19 | 2000950 |    4050 |  67.5 |
| 7/23/2017 21:19 | 3118410 |     800 |  13.3 |
| 7/23/2017 21:19 | 3124160 |     450 |   7.5 |
| 7/23/2017 21:19 |  311840 |     400 |   6.7 |
| 7/30/2017 7:00  | 3140340 |    2250 |  37.5 |
| 7/30/2017 7:00  | 2000950 |     400 |   6.7 |
| 7/30/2017 7:00  | 3118410 |    1200 |    20 |
| 7/30/2017 7:00  |  311840 |     700 |  11.7 |
| 8/6/2017 12:00  | 3140340 |     444 |   7.4 |
| 8/6/2017 12:00  | 3118410 |     444 |   7.4 |
| 8/6/2017 12:00  | 3124160 |      90 |   1.5 |
| 8/6/2017 12:00  |  311840 |     325 |   5.4 |
| 8/13/2017 12:00 | 3140340 |     900 |    15 |
| 8/13/2017 12:00 | 3118410 |    1350 |  22.5 |
| 8/13/2017 12:00 | 3124160 |      90 |   1.5 |
| 8/13/2017 12:00 |  311840 |    1700 |  28.3 |

Expected output is as follows

|         | 16-Jul | 23-Jul | 30-Jul | 6-Aug | 13-Aug |
|---------|--------|--------|--------|-------|--------|
| emp_id  | wk1    | wk2    | wk3    | wk4   | wk5    |
| 3140340 | 1      | 2      | 3      | 0     | 1      |
| 2000950 | 1      | 2      | 0      | 0     | 0      |
| 3118410 | 0      | 1      | 2      | 0     | 1      |
| 311840  | 1      | 0      | 1      | 0     | 1      |
| 3124160 | 0      | 1      | 0      | 1     | 2      |

An important note - unfortunately the data type for balance (minutes) is in varchar and I need to convert it to hours by dividing by 60. I used the following for that

ROUND(CONVERT(varchar(50),CONVERT(float,([BALANCE]/convert(float,60.0)))),2) AS [Hours]

With that being said what I now need to accomplish is if the balance of each week is less than 7.5 hours then just put a 0 (zero).

If it is >= 7.5 hours then a count of 1. If consecutive weeks are >= 7.5 hours - for example 2 consecutive weeks then the first week will have a count of 1 and the 2nd week a count of 2. IF the 3rd week is below 7.5 hours then 0.

As stated earlier the original question along with the solutions can be found at SQL Server to count how many times a value appears between multiple date ranges

Two of the solutions that worked really well for the original question were

;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

and

; with wk_nbrs as
(
    --recursive CTE that generates the week numbers.
    -- 7/23 thru 7/29 is Week 1
    select cast('2017-07-23' as date) as wk_bgn
    , cast('2017-07-29' as date) as wk_end
    , 1 as wk_nbr
    union all
    select dateadd(d,7,fw.wk_bgn) as wk_bgn
    , dateadd(d,7,fw.wk_end) as wk_end
    , fw.wk_nbr + 1 as wk_nbr
    from wk_nbrs as fw
    where 1=1
    and fw.wk_nbr < 100
)
, emp_wk_cnt as
(
    --Getting the running total count of emp_id by week
    select a.emp_id
    , b.wk_nbr
    , count(*) over (partition by a.emp_id order by b.wk_nbr asc) as emp_wk_cnt
    from @emp_ts as a
    inner join wk_nbrs as b on cast(a.time_stamp as date) between b.wk_bgn and b.wk_end
    group by a.emp_id
    , b.wk_nbr  
)
--pivoting the results out to final expected output
select post.emp_id
, post.wk2
, post.wk3
, post.wk4
from (
select a.emp_id
, 'wk' + cast(a.wk_nbr as varchar(10)) as wk_nbr
, a.emp_wk_cnt
from emp_wk_cnt as a
) as pre
pivot (sum(pre.emp_wk_cnt) 
    for pre.wk_nbr in 
        ([wk2],[wk3],[wk4])
) post
order by post.emp_id

Any additional assistance with this will be greatly appreciated.

Thanks in advance.

Answer 1

I think this might give you what you are looking for:

;WITH 
weekcounts AS (
    SELECT Time_Stamp, Emp_ID, [Balance], ROUND(CONVERT(FLOAT,[Balance]) / 60.0,2) AS [Hours], DATEPART(week, Time_Stamp) AS int_week FROM newSampleData
)

,counts AS (
    SELECT Emp_ID, int_week, 1 AS int_count
    FROM weekcounts
    WHERE ([Hours] >= 7.5)
    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
            AND ([Hours] >= 7.5)
)

,countsagg AS (
    SELECT Emp_ID, int_week, SUM(int_count) AS int_count
    FROM counts
    GROUP BY Emp_ID, int_week
)

SELECT Emp_ID, 
       ISNULL([29],0) AS [week 29],
       ISNULL([30],0) AS [week 30],
       ISNULL([31],0) AS [week 31],
       ISNULL([32],0) AS [week 32],
       ISNULL([33],0) AS [week 33] 
FROM countsagg
PIVOT (MAX(int_count) FOR int_week IN ([29],[30],[31],[32],[33])) piv

Also if Balance is a varchar you are doing more converts than needed in your code provided. This will give the result for hours with less code:

ROUND(CONVERT(FLOAT,[Balance]) / 60.0,2)