SQL Query Help needed - Thank you.

  • Hello Folks,

    I'm in need of your kind help here, I have two tables listed below, the first table has employees pay data, the second table has a day and time reset data, both tables are joined using the ID column, so based on second table's reset day and time I need to grab the hours and also put a flag of 'F' in the last column to indicate that the hours occurred on the reset day at or after the reset time hours are 'special'.

    Some days employee work with taking a break like on Monday the 23rd, and some days they work straight like on Tuesday the 24th which happens to be the reset day for this employee.

    My challenge is to get the breakdown of hours on the reset days, in the example below this employee worked 10 hours straight from 7am to 5pm, I would like to break it down to 7am to 11am and then 11am to 5pm.

    The other (non RESET) days I can just get the hours from time difference between Start time and End time.

    I hope this is doable 🙂

    Pay Data Table

    WITH SampleData (PERSON,[DATE],[STARTIME],[ENDTIME],,[ID],[DOW]) AS

    (

    -- Tuesday's Reset

    SELECT 901,'06/23/2014','07:00:00.0000000','11:00:00.0000000','Regular',51,'Monday'

    UNION ALL SELECT 901,'06/23/2014','11:30:00.0000000','17:00:00.0000000','Regular',51,'Monday'

    UNION ALL SELECT 901,'06/24/2014','07:00:00.0000000','17:00:00.0000000','Regular',51,'Tuesday'

    UNION ALL SELECT 901,'06/24/2014','11:00:00.0000000','12:00:00.0000000','Jury',51,'Tuesday'

    UNION ALL SELECT 901,'06/25/2014','07:00:00.0000000','11:00:00.0000000','Regular',51,'Wednesday'

    UNION ALL SELECT 901,'06/25/2014','11:30:00.0000000','17:00:00.0000000','Regular',51,'Wednesday'

    UNION ALL SELECT 901,'06/26/2014','07:00:00.0000000','17:00:00.0000000','Regular',51,'Thursday'

    UNION ALL SELECT 901,'06/27/2014','07:00:00.0000000','11:00:00.0000000','Regular',51,'Friday'

    UNION ALL SELECT 901,'06/27/2014','11:30:00.0000000','16:30:00.0000000','Regular',51,'Friday'

    )

    SELECT *

    FROM SampleData;

    Reset Data Table

    WITH SampleData1 ([ID],[RESETTIME],[DOW]) AS

    (

    -- First employee -

    SELECT 51,'11:00:00.0000000','Tuesday'

    UNION ALL SELECT 53,'13:00:00.0000000','Wednesday'

    )

    SELECT *

    FROM SampleData1;

    Current Pay Data Table Results

    PERSONDATE STARTIME ENDTIME CODEIDDOW

    90106/23/201407:00:00.000000011:00:00.0000000Regular51Monday

    90106/23/201411:30:00.000000017:00:00.0000000Regular51Monday

    90106/24/201407:00:00.000000017:00:00.0000000Regular51Tuesday

    90106/24/201411:00:00.000000012:00:00.0000000Jury51Tuesday

    90106/25/201407:00:00.000000011:00:00.0000000Regular51Wednesday

    90106/25/201411:30:00.000000017:00:00.0000000Regular51Wednesday

    90106/26/201407:00:00.000000017:00:00.0000000Regular51Thursday

    90106/27/201407:00:00.000000011:00:00.0000000Regular51Friday

    90106/27/201411:30:00.000000016:30:00.0000000Regular51Friday

    Desired Results After joining both tables - Employee 901's reset day and time is Tuesday's at 11am so his hours on the 24th split up, 7am to 11am (4.00 Hours) and 11am to 5pm (6.00 hours) also after 11am the FLAG will have an 'B'.

    PERSONDATE HOURSCODE ID DOW FLAG

    90106/23/20144.00Regular 51 Monday

    90106/23/20145.5Regular 51 Monday

    90106/24/20144.0Regular 51 Tuesday

    90106/24/20146.0Regular 51 TuesdayB

    90106/24/20141.0Jury 51 Tuesday

    90106/25/20144.0Regular 51 Wednesday

    90106/25/20145.5Regular 51 Wednesday

    90106/26/201410.0Regular 51 Thursday

    90106/27/20144.00Regular 51 Friday

    90106/27/20145.5Regular 51 Friday

  • Hey there,

    I suspect that the reason no-one has helped you yet is that we don't have any data-types to marry up your sample data with and you haven't explained how the "B" comes in to play. Using just your sample data, I spent 5 mins and knocked this out: -

    WITH PayData (PERSON,[DATE],[STARTIME],[ENDTIME],,[ID],[DOW]) AS

    (

    -- Tuesday's Reset

    SELECT 901,'06/23/2014','07:00:00.0000000','11:00:00.0000000','Regular',51,'Monday'

    UNION ALL SELECT 901,'06/23/2014','11:30:00.0000000','17:00:00.0000000','Regular',51,'Monday'

    UNION ALL SELECT 901,'06/24/2014','07:00:00.0000000','17:00:00.0000000','Regular',51,'Tuesday'

    UNION ALL SELECT 901,'06/24/2014','11:00:00.0000000','12:00:00.0000000','Jury',51,'Tuesday'

    UNION ALL SELECT 901,'06/25/2014','07:00:00.0000000','11:00:00.0000000','Regular',51,'Wednesday'

    UNION ALL SELECT 901,'06/25/2014','11:30:00.0000000','17:00:00.0000000','Regular',51,'Wednesday'

    UNION ALL SELECT 901,'06/26/2014','07:00:00.0000000','17:00:00.0000000','Regular',51,'Thursday'

    UNION ALL SELECT 901,'06/27/2014','07:00:00.0000000','11:00:00.0000000','Regular',51,'Friday'

    UNION ALL SELECT 901,'06/27/2014','11:30:00.0000000','16:30:00.0000000','Regular',51,'Friday'

    ),

    ResetData ([ID],[RESETTIME],[DOW]) AS

    (

    -- First employee -

    SELECT 51,'11:00:00.0000000','Tuesday'

    UNION ALL SELECT 53,'13:00:00.0000000','Wednesday'

    )

    SELECT pd.[PERSON], pd.[DATE],

    -- More messing around with data

    CONVERT(VARCHAR(10),[diff].Seconds/3600)+':'+RIGHT('00'+CONVERT(VARCHAR(2),([diff].Seconds%3600)/60),2)+':'+

    RIGHT('00'+CONVERT(VARCHAR(2),[diff].Seconds%60),2) AS [HOURS],

    pd.,

    pd.[ID],

    pd.[DOW],

    CASE WHEN pd.[STARTIME] = [times].[EARLY] AND pd.[ENDTIME] = [times].[LATE]

    THEN 'B'

    ELSE NULL END AS [FLAG]

    FROM PayData pd

    OUTER APPLY (SELECT CASE WHEN rd.[RESETTIME] > pd.[STARTIME]

    THEN pd.[STARTIME]

    ELSE rd.[RESETTIME] END AS [EARLY],

    CASE WHEN rd.[RESETTIME] > pd.[STARTIME]

    THEN rd.[RESETTIME]

    ELSE pd.[ENDTIME] END AS [LATE]

    FROM ResetData rd

    WHERE pd.ID = rd.ID

    AND pd.DOW = rd.DOW -- THIS WOULD BE BETTER IF IT WAS THE DATE INSTEAD

    )[times]

    -- This messing around occurs because you aren't storing the data as "DATETIME". If you're using "DATE" and "TIME" rather than

    -- strings as it appears, let me know because this becomes easier.

    OUTER APPLY (SELECT DATEDIFF(SECOND, CAST('2000-01-01 '+SUBSTRING(ISNULL([times].[EARLY],pd.[STARTIME]),1,8) AS DATETIME),

    CAST('2000-01-01 '+SUBSTRING(ISNULL([times].[LATE],pd.[ENDTIME]),1,8) AS DATETIME)) AS [Seconds]

    )[diff];

    Note the crazy messing around I had to do because I had to assume that your data is char/varchar/nvarchar/nchar ? It returns this on my system: -

    PERSON DATE HOURS CODE ID DOW FLAG

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

    901 06/23/2014 4:00:00 Regular 51 Monday NULL

    901 06/23/2014 5:30:00 Regular 51 Monday NULL

    901 06/24/2014 4:00:00 Regular 51 Tuesday NULL

    901 06/24/2014 1:00:00 Jury 51 Tuesday B

    901 06/25/2014 4:00:00 Regular 51 Wednesday NULL

    901 06/25/2014 5:30:00 Regular 51 Wednesday NULL

    901 06/26/2014 10:00:00 Regular 51 Thursday NULL

    901 06/27/2014 4:00:00 Regular 51 Friday NULL

    901 06/27/2014 5:00:00 Regular 51 Friday NULL

    Which appears to match what you want, but it will not scale well do to the string manipulations.

    Thanks.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Hi Cadavre,

    I had a feeling that I screwed up somewhere with my post.

    The pay Data and Reset Data values comes from different tables customer's database, I could use datetime data types for the starttime and endtime columns however the resettime column is only in seconds for example: for example for 11am it has a value of '39600' so I had use CONVERT(varchar, DATEADD(ms, RESETTIME * 1000, 0), 108) to get a value '11:00:00' so then I can match it up against the starttime column.

    Here are the data types from Pay Data Table

    (PERSON [nvarchar](15)

    DATE [datetime]

    [HOURS] [int]

    STARTTIME [datetime] ** I grab the time portion only

    ENDTIME [datetime] ** I grab the time portion only

    CODE [nvarchar](35)

    ID [int]

    DOW [nvarchar](15)

    Here are the data types from Reset Data table

    ID [int]

    DOW [nvarchar](15)

    RESETTIME [nvarchar](15) ** The actual table is using INT data type and stores the values in for this field is seconds, so for example for 11am it has a value of '39600' so I had to use CONVERT(varchar, DATEADD(ms, RESETTIME * 1000, 0), 108) to get a value '11:00:00'

    The B flag is an indicator for the hours on and after the Reset time and DOW, so for our example this employee's reset time is 11am and reset day is Tuesday, so on Tuesday 4 hours from 7:00 to 11:00 shows up on one row with no B flag and 6 hours from 11:00 to 17:00 shows up with a B flag.

    I hope this makes sense..

    PERSONDATE HOURSCODE ID DOW FLAG

    90106/24/20144.0Regular 51 Tuesday

    90106/24/20146.0Regular 51 TuesdayB

    90106/24/20141.0Jury 51 Tuesday

    I ran your solution, it is missing the 6 hours with a B flag on Tuesday but it's very close 🙂

    Thank you for taking the time to read through this issue.

  • DiabloZA (7/15/2014)


    Hi Cadavre,

    I had a feeling that I screwed up somewhere with my post.

    Excellent. One little issue, I can't see how "HOURS" could be an INT, judging by the data you've been showing it must either be a string or a decimal of some kind. I'm going to assume VARCHAR(10).

    Here's the sample data in a readily consumable format: -

    IF OBJECT_ID('tempdb..#PayData') IS NOT NULL

    BEGIN;

    DROP TABLE #PayData;

    END;

    CREATE TABLE #PayData

    (

    [PERSON] NVARCHAR(15),

    [DATE] DATETIME,

    [HOURS] VARCHAR(10),

    [STARTTIME] DATETIME,

    [ENDTIME] DATETIME,

    NVARCHAR(35),

    [ID] INT,

    [DOW] NVARCHAR(15),

    [FLAG] CHAR(1)

    );

    IF OBJECT_ID('tempdb..#ResetData') IS NOT NULL

    BEGIN;

    DROP TABLE #ResetData;

    END;

    CREATE TABLE #ResetData

    (

    [ID] INT,

    [RESETSECONDS] INT,

    [RESETTIME] AS CONVERT(VARCHAR, DATEADD(ms, [RESETSECONDS] * 1000, 0), 108),

    [DOW] NVARCHAR(15)

    );

    INSERT INTO [#PayData]

    (

    [PERSON],

    [DATE],

    [HOURS],

    [STARTTIME],

    [ENDTIME],

    ,

    [ID],

    [DOW]

    )

    SELECT 901,'2014-06-23',NULL,'2014-06-23 07:00:00','2014-06-23 11:00:00','Regular',51,'Monday'

    UNION ALL SELECT 901,'2014-06-23',NULL,'2014-06-23 11:30:00','2014-06-23 17:00:00','Regular',51,'Monday'

    UNION ALL SELECT 901,'2014-06-24',NULL,'2014-06-24 07:00:00','2014-06-24 17:00:00','Regular',51,'Tuesday'

    UNION ALL SELECT 901,'2014-06-24',NULL,'2014-06-24 11:00:00','2014-06-24 12:00:00','Jury',51,'Tuesday'

    UNION ALL SELECT 901,'2014-06-25',NULL,'2014-06-25 07:00:00','2014-06-25 11:00:00','Regular',51,'Wednesday'

    UNION ALL SELECT 901,'2014-06-25',NULL,'2014-06-25 11:30:00','2014-06-25 17:00:00','Regular',51,'Wednesday'

    UNION ALL SELECT 901,'2014-06-26',NULL,'2014-06-26 07:00:00','2014-06-26 17:00:00','Regular',51,'Thursday'

    UNION ALL SELECT 901,'2014-06-27',NULL,'2014-06-27 07:00:00','2014-06-27 11:00:00','Regular',51,'Friday'

    UNION ALL SELECT 901,'2014-06-27',NULL,'2014-06-27 11:30:00','2014-06-27 16:30:00','Regular',51,'Friday';

    INSERT INTO [#ResetData]

    (

    [ID],

    [RESETSECONDS],

    [DOW]

    )

    SELECT 51,39600,'Tuesday'

    UNION ALL SELECT 53,46800,'Wednesday';

    I feel like this should be easier than this, but well, it's pre-morning coffee right now 😀

    -- STEP 1

    IF OBJECT_ID('tempdb..#HOLDER') IS NOT NULL

    BEGIN;

    DROP TABLE #HOLDER;

    END;

    SELECT [pd].[PERSON],

    [pd].[DATE],

    [pd].[HOURS],

    [pd].[STARTTIME],

    [pd].[ENDTIME],

    [pd].,

    [pd].[ID],

    [pd].[DOW],

    DATEADD(SECOND, rd.[RESETSECONDS], pd.[DATE]) AS [NEWTIME],

    'B' AS [FLAG]

    INTO #HOLDER

    FROM [#PayData] AS pd

    INNER JOIN [#ResetData] AS rd ON pd.[ID] = rd.[ID] AND pd.[DOW] = rd.[DOW]

    WHERE DATEADD(SECOND, rd.[RESETSECONDS], pd.[DATE]) > pd.[STARTTIME]

    AND DATEADD(SECOND, rd.[RESETSECONDS], pd.[DATE]) < pd.[ENDTIME];

    -- STEP 2

    MERGE INTO [#PayData] [Target]

    USING

    (

    SELECT [PERSON],

    [DATE],

    [HOURS],

    [STARTTIME],

    [ENDTIME],

    ,

    [ID],

    [DOW],

    [NEWTIME]

    FROM [#HOLDER]

    ) [Source]

    ON [Target].[PERSON] = [Source].[PERSON]

    AND [Target].[DATE] = [Source].[DATE]

    AND [Target].[STARTTIME] = [Source].[STARTTIME]

    AND [Target].[ENDTIME] = [Source].[ENDTIME]

    AND [Target]. = [Source].

    AND [Target].[DOW] = [Source].[DOW]

    WHEN MATCHED THEN

    UPDATE SET [ENDTIME] = [Source].[NEWTIME];

    -- STEP 3

    INSERT INTO [#PayData]

    (

    [PERSON],

    [DATE],

    [HOURS],

    [STARTTIME],

    [ENDTIME],

    ,

    [ID],

    [DOW],

    [FLAG]

    )

    SELECT [PERSON],

    [DATE],

    [HOURS],

    [NEWTIME],

    [ENDTIME],

    ,

    [ID],

    [DOW],

    [FLAG]

    FROM [#HOLDER];

    --Step 4

    UPDATE [#PayData]

    SET [HOURS] = CONVERT(VARCHAR(10), DATEDIFF(SECOND, [STARTTIME], [ENDTIME]) / 3600) + ':' +

    RIGHT('00' + CONVERT(VARCHAR(2), ( DATEDIFF(SECOND, [STARTTIME], [ENDTIME]) % 3600 ) / 60), 2) + ':' +

    RIGHT('00' + CONVERT(VARCHAR(2), DATEDIFF(SECOND, [STARTTIME], [ENDTIME]) % 60), 2);

    --Step 5

    IF OBJECT_ID('tempdb..#HOLDER') IS NOT NULL

    BEGIN;

    DROP TABLE #HOLDER;

    END;

    -- Display results

    SELECT *

    FROM [#PayData]

    ORDER BY [PERSON],

    [DATE],

    CASE WHEN = 'Regular' THEN 0

    ELSE 1

    END;

    Which results in: -

    PERSON DATE HOURS STARTTIME ENDTIME CODE ID DOW FLAG

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

    901 2014-06-23 00:00:00.000 4:00:00 2014-06-23 07:00:00.000 2014-06-23 11:00:00.000 Regular 51 Monday NULL

    901 2014-06-23 00:00:00.000 5:30:00 2014-06-23 11:30:00.000 2014-06-23 17:00:00.000 Regular 51 Monday NULL

    901 2014-06-24 00:00:00.000 4:00:00 2014-06-24 07:00:00.000 2014-06-24 11:00:00.000 Regular 51 Tuesday NULL

    901 2014-06-24 00:00:00.000 6:00:00 2014-06-24 11:00:00.000 2014-06-24 17:00:00.000 Regular 51 Tuesday B

    901 2014-06-24 00:00:00.000 1:00:00 2014-06-24 11:00:00.000 2014-06-24 12:00:00.000 Jury 51 Tuesday NULL

    901 2014-06-25 00:00:00.000 4:00:00 2014-06-25 07:00:00.000 2014-06-25 11:00:00.000 Regular 51 Wednesday NULL

    901 2014-06-25 00:00:00.000 5:30:00 2014-06-25 11:30:00.000 2014-06-25 17:00:00.000 Regular 51 Wednesday NULL

    901 2014-06-26 00:00:00.000 10:00:00 2014-06-26 07:00:00.000 2014-06-26 17:00:00.000 Regular 51 Thursday NULL

    901 2014-06-27 00:00:00.000 4:00:00 2014-06-27 07:00:00.000 2014-06-27 11:00:00.000 Regular 51 Friday NULL

    901 2014-06-27 00:00:00.000 5:00:00 2014-06-27 11:30:00.000 2014-06-27 16:30:00.000 Regular 51 Friday NULL

    Hope that helps!


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Hi Cadavre,

    Thank you so much for ALL your help and time in helping me with this issue, God bless you..

    I left out a BIG piece of information that the client is using a SQL 2005 environment and I believe the MERGE INTO statement was introduced in SQL 2008, so when I ran your entire SQL query I received the following errors:

    Msg 102, Level 15, State 1, Line 24

    Incorrect syntax near 'MERGE'.

    Msg 102, Level 15, State 1, Line 37

    Incorrect syntax near 'Source'.

    Best Regards,

  • DiabloZA (7/16/2014)


    Hi Cadavre,

    Thank you so much for ALL your help and time in helping me with this issue, God bless you..

    I left out a BIG piece of information that the client is using a SQL 2005 environment and I believe the MERGE INTO statement was introduced in SQL 2008, so when I ran your entire SQL query I received the following errors:

    Msg 102, Level 15, State 1, Line 24

    Incorrect syntax near 'MERGE'.

    Msg 102, Level 15, State 1, Line 37

    Incorrect syntax near 'Source'.

    Best Regards,

    You should be able to just convert step 2 into a plain update statement. I'm at the supermarket at the moment, so I'm on my phone so no guarantees that this is correct: -

    -- STEP 2

    UPDATE [Target]

    SET [Target].[ENDTIME] = [NEWTIME]

    FROM [#PayData] [Target]

    INNER JOIN [#HOLDER] [Source] ON [Target].[PERSON] = [Source].[PERSON]

    AND [Target].[DATE] = [Source].[DATE]

    AND [Target].[STARTTIME] = [Source].[STARTTIME]

    AND [Target].[ENDTIME] = [Source].[ENDTIME]

    AND [Target]. = [Source].

    AND [Target].[DOW] = [Source].[DOW];

    Make sure you test! 😉


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 5 (of 5 total)

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