3

I'm trying to create a lost time report in SSRS. I found a script that gives me what I need for a single date range, but I need this for multiple dates, and I need the total for the range.

So if I have 6 date ranges, I just need the total for the month/year combination and type combination (either L or R).

DECLARE @s SMALLDATETIME, @e SMALLDATETIME;
SELECT  @s = '20161209',  @e = '20170113';
    ;WITH n(n) AS
(
  SELECT TOP (DATEDIFF(MONTH, @s, @e)+1) ROW_NUMBER() OVER 
  (ORDER BY [object_id])-1 FROM sys.all_objects
),
x(n,fd,ld) AS 
(
  SELECT n.n, DATEADD(MONTH, n.n, m.m), DATEADD(MONTH, n.n+1, m.m)
  FROM n, (SELECT DATEADD(DAY, 1-DAY(@s), @s)) AS m(m)
)
SELECT [Month] = DATENAME(MONTH, fd), [Days] = DATEDIFF(DAY, fd, ld) 
  - CASE WHEN @s > fd THEN (DATEDIFF(DAY, fd, @s)+1) ELSE 0 END
  - CASE WHEN @e < ld THEN (DATEDIFF(DAY, @e, ld)-1) ELSE 0 END
  FROM x;

My table is called HRAL and the main fields are BeginDate (inclusive), EndDate (exclusive) and Type.

Sample Data

StartDate   EndDate    Type
2017-09-28  2017-10-02 L
2017-10-03  2017-10-10 R
2016-11-10  2016-11-11 L
2017-08-17  2017-12-25 R

Results

Date        Days  Type
2017-09-01  3     L
2017-10-01  1     L
2017-10-01  38    R
2017-11-01  1     L
2017-08-01  15    R
2017-09-01  30    R
2017-11-01  30    R
2017-12-01  24    R

I assume the solution needs recursion?

3

First I'll create a very abbreviated version of a calendar table that stores just month start and end dates.

CREATE TABLE #LAZY_DATE_DIM (
    MONTH_START_DATE DATETIME,
    MONTH_END_DATE DATETIME,
    PRIMARY KEY (MONTH_START_DATE)
);

INSERT INTO #LAZY_DATE_DIM WITH (TABLOCK)
SELECT DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '18991201')
, DATEADD(DAY, -1, DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '19000101' ))
FROM master..spt_values;

You may find it useful to create a permanent calendar table in your database, but there are ways to generate the data that you need on the fly if that isn't an option.

Next step is to mock up your sample data:

CREATE TABLE #HRAL (
    StartDate DATETIME,
    EndDate DATETIME,
    [Type] VARCHAR(1)
);

INSERT INTO #HRAL VALUES ('2017-09-28','2017-10-02','L');
INSERT INTO #HRAL VALUES ('2017-10-03','2017-10-10','R');
INSERT INTO #HRAL VALUES ('2016-11-10','2016-11-11','L');
INSERT INTO #HRAL VALUES ('2017-08-17','2017-12-25','R');

I broke down the overall query into a few different steps. First step is to join the tables together such that we get a row for every relevant month that intersects with the start and end dates in your HRAL table. I used the following code:

SELECT
  h.*
, dd.*
FROM #HRAL h
INNER JOIN #LAZY_DATE_DIM dd ON 
    dd.MONTH_START_DATE > DATEADD(MONTH, -1, h.StartDate)
    AND dd.MONTH_START_DATE < h.EndDate

The intermediate results:

╔═════════════════════════╦═════════════════════════╦══════╦═════════════════════════╦═════════════════════════╗
║        StartDate        ║         EndDate         ║ Type ║    MONTH_START_DATE     ║     MONTH_END_DATE      ║
╠═════════════════════════╬═════════════════════════╬══════╬═════════════════════════╬═════════════════════════╣
║ 2017-09-28 00:00:00.000 ║ 2017-10-02 00:00:00.000 ║ L    ║ 2017-09-01 00:00:00.000 ║ 2017-09-30 00:00:00.000 ║
║ 2017-09-28 00:00:00.000 ║ 2017-10-02 00:00:00.000 ║ L    ║ 2017-10-01 00:00:00.000 ║ 2017-10-31 00:00:00.000 ║
║ 2017-10-03 00:00:00.000 ║ 2017-10-10 00:00:00.000 ║ R    ║ 2017-10-01 00:00:00.000 ║ 2017-10-31 00:00:00.000 ║
║ 2016-11-10 00:00:00.000 ║ 2016-11-11 00:00:00.000 ║ L    ║ 2016-11-01 00:00:00.000 ║ 2016-11-30 00:00:00.000 ║
║ 2017-08-17 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ R    ║ 2017-08-01 00:00:00.000 ║ 2017-08-31 00:00:00.000 ║
║ 2017-08-17 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ R    ║ 2017-09-01 00:00:00.000 ║ 2017-09-30 00:00:00.000 ║
║ 2017-08-17 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ R    ║ 2017-10-01 00:00:00.000 ║ 2017-10-31 00:00:00.000 ║
║ 2017-08-17 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ R    ║ 2017-11-01 00:00:00.000 ║ 2017-11-30 00:00:00.000 ║
║ 2017-08-17 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ R    ║ 2017-12-01 00:00:00.000 ║ 2017-12-31 00:00:00.000 ║
╚═════════════════════════╩═════════════════════════╩══════╩═════════════════════════╩═════════════════════════╝

We can find the overlapping days by taking the maximum of the two start dates, the minimum of the two end days (adding one to the month end date from the calendar table), and using DATEDIFF to find the difference of days. Below is the query that shows some of the intermediate column values:

SELECT
  dd.MONTH_START_DATE
, h.StartDate
, start_dt.dt calc_start_dt
, h.[Type]
, dd.MONTH_END_DATE
, h.EndDate
, end_dt.dt calc_end_dt
FROM #HRAL h
INNER JOIN #LAZY_DATE_DIM dd ON 
    dd.MONTH_START_DATE > DATEADD(MONTH, -1, h.StartDate)
    AND dd.MONTH_START_DATE < h.EndDate
CROSS APPLY (
    SELECT MAX(start_dt)
    FROM (VALUES (dd.MONTH_START_DATE), (h.StartDate)) x (start_dt)
) start_dt (dt)
CROSS APPLY (
    SELECT MIN(end_dt)
    FROM (VALUES (DATEADD(DAY, 1, dd.MONTH_END_DATE)), (h.EndDate)) x (end_dt)
) end_dt (dt)

The result set:

╔═════════════════════════╦═════════════════════════╦═════════════════════════╦══════╦═════════════════════════╦═════════════════════════╦═════════════════════════╗
║    MONTH_START_DATE     ║        StartDate        ║      calc_start_dt      ║ Type ║     MONTH_END_DATE      ║         EndDate         ║       calc_end_dt       ║
╠═════════════════════════╬═════════════════════════╬═════════════════════════╬══════╬═════════════════════════╬═════════════════════════╬═════════════════════════╣
║ 2017-09-01 00:00:00.000 ║ 2017-09-28 00:00:00.000 ║ 2017-09-28 00:00:00.000 ║ L    ║ 2017-09-30 00:00:00.000 ║ 2017-10-02 00:00:00.000 ║ 2017-10-01 00:00:00.000 ║
║ 2017-10-01 00:00:00.000 ║ 2017-09-28 00:00:00.000 ║ 2017-10-01 00:00:00.000 ║ L    ║ 2017-10-31 00:00:00.000 ║ 2017-10-02 00:00:00.000 ║ 2017-10-02 00:00:00.000 ║
║ 2017-10-01 00:00:00.000 ║ 2017-10-03 00:00:00.000 ║ 2017-10-03 00:00:00.000 ║ R    ║ 2017-10-31 00:00:00.000 ║ 2017-10-10 00:00:00.000 ║ 2017-10-10 00:00:00.000 ║
║ 2016-11-01 00:00:00.000 ║ 2016-11-10 00:00:00.000 ║ 2016-11-10 00:00:00.000 ║ L    ║ 2016-11-30 00:00:00.000 ║ 2016-11-11 00:00:00.000 ║ 2016-11-11 00:00:00.000 ║
║ 2017-08-01 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ R    ║ 2017-08-31 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ 2017-09-01 00:00:00.000 ║
║ 2017-09-01 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ 2017-09-01 00:00:00.000 ║ R    ║ 2017-09-30 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ 2017-10-01 00:00:00.000 ║
║ 2017-10-01 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ 2017-10-01 00:00:00.000 ║ R    ║ 2017-10-31 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ 2017-11-01 00:00:00.000 ║
║ 2017-11-01 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ 2017-11-01 00:00:00.000 ║ R    ║ 2017-11-30 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ 2017-12-01 00:00:00.000 ║
║ 2017-12-01 00:00:00.000 ║ 2017-08-17 00:00:00.000 ║ 2017-12-01 00:00:00.000 ║ R    ║ 2017-12-31 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║ 2017-12-25 00:00:00.000 ║
╚═════════════════════════╩═════════════════════════╩═════════════════════════╩══════╩═════════════════════════╩═════════════════════════╩═════════════════════════╝

Now I can put it all together by using DATEDIFF along with GROUP BY. The final query:

SELECT
  dd.MONTH_START_DATE
, SUM(DATEDIFF(DAY, start_dt.dt, end_dt.dt)) [days]
, h.[Type]
FROM #HRAL h
INNER JOIN #LAZY_DATE_DIM dd ON 
    dd.MONTH_START_DATE > DATEADD(MONTH, -1, h.StartDate)
    AND dd.MONTH_START_DATE < h.EndDate
CROSS APPLY (
    SELECT MAX(start_dt)
    FROM (VALUES (dd.MONTH_START_DATE), (h.StartDate)) x (start_dt)
) start_dt (dt)
CROSS APPLY (
    SELECT MIN(end_dt)
    FROM (VALUES (DATEADD(DAY, 1, dd.MONTH_END_DATE)), (h.EndDate)) x (end_dt)
) end_dt (dt)
GROUP BY
  dd.MONTH_START_DATE
, h.[Type];

The results match yours:

╔═════════════════════════╦══════╦══════╗
║    MONTH_START_DATE     ║ days ║ Type ║
╠═════════════════════════╬══════╬══════╣
║ 2016-11-01 00:00:00.000 ║    1 ║ L    ║
║ 2017-09-01 00:00:00.000 ║    3 ║ L    ║
║ 2017-10-01 00:00:00.000 ║    1 ║ L    ║
║ 2017-08-01 00:00:00.000 ║   15 ║ R    ║
║ 2017-09-01 00:00:00.000 ║   30 ║ R    ║
║ 2017-10-01 00:00:00.000 ║   38 ║ R    ║
║ 2017-11-01 00:00:00.000 ║   30 ║ R    ║
║ 2017-12-01 00:00:00.000 ║   24 ║ R    ║
╚═════════════════════════╩══════╩══════╝

If you prefer to avoid the CROSS APPLY syntax you can accomplish the same thing with a CASE expression.

  • Thank you :) I've never used cross apply, so it's something I will need to look into. Is there any good resources you would recommend for things like this, or just do a search and read some tutorials. – Phalanx Nov 16 '17 at 3:10
  • @Phalanx Added an alternative syntax – Joe Obbish Nov 16 '17 at 3:39
  • Did you accidentally not paste the alternate code? I can't see it. – Phalanx Nov 16 '17 at 7:01
  • @Phalanx It's at the bottom. Just use CASE. – Joe Obbish Nov 16 '17 at 14:35
  • from this, how would you remove the weekends from the results. For example, in December 2017 should be 16 days. – Phalanx Dec 4 '17 at 22:23
0

Hope I am not so late.

Lemme know if its not working with other sample data. I am sure it can be fix if its giving wrong output for other sample data.

I have normal calendar table which store only FOM (first day of each month)

CREATE TABLE tblcalender (
    DATEVal DATETIME
    PRIMARY KEY (DATEVal)
);
INSERT INTO tblcalender WITH (TABLOCK)
SELECT DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '18991201')

FROM master..spt_values;

Sample data,

CREATE TABLE #TMP(StartDate DATE, EndDate DATE,Type1 CHAR(1))
INSERT INTO #TMP VALUES
 ('2017-09-28','2017-10-02','L')
,('2017-10-03','2017-10-10','R')
,('2016-11-10','2016-11-11','L')
,('2017-08-17','2017-12-25','R')

--SELECT * FROM #TMP

For Sql server 2008 and below,

;WITH CTE
AS (
    SELECT DATEVal
        ,StartDate
        ,EndDate
        ,Type1
        ,CASE 
            WHEN year(StartDate) = year(EndDate)
                AND month(StartDate) = month(EndDate)
                THEN '1'
            WHEN month(DATEVal) = month(StartDate)
                AND year(DATEVal) = year(ca.StartDate)
                THEN 'S'
            WHEN DATEVal > StartDate
                AND DATEVal < dateadd(month, - 1, DATEADD(month, datediff(month, 0, ca.EndDate) + 1, 0))
                THEN 'W'
            ELSE 'E'
            END flag
    FROM tblcalender
    CROSS APPLY (
        SELECT StartDate
            ,EndDate
            ,Type1
        FROM #tmp
        WHERE DATEVal >= dateadd(month, - 1, DATEADD(month, datediff(month, 0, StartDate) + 1, 0))
            AND DATEVal <= dateadd(day, - 1, DATEADD(month, datediff(month, 0, EndDate) + 1, 0))
        ) ca
    )
    ,CTE1
AS (
    SELECT DATEVal
        ,CASE 
            WHEN flag = '1'
                THEN datediff(day, StartDate, EndDate)
            WHEN flag = 'S'
                THEN datediff(day, StartDate, dateadd(day, - 1, DATEADD(month, datediff(month, 0, StartDate) + 1, 0))) + 1
            WHEN flag = 'E'
                THEN datediff(day, dateadd(month, - 1, DATEADD(month, datediff(month, 0, endDate) + 1, 0)), endDate)
            ELSE day(dateadd(day, - 1, DATEADD(month, datediff(month, 0, DATEVal) + 1, 0))) --W
            END [Days]
        ,Type1
        ,StartDate
        ,EndDate
        ,Flag
    FROM CTE
    )
--select * from CTE1
SELECT DATEVal
    ,Type1
    ,SUM([Days]) [Days]
FROM CTE1
GROUP BY DATEVal
    ,Type1

DROP TABLE #TMP

For Sql server 2012 and above,just minor change.using EOMonth and FORMAT to make the code shorter.

;with CTE as
(
select DATEVal,StartDate,EndDate,Type1
,case when FORMAT(StartDate,'MMyyyy')=FORMAT(EndDate,'MMyyyy') 
THEN '1'
when FORMAT(StartDate,'MMyyyy')=FORMAT(DATEVal,'MMyyyy') 
then 'S' 
when DATEVal>StartDate and DATEVal< dateadd(day,1,EOMONTH(EndDate,-1))--FOM
THEN 'W'
else 'E' END flag
 from tblcalender
cross APPLY(
select StartDate,EndDate,Type1 from #tmp 
where  DATEVal>= dateadd(day,1,EOMONTH(StartDate,-1))
and 
DATEVal<=EOMONTH(EndDate)
)ca
)
,CTE1 as
(
select DATEVal
, case when  flag='1'
THEN datediff(day,StartDate,EndDate)
when flag='S'
THEN datediff(day,StartDate,EOMONTH(StartDate))+1
when flag='E'
THEN datediff(day,dateadd(day,1,EOMONTH(endDate,-1)),endDate)

else  day(EOMONTH(DATEVal))--W
end [Days]
,Type1,StartDate,EndDate,Flag   
from CTE
)
--select * from CTE1

SELECT DATEVal,Type1
 ,SUM([Days])[Days]
from CTE1
group by DATEVal,Type1

DROP TABLE #TMP
  • Why are the ; randomly placed? (sometimes before, sometimes after a statement, sometimes not at all)? – ypercubeᵀᴹ Nov 22 '17 at 11:03
  • I use poormanSQL formatter.BTW where else I am missing it. – KumarHarsh Nov 22 '17 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.