CODEWITHSUNDEEP
sqlsql-server
whitedolphin
whitedolphin

Reputation: 3

Weekly count for 2 different date columns in SQL Server

I have a table of tickets that have submit date and complete date.

Something like this:

Ticket   |  Submit_Date  |  Complete_Date
---------+---------------+-----------------
001      |   05/09/2020  |   05/09/2020
123      |   05/13/2020  |   05/16/2020
456      |   05/15/2020  |   05/22/2020
789      |   05/21/2020  |   05/23/2020

I need a count for how many were submitted each week and how many were completed each week.

The week number I can get with DatePart(ww,'<some date here>').

I can pull up one or the other but cannot figure out how to do both.

For the table above I would like to get something like this:

week |         range         | Submitted | Worked | 
-----+-----------------------+-----------+--------+
19   | 05/03/2020-05/09/2020 |     1     |    1   |
20   | 05/10/2020-05/16/2020 |     2     |    1   |
21   | 05/17/2020-05/23/2020 |     1     |    2   |

Any help is appreciated.

Upvotes: 0

Views: 95

Answers (2)

Srinika Pinnaduwage
Srinika Pinnaduwage

Reputation: 1042

Consider the 2 cases as 2 separate queries and join those 2. Here is what I got - disregarding the range (which I left it to you as you didn't ask for it)

;With tCTE as
(select DATEPART(ww,Submit_Date) as wkNumSubmit, DATEPART(ww,Complete_Date) as wkNumComplete,   * from t) 

Select * from
(select wkNumSubmit, Count(distinct Submit_Date) as Submitted from tCTE Group By wkNumSubmit) s
inner join
(select wkNumComplete, Count(distinct Complete_Date) as Worked from tCTE Group By wkNumComplete) w
on s.wkNumSubmit = w.wkNumComplete

Here is the fiddle

To add all week numbers (consider all dates) here is what I suggest

;With tCTE   as
(select DATEPART(ww,Submit_Date) as wkNumSubmit, DATEPART(ww,Complete_Date) as wkNumComplete,   * from t),
 allDays (wkNum) as
(Select wkNumSubmit from tCTE Union Select wkNumComplete from tCTE )

Select * from allDays a
Left Join (select wkNumSubmit, Count(distinct Submit_Date) as Submitted from tCTE Group By wkNumSubmit) 
    as s on a.wkNum = s.wkNumSubmit
Left Join (select wkNumComplete, Count(distinct Complete_Date) as Worked from tCTE Group By wkNumComplete) 
    as w on a.wkNum = w.wkNumComplete

Here is the fiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can unpivot using apply and then aggregate. Getting the week is a little tricky:

select v2.week, sum(is_submit), sum(is_complete)
from t cross apply
     (values (submit_date, 1, 0), (complete_date, 0, 1)
     ) v(dte, is_submit, is_complete) cross apply
     (values (dateadd(week, datediff(week, 0, v.dte), 0))) v2(week)
group by v2.week
order by v2.week;

Here is a db<>fiddle.

Upvotes: 1

Related Questions