July 27, 2014 at 5:20 pm
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.
July 28, 2014 at 2:38 pm
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.
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
July 28, 2014 at 11:57 pm
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,
July 29, 2014 at 2:03 pm
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.
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
July 29, 2014 at 2:18 pm
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.
July 29, 2014 at 3:11 pm
Hi Lynn,
You are right, I'm adding a new row of data for Regular2.
Regards,
Zulf
July 29, 2014 at 8:04 pm
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