工作日SQL查询

Question

我有一个问题，我无法解决。

我有一个事件表和一个用户表。 有些事件是收费的，有些则不是。 我想建立的是每个用户每周每月和每年的收费天数。

这是数据...

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

我写了一个基本查询来计算开始日期和结束日期之间的差异，并将它们相加 1 到总数中，因为 toDate 计为一整天。

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

这导致...

      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

这是不正确的，因为它显示 6 个工作日，因为两个事件重叠。

我怎样才能允许这种重叠？ 我需要声明，如果某一天有事件是收费的，那么那一天就是收费的一天。

我想我需要一个日历表，但我不确定使用它来获得正确结果的过程。

任何帮助将不胜感激！

谢谢

史蒂夫

编辑：从下面尝试解决方案

        ;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 ) 

生产（更新）

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

试试这个。

如果您有唯一 ID，请尝试此操作。

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 ) 

更新：

对于多个用户。

;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 ) 

输出：

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

试试这个：

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

Answer 3

如果所有 ID 值都不是唯一的，我们可以进行以下查询：

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

否则，我们必须创建 CTE 来创建唯一的 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