SQL Query Help Please

  • Hello Everyone,

    I need to write a report where I query against my table that holds employee's Pay Data, I look back from today back to current Monday of the week, if there is Pay Type of 'Regular' on a day then I want to insert a Pay Type of 'Regular2', this is the easy part :), so if today is Tuesday and I run the report from today back to yesterday (Monday) and there is a Pay Type of 'Regular' I would then insert a Pay Type of 'Regular2' for Monday, then when I run the report again tomorrow (Wednesday) back to Monday again and there is a new Pay Type of 'Regular' on Tuesday then I would add a Pay Type of 'Regular2' for Tuesday. I would never pickup the Monday Pay Type again because I had already picked it from Tuesday's run.

    So essentially the report will run Monday thru Sunday looking from today's date to first Monday of the week and whenever it finds a Pay Type of 'Regular' and inserts Pay Type of 'Regular2' on a certain date it should not repeat the insert process again for the already picked dates. There could be two Pay Types of 'Regular' on a given day and I need to insert two Pay Types of 'Regular2' on that day.

    Pay Data Table - The ID column is unique for each day

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

    (

    SELECT 12913,'07/21/2014','Regular',101,'Monday'

    UNION ALL SELECT 12913,'07/22/2014','Regular',102,'Tuesday'

    UNION ALL SELECT 12913,'07/23/2014','Regular',103,'Wednesday'

    UNION ALL SELECT 12913,'07/24/2014','Regular',104,'Thursday'

    UNION ALL SELECT 12913,'07/25/2014','Regular',105,'Friday'

    )

    SELECT *

    FROM SampleData;

    On 7/21 in employee's timecard there is a Regular Pay Type on Monday 7/21, so when I run the query on Monday 7/21 I would see the following results - Note: I don't need the ID column in the results

    Monday thru Monday

    PERSON DATE PAYTYPE DOW

    1291307/21/2014Regular2Monday

    On 7/22 employee worked on Tuesday 7/22 so now in employee's timecard there is a Regular Pay Type on Monday 7/21 and on Tuesday 7/22, so when I run the query on Tuesday 7/22 I would see the following results.

    Monday thru Tuesday

    PERSON DATE PAYTYPE DOW

    1291307/22/2014Regular2Tuesday

    On 7/23 employee worked on Wednesday 7/23 so now in employee's timecard there is a Regular Pay Type on Monday 7/21 Thru Wednesday 7/23, so when I run the query on Wednesday 7/23 I would see the following results.

    Monday thru Wednesday

    PERSON DATE PAYTYPE DOW

    1291307/23/2014Regular2Wednesday

    And so on..

    The idea is that even though I'm looking back to current Monday if the week, I don't want to pick a record again once I have already looked at it.

    I hope I explained it alright but please ask me if you have questions and than you in advance for helping.

  • DiabloZA (7/27/2014)


    The idea is that even though I'm looking back to current Monday if the week, I don't want to pick a record again once I have already looked at it.

    I hope I explained it alright but please ask me if you have questions and than you in advance for helping.

    I recently dealt with an issue like this, but I want to ask a few questions before I can help you, otherwise it's real easy to shoot yourself in the foot.

    What is the delivery mechanism for this report? What are the expectations if the report is run, is not saved in some way by the user (or it fails), and they cannot recover the data from the source system without an operations ticket to adjust data in the production system?

    You only want to do reports that change the source data during the run period via an automated component that will save off the report for the end users, who can then use it at their leisure. Trusting Windows to correctly save to Excel 100% of the time on the first report run, or for someone's IE not to crash before the results get to screen (but the procs complete) is setting yourself up for a serious problem.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Hi Evil Kraig F,

    The way this works at the client site is that employees daily worked hours are recorded as Pay Type (Regular) carrying lets say 8 hours each day on their timecard (it's timekeeping system), so when my query runs against this time keeping system on Monday it looks at todays timecard and looks for the Regular Pay Type, once it sees that Pay Type it then adds 8 hours of Regular 2 Pay Type in that timecard via an XML API program.

    Now today is Tuesday and again there is a Pay Type of Regular of 8 hours in the timecard for Tuesday, the query runs on Tuesday and it looks at the timecard from today (Tuesday) back to Monday, it ignores Monday's data because there is already a combination of Regular and Regular 2 Pay Types there thus it inserts Pay Type of Regular 2 with 8 hours on Tuesday.

    This cycle continues from current Monday till Sunday each week, one thing to note is that if there is a day where there is no Regular Pay Type then it moves on to the next day.

    I hope this makes sense 🙂 and thank you for helping..

    Regards,

  • So, while I still think you're setting yourself up if you're not incredibly careful, here's one of the most cautious ways to approach this to make sure the data can't be double-touched between the select and the updates.

    Note, I switched the sample data over to a temp table so you could actually do an update.

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

    DROP TABLE #SampleDAta

    CREATE TABLE #SampleData (PERSON INT ,dt DATETIME,[PAYTYPE] VARCHAR(20) ,[ID] INT ,[DOW] VARCHAR(20))

    INSERT INTO #SampleData

    SELECT 12913,'07/21/2014','Regular',101,'Monday'

    UNION ALL SELECT 12913,'07/22/2014','Regular',102,'Tuesday'

    UNION ALL SELECT 12913,'07/23/2014','Regular',103,'Wednesday'

    UNION ALL SELECT 12913,'07/24/2014','Regular',104,'Thursday'

    UNION ALL SELECT 12913,'07/25/2014','Regular',105,'Friday'

    UNION ALL SELECT 12913,'07/26/2014','Regular',104,'Saturday'

    UNION ALL SELECT 12913,'07/27/2014','Regular',105,'Sunday'

    UNION ALL SELECT 12913,'07/28/2014','Regular',101,'Monday'

    UNION ALL SELECT 12913,'07/29/2014','Regular',102,'Tuesday'

    UNION ALL SELECT 12913,'07/30/2014','Regular',103,'Wednesday'

    UNION ALL SELECT 12913,'07/31/2014','Regular',104,'Thursday'

    UNION ALL SELECT 12913,'08/01/2014','Regular',105,'Friday'

    DECLARE @RunDate DATETIME

    SET @RunDate = '20140730'

    DECLARE @Result TABLE

    (PERSON INT ,dt DATETIME,[PAYTYPE] VARCHAR(20) ,[ID] INT )

    SELECT

    dt,

    DATEPART( week, dt - 1) -- -1 because standard weeks start on sunday)

    FROM

    #SampleData

    WHERE

    DATEPART( week, dt - 1) = DATEPART( week, @RunDate - 1)

    AND dt <= @RunDate

    UPDATE #SampleData

    SETPayType = 'Regular2'

    OUTPUT

    inserted.Person,

    Inserted.dt,

    Inserted.PayType,

    inserted.id

    INTO

    @Result

    WHERE

    PAyType = 'Regular'

    AND DATEPART( week, dt - 1) = DATEPART( week, @RunDate - 1) -- -1 because standard weeks start on sunday)

    AND dt <= @RunDate

    SELECT * FROM @Result

    Let me know if there's any confusion or concerns. Be aware that the DATEPART component is going to die a horrid death because of no indexing. I'd recommend you turn that into a computed persisted column on the table and index it.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • DiabloZA (7/28/2014)


    Hi Evil Kraig F,

    The way this works at the client site is that employees daily worked hours are recorded as Pay Type (Regular) carrying lets say 8 hours each day on their timecard (it's timekeeping system), so when my query runs against this time keeping system on Monday it looks at todays timecard and looks for the Regular Pay Type, once it sees that Pay Type it then adds 8 hours of Regular 2 Pay Type in that timecard via an XML API program.

    Now today is Tuesday and again there is a Pay Type of Regular of 8 hours in the timecard for Tuesday, the query runs on Tuesday and it looks at the timecard from today (Tuesday) back to Monday, it ignores Monday's data because there is already a combination of Regular and Regular 2 Pay Types there thus it inserts Pay Type of Regular 2 with 8 hours on Tuesday.

    This cycle continues from current Monday till Sunday each week, one thing to note is that if there is a day where there is no Regular Pay Type then it moves on to the next day.

    I hope this makes sense 🙂 and thank you for helping..

    Regards,

    As I read this I have a question, are you updating the pay type from Regular to Regular 2 or are you adding another row of data? Reading the description above it sounds more like adding an additional row of data than updating a row of data.

  • Hi Lynn,

    You are right, I'm adding a new row of data for Regular2.

    Regards,

    Zulf

  • Using Craig's setup, is this what you are looking for?

    declare @RunDate date;

    set @RunDate = '2014-07-22'

    select dateadd(week,datediff(week,0,dateadd(day,-1,@RunDate)),0)

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

    DROP TABLE #SampleData

    CREATE TABLE #SampleData (PERSON INT ,dt DATETIME,[PAYTYPE] VARCHAR(20) ,[ID] INT)

    INSERT INTO #SampleData

    SELECT 12913,'07/21/2014','Regular',101

    UNION ALL SELECT 12913,'07/22/2014','Regular',102

    UNION ALL SELECT 12913,'07/23/2014','Regular',103

    UNION ALL SELECT 12913,'07/24/2014','Regular',104

    UNION ALL SELECT 12913,'07/25/2014','Regular',105

    UNION ALL SELECT 12913,'07/26/2014','Regular',104

    UNION ALL SELECT 12913,'07/27/2014','Regular',105

    UNION ALL SELECT 12913,'07/28/2014','Regular',101

    UNION ALL SELECT 12913,'07/29/2014','Regular',102

    UNION ALL SELECT 12913,'07/30/2014','Regular',103

    UNION ALL SELECT 12913,'07/31/2014','Regular',104

    UNION ALL SELECT 12913,'08/01/2014','Regular',105

    SELECT

    *

    FROM

    #SampleData

    WHERE

    dt >= dateadd(week,datediff(week,0,dateadd(day,-1,@RunDate)),0) and

    dt <= @RunDate

    ORDER BY

    PERSON,

    dt,

    ID;

    UPDATE #SampleData SET

    PAYTYPE = 'Regular2'

    OUTPUT

    deleted.PERSON,

    deleted.dt,

    deleted.PAYTYPE,

    deleted.ID

    INTO

    #SampleData

    WHERE

    PAYTYPE = 'Regular'

    AND dt >= dateadd(week,datediff(week,0,dateadd(day,-1,@RunDate)),0) and

    dt <= @RunDate;

    SELECT

    *

    FROM

    #SampleData

    ORDER BY

    PERSON,

    dt,

    ID;

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

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