Geek Answers Handbook

Find a date range within a single column

I have a dataset that looks like this:

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

I want to calculate the range and the corresponding billing% for this particular month, which is my request:

    INSERT INTO @DateTimeline
    SELECT @MonthStartDate AS OSTARTDATE, @ MonthEndDate AS OENDDATE, 0 

    INSERT INTO @DateTimeline
    SELECT Startdate AS OSTARTDATE, EndDate AS OENDDATE, BillingPercentage From @RESOURCE_UNBILLED Order by Startdate

    INSERT INTO @DateTimeline
    SELECT EndDate AS OSTARTDATE, EndDate AS OENDDATE, BillingPercentage From @RESOURCE_UNBILLED Order by Startdate

And the data is as follows:

 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

, , , ?

+4
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
0

More articles:


All Articles