How to split one record into multiple records in query based on start and end date

  • Hi,

    I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.

    A random record in my Absences table shows (as an example):

    resource: 1

    startdate: 2014-08-20 09:00:00.000

    enddate: 2014-08-23 13:00:00.000

    hours: 28 (= 8 + 8 + 8 + 4)

    I would like to have 4 lines in my query:

    resource date hours

    1 2014-08-20 8

    1 2014-08-21 8

    1 2014-08-22 8

    1 2014-08-23 4

    Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.

    One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours)

    What I can't get fixed is having the correct hours per line.

    What I now get is:

    resource date hours

    ...

    1 2014-08-19 NULL

    1 2014-08-20 28

    1 2014-08-21 28

    1 2014-08-22 28

    1 2014-08-23 28

    1 2014-08-24 NULL

    ...

    ... instead of the correct hours per date (8, 8, 8, 4).

    A very simplified extract of my code is:

    DECLARE @startdate DATETIME

    DECLARE @enddate DATETIME

    SET @startdate = '2014-01-01'

    SET @enddate = '2014-08-31'

    SELECTh.res_id AS Resource,

    t.datevalue,

    (SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,

    (SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours

    FROMResources h (NOLOCK)

    CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t

    p.s.

    The 9538 value in the HRAbsenceHours function refers to the absences-workflowID

    I can't get this solved.

    Thanks!

    reg,

    Michiel

  • Here is a quick "set based loop" method using an inline Tally table to count the hours.

    😎

    USE tempdb;

    GO

    DECLARE @TEST_DATA TABLE

    (

    TEST_DATA_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

    ,startdate DATETIME NOT NULL

    ,enddate DATETIME NOT NULL

    );

    INSERT INTO @TEST_DATA(startdate,enddate)

    VALUES

    ('2014-08-20 09:00:00.000','2014-08-23 13:00:00.000')

    ;

    /* Seed for the inline Tally table */

    ;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL)

    ,(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

    /* Assign 1 to each working hour between 9 and 17 */

    ,COUNT_HOURS AS

    (

    SELECT

    TD.TEST_DATA_ID

    ,CASE

    WHEN DATEPART(HOUR,(DATEADD(HOUR,NM.N,TD.startdate))) BETWEEN 10 AND 17 THEN 1

    ELSE 0

    END AS COUNT_HOURS

    ,CONVERT(DATE,DATEADD(HOUR,NM.N,TD.startdate)) AS GR_DATE

    FROM @TEST_DATA TD

    OUTER APPLY

    (

    SELECT TOP(DATEDIFF(HOUR,TD.startdate,TD.enddate))

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N

    FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7

    ) AS NM(N)

    )

    --resource date hours

    SELECT

    CH.TEST_DATA_ID AS [resource]

    ,CH.GR_DATE AS [date]

    ,SUM(CH.COUNT_HOURS) AS [hours]

    FROM COUNT_HOURS CH

    GROUP BY CH.TEST_DATA_ID,CH.GR_DATE;

    Results

    resource date hours

    ----------- ---------- ------

    1 2014-08-20 8

    1 2014-08-21 8

    1 2014-08-22 8

    1 2014-08-23 4

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply