SQL Server - Counting total number of days user had active contracts

user_18852872 0
Viewed 65

I want to count the number of days while user had active contract based on table with start and end dates for each service contract. I want to count the time of any activity, no matter if the customer had 1 or 5 contracts active at same time.

+---------+-------------+------------+------------+
| USER_ID | CONTRACT_ID | START_DATE |  END_DATE  |
+---------+-------------+------------+------------+
|       1 |          14 | 18.02.2021 | 18.04.2022 |
|       1 |          13 | 02.01.2019 | 02.01.2020 |
|       1 |          12 | 01.01.2018 | 01.01.2019 |
|       1 |          11 | 13.02.2017 | 13.02.2019 |
|       2 |          23 | 19.06.2021 | 18.04.2022 |
|       2 |          22 | 01.07.2019 | 01.07.2020 |
|       2 |          21 | 19.01.2019 | 19.01.2020 |
+---------+-------------+------------+------------+

In result I want a table:

+---------+--------------------+
| USER_ID | DAYS_BEEING_ACTIVE |
+---------+--------------------+
|       1 |               1477 |
|       2 |                832 |
+---------+--------------------+

Where

1477 stands by 1053 (days from 13.02.2017 to 02.01.2020 - user had active contracts during this time) + 424 (days from 18.02.2021 to 18.04.2022)

832 stands by 529 (days from 19.01.2019 to 01.07.2020) + 303 (days from 19.06.2021 to 18.04.2022).

I tried some queries with joins, datediff's, case when conditions but nothing worked. I'll be grateful for any help.

datetime sql-server sql
1 Answers

If you don't have a Tally/Numbers table (highly recommended), you can use an ad-hoc tally/numbers table

Example or dbFiddle

Select User_ID
      ,Days = count(DISTINCT dateadd(DAY,N,Start_Date))
 from  YourTable A
 Join ( Select Top 10000 N=Row_Number() Over (Order By (Select NULL)) 
         From master..spt_values n1, master..spt_values n2
      ) B
  On N<=DateDiff(DAY,Start_Date,End_Date)  
 Group By User_ID

Results

User_ID     Days
1           1477
2           832
user_15700000
Related