sql - 查找同一列中的日期范围

Question

我有一个数据集，看起来像这样:

ResourceID RequirementId ProjectID Startdate EndDate BillingPercentage
-------------------- -------------------- ---------- ---------- --------------- ----------------- ----------------------------------------------
1 5066 7505 2015-09-15 00:00:00.000 2015-09-30 00:00:00.000 50
2 4748 7499 2015-09-10 00:00:00.000 2015-09-20 00:00:00.000 50

我想计算该特定月份的范围和相应的计费百分比，我的查询是:

插入@DateTimeline
SELECT @MonthStartDate AS OSTARTDATE,@MonthEndDate AS OENDDATE,0

插入@DateTimeline
SELECT Startdate AS OSTARTDATE,EndDate AS OENDDATE,BillingPercentage From @RESOURCE_UNBILLED 按开始日期排序

插入@DateTimeline
SELECT EndDate AS OSTARTDATE,EndDate AS OENDDATE,BillingPercentage From @RESOURCE_UNBILLED 按开始日期排序

数据如下所示:

SerialNo OSTARTDATE OENDDATE BillingPercentage
—————————————————————————————— ------- ---------------------------------------
1 2015-09-01 00:00:00.000 2015-09-30 00:00:00.000 0
2 2015-09-10 00:00:00.000 2015-09-20 00:00:00.000 50
3 2015-09-15 00:00:00.000 2015-09-30 00:00:00.000 50
4 2015-09-20 00:00:00.000 2015-09-20 00:00:00.000 50
5 2015-09-30 00:00:00.000 2015-09-30 00:00:00.000 50

我想检索如下数据

OSTARTDATE OENDDATE BillingPercentage
—————————————————————————————— ------- ---------------------------------------
2015-09-01 00:00:00.000 2015-09-10 00:00:00.000 0
2015-09-10 00:00:00.000 2015-09-15 00:00:00.000 50
2015-09-15 00:00:00.000 2015-09-20 00:00:00.000 100
2015-09-20 00:00:00.000 2015-09-30 00:00:00.000 50

请建议我怎样才能得到这个，我也可以在这里使用pivot吗？

Answer 1

使用表变量来存储您的 @dateStamps带列:SerialNo , OSTARTDATE和 OENDDATE .

试试这个查询:

SELECT  d.SerialNo, d.OSTARTDATE, d.OENDDATE
        , ( SELECT SUM(t.BillingPercentage)
            FROM yourTable t
            WHERE d.OENDDATE BETWEEN t.Startdate AND t.EndDate
               OR d.OSTARTDATE BETWEEN t.Startdate AND t.EndDate
               OR (d.OSTARTDATE > t.Startdate AND d.OENDDATE < t.EndDate)
              ) AS BillingPercentage
FROM 
    @dateStamps d 

我的完整代码是:

DECLARE @DatePart as int = 5

;WITH dateStamps AS (
    SELECT 1 As SerialNo, CAST('2015-' + CONVERT(varchar, MONTH(MIN(t.Startdate))) + '-01 00:00:00.000' As datetime) AS OSTARTDATE
                        , CAST('2015-' + CONVERT(varchar, MONTH(MIN(t.Startdate))) + '-01 00:00:00.000' As datetime) + (@DatePart - 1) AS OENDDATE

    FROM yourTable t
    UNION ALL 
    SELECT ds.SerialNo + 1, ds.OSTARTDATE + @DatePart, ds.OSTARTDATE + (@DatePart * 2 - 1)
    FROM dateStamps ds
    WHERE MONTH(OSTARTDATE + @DatePart) <= MONTH(ds.OSTARTDATE)
)
SELECT  d.SerialNo, d.OSTARTDATE, d.OENDDATE
        , ( SELECT SUM(t.BillingPercentage)
            FROM t
            WHERE d.OENDDATE BETWEEN t.Startdate AND t.EndDate
               OR d.OSTARTDATE BETWEEN t.Startdate AND t.EndDate
               OR (d.OSTARTDATE > t.Startdate AND d.OENDDATE < t.EndDate)
              ) AS BillingPercentage
FROM 
    dateStamps d