SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Query Help Please


SQL Query Help Please

Author
Message
DiabloSlayer
DiabloSlayer
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 434
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 Smile, 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
12913 07/21/2014 Regular2 Monday


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
12913 07/22/2014 Regular2 Tuesday


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
12913 07/23/2014 Regular2 Wednesday


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.
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8617 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
DiabloSlayer
DiabloSlayer
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 434
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 Smile and thank you for helping..

Regards,
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8617 Visits: 7660
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
SET PayType = '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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39591 Visits: 38554
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 Smile 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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
DiabloSlayer
DiabloSlayer
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 434
Hi Lynn,

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

Regards,

Zulf
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39591 Visits: 38554
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;




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search