Working days SQL query

Question

I have a problem I can't get my head around.

I have a table of events and a table of users. Some events are chargeable and some are not. What I want to establish is the number chargeable days per week month and year for every user.

Here is the data...

ID FromDate    ToDate       isChargeable     Username
1  2014-11-03  2014-11-04        Y           AUser 
2  2014-11-04  2014-11-06        Y           AUser
3  2014-11-07  2014-11-07        Y           AUser

And I've written a basic query to calculate the difference between the from and to dates and sum them adding 1 to the total as the toDate counts as a full day.

SELECT DISTINCT FromDate, ToDate, isChargeable,Username, DATEDIFF(day, FromDate, ToDate) + 1) AS 'count1'
FROM            dbo.vDiary
GROUP BY FromDate, ToDate, isChargeable,Username

This results in...

      FromDate    ToDate       isChargeable     Username   count1
      2014-11-03  2014-11-04        Y           AUser      2
      2014-11-04  2014-11-06        Y           AUser      3
      2014-11-07  2014-11-07        Y           AUser      1

Which is incorrect as it's showing 6 working days because two events are overlapping.

How can I allow for this overlapping? I need to state that if there is an event on a particular day that is chargeable then that day is a chargeable day.

I think I need a calendar table but I'm not sure on the process of using it to get the right results.

Any help would be really appreciated!!

Thanks

Steve

EDIT: Trying solution from below

        ;WITH cte
     AS (SELECT Row_number()OVER(PARTITION BY Username ORDER BY FromDate) rn,
                FromDate,ToDate,isChargeable,Username
         FROM   #calen)
SELECT a.FromDate,a.ToDate,a.isChargeable,a.Username,
       CASE
         WHEN a.FromDate > b.ToDate
               OR b.ToDate IS NULL THEN Datediff(day, a.FromDate, a.ToDate) + 1
         ELSE Datediff(day, Dateadd(dd, 1, b.ToDate), a.ToDate)+ 1
       END DatDiff,
       *
FROM   cte a
       LEFT JOIN cte b
              ON a.rn = b.rn + 1
                 AND a.username = b.username
WHERE  ( a.ToDate > b.todate
          OR b.ToDate IS NULL ) 

Produces (updated)

FromDate    ToDate  isChargeable    Username    DatDiff rn  FromDate    ToDate  isChargeable    Username    rn  FromDate    ToDate  isChargeable    Username
2014-11-03  2014-11-04  Y   AUser                2      1   2014-11-03  2014-11-04  Y   AUser   NULL    NULL    NULL    NULL    NULL
2014-11-04  2014-11-06  Y   AUser                2      2   2014-11-04  2014-11-06  Y   AUser   1   2014-11-03  2014-11-04  Y   AUser
2014-11-07  2014-11-07  Y   AUser                1      3   2014-11-07  2014-11-07  Y   AUser   2   2014-11-04  2014-11-06  Y   AUser
2014-12-03  2014-12-15  Y   AUser                13     4   2014-12-03  2014-12-15  Y   AUser   3   2014-11-07  2014-11-07  Y   AUser
2014-12-10  2014-12-17  Y   AUser                2      5   2014-12-10  2014-12-17  Y   AUser   4   2014-12-03  2014-12-15  Y   AUser
2015-12-04  2015-12-15  Y   AUser                12     6   2015-12-04  2015-12-15  Y   AUser   5   2014-12-10  2014-12-17  Y   AUser
2014-11-03  2014-11-03  Y   BUser                1      1   2014-11-03  2014-11-03  Y   BUser   NULL    NULL    NULL    NULL    NULL
2014-11-04  2014-11-04  Y   BUser                1      2   2014-11-04  2014-11-04  Y   BUser   1   2014-11-03  2014-11-03  Y   BUser

Answer 1

Try this.

If you have Unique ID's try this.

create table #calen(ID int, FromDate   date, ToDate date,isChargeable char(1),Username varchar(20))

INSERT #calen
VALUES (1,'2014-11-03','2014-11-04','Y ','AUser'),
       (2,'2014-11-04','2014-11-06','Y','AUser'),
       (3,'2014-11-07','2014-11-07','Y','AUser') 


SELECT a.FromDate,a.ToDate,a.isChargeable,a.Username,
       CASE
         WHEN a.FromDate > b.ToDate
               OR b.ToDate IS NULL THEN Datediff(day, a.FromDate, a.ToDate) + 1
         ELSE Datediff(day, Dateadd(dd, 1, b.ToDate), a.ToDate)
              + 1
       END DatDiff
FROM   #calen a
       LEFT JOIN #calen b
              ON a.id = b.id + 1
WHERE  ( a.ToDate > b.todate
          OR b.ToDate IS NULL ) 

Update :

For more than one user.

;WITH cte
     AS (SELECT Row_number()OVER(PARTITION BY Username ORDER BY FromDate) rn,
                FromDate,ToDate,isChargeable,Username
         FROM   #calen)
SELECT a.FromDate,a.ToDate,a.isChargeable,a.Username,
       CASE
         WHEN a.FromDate > b.ToDate
               OR b.ToDate IS NULL THEN Datediff(day, a.FromDate, a.ToDate) + 1
         ELSE Datediff(day, Dateadd(dd, 1, b.ToDate), a.ToDate)+ 1
       END DatDiff
FROM   cte a
       LEFT JOIN cte b
              ON a.rn = b.rn + 1
                 AND a.username = b.username
WHERE  ( a.ToDate > b.todate
          OR b.ToDate IS NULL ) 

OUTPUT :

FromDate    ToDate      isChargeable    Username    DatDiff
----------  ----------  ------------    --------    -------
2014-11-03  2014-11-04  Y               AUser           2
2014-11-04  2014-11-06  Y               AUser           2
2014-11-07  2014-11-07  Y               AUser           1

Answer 2

If all the ID values are not unique we can have the below query:

SELECT ID, FromDate, ToDate, isChargeable, Username,
 CASE WHEN EXISTS(SELECT ToDate FROM UserTable B WHERE A.FromDate = B.ToDate 
   AND A.UserName = B.UserName AND A.ID <> B.ID) THEN
   DATEDIFF(day, FromDate, ToDate)
 ELSE
   DATEDIFF(day, FromDate, ToDate) + 1
 END  AS 'count1'
FROM UserTable A

else we have to create CTE to create unique ROWID.

;WITH UserTable
     AS (SELECT Row_number() OVER(ORDER BY FromDate) RowID,
                FromDate,ToDate, isChargeable, Username
         FROM #user)

SELECT RowID, FromDate, ToDate, isChargeable, Username,
 CASE WHEN EXISTS(SELECT ToDate FROM UserTable B WHERE A.FromDate = B.ToDate 
       AND A.UserName = B.UserName AND A.RowID <> B.RowID) THEN
   DATEDIFF(day, FromDate, ToDate)
 ELSE
   DATEDIFF(day, FromDate, ToDate) + 1
 END  AS 'count1'
FROM UserTable A

Answer 3

try this :

WITH CTE AS 
(SELECT a.* ,y.flag
FROM EventsTable a
OUTER APPLY 
  (SELECT DATEDIFF(dd, a.ToDate, x.FromDate) as flag 
  FROM EventsTable x 
  WHERE a.id = x.id AND x.FromDate > a.ToDate) y)

SELECT id,FromDate,ToDate,isChargeable,Username,
   CASE WHEN flag = 1 THEN 
      DATEDIFF(dd, FromDate, ToDate) 
   ELSE DATEDIFF(dd, FromDate,ToDate) + 1 
   END AS count1
FROM CTE