Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Query Help Please Expand / Collapse
Author
Message
Posted Sunday, July 27, 2014 5:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
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
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.
Post #1596667
Posted Monday, July 28, 2014 2:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 6,259, Visits: 7,451
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
Post #1597062
Posted Monday, July 28, 2014 11:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
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,
Post #1597151
Posted Tuesday, July 29, 2014 2:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 6,259, Visits: 7,451
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
Post #1597486
Posted Tuesday, July 29, 2014 2:18 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:24 PM
Points: 23,397, Visits: 32,246
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.



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)
Post #1597489
Posted Tuesday, July 29, 2014 3:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
Hi Lynn,

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

Regards,

Zulf
Post #1597506
Posted Tuesday, July 29, 2014 8:04 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:24 PM
Points: 23,397, Visits: 32,246
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;





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)
Post #1597552
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse