Another SQL question, hopefull simple one

Question

Post reply

Another SQL question, hopefull simple one

DiabloSlayer

SSCrazy

Points: 2232
More actions
August 25, 2015 at 4:10 pm

#302277

Hello Everyone,
I have been trying to get minimum in punch time for IN_PUNCH column and maximum out punch time for OUT_PUNCH column for the same day and same employee as shown below.
Is this possible?
Thank you guys,
WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS) AS
(
SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3' UNION ALL
SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4'
)
SELECT *
FROM SampleData
Existing Results
PERSONTRANSACTDATESTARTDATE END_DATEIN_PUNCH OUT_PUNCH HOURS
123408/03/201508/03/2015 08/03/2015 06:00 09:00 3
123408/03/201508/03/2015 08/03/2015 09:00 13:00 4
Desired Results
PERSONTRANSACTDATESTARTDATE END_DATEIN_PUNCH OUT_PUNCH HOURS
123408/03/201508/03/2015 08/03/2015 06:00 13:00 3
123408/03/201508/03/2015 08/03/2015 06:00 13:00 4

Viewing 9 posts - 1 through 8 (of 8 total)

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

pietlinden SSC Guru Points: 63519 More actions · Answer 1

Can't remember if this works in 2008. I think it works in R2

WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS) AS

(

SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3' UNION ALL

SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4' UNION ALL

SELECT 1,'08/04/2015','08/04/2015','08/04/2015','07:00','15:00','3'UNION ALL

SELECT 1,'08/05/2015','08/05/2015','08/05/2015','07:00','18:00','4'

)

SELECT Person

, TransactDate

, MIN(IN_PUNCH) OVER (PARTITION BY Person, TransactDate ORDER BY TransactDate) AS MIN_IN

, MAX(OUT_PUNCH) OVER (PARTITION BY Person, TransactDate ORDER BY TransactDate) AS MAX_OUT

FROM SampleData

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 2

Hi Ten Centuries,

Thank you for the quick reply, I ran the query and I get the following error:

Msg 102, Level 15, State 1, Line 11

Incorrect syntax near 'order'.

Regards,

D

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 3

Hi Ten Centuries,

I removed the ORDER BY clause and it gave me the results I was looking for.

Thank you,

D

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 4

For completeness, the order by does not work with the over clause prior to 2012

😎

WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS) AS

(

SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3' UNION ALL

SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4'

)

SELECT

SD.PERSON

,SD.TRANSACTDATE

,SD.STARTDATE

,SD.END_DATE

,MIN(SD.IN_PUNCH) OVER

(

PARTITION BY SD.PERSON

,SD.TRANSACTDATE

) AS IN_PUNCH

,MAX(SD.OUT_PUNCH) OVER

(

PARTITION BY SD.PERSON

,SD.TRANSACTDATE

) AS OUT_PUNCH

,SD.[HOURS]

FROM SampleData SD;

Results

PERSON TRANSACTDATE STARTDATE END_DATE IN_PUNCH OUT_PUNCH HOURS

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

1234 08/03/2015 08/03/2015 08/03/2015 06:00 13:00 3

1234 08/03/2015 08/03/2015 08/03/2015 06:00 13:00 4

drew.allen SSC Guru Points: 76999 More actions · Answer 5

For completeness, ordering by one of the partition expressions is pointless. The ORDER BY clause orders the records within the partition. Since all rows within a partition have the same values for each of the partition expressions, including them in the order by clause has no bearing on the final order.

For completeness, the MIN and MAX value of an expression are the same regardless of the sort order (unless a range is specified). Using an ORDER BY clause associated with MIN or MAX is pointless, which is why they weren't available in SQL 2008. They were added to SQL 2012, because ranges were made available in SQL 2012.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 6

This can be achieved by sub-query also 🙂

WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS) AS

(

SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3' UNION ALL

SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4'

)

SELECT s.PERSON,

s.TRANSACTDATE,

s.STARTDATE,

s.END_DATE,

in_sample.IN_PUNCH,

in_sample.OUT_PUNCH,

HOURS

FROM SampleDataas s

INNER JOIN (

SELECT PERSON,TRANSACTDATE, STARTDATE, END_DATE, MIN(IN_PUNCH) IN_PUNCH ,MAX(OUT_PUNCH) OUT_PUNCH

FROM SampleData

GROUP BY PERSON,TRANSACTDATE, STARTDATE, END_DATE

) AS in_sample

ON s.PERSON=in_sample.PERSONAND

s.TRANSACTDATE=s.TRANSACTDATE AND

s.STARTDATE=s.STARTDATEAND

s.END_DATE=in_sample.END_DATE

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 7

Hi pietlinden,

I kept calling you Ten Centuries so I my apologies LOL..

Your solution worked sir so thank you VERY MUCH for your kind and quick help !!

Regards,

D

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 8

Let's hope that you never have someone that punches in one day and punches out the next day, like someone on a 3rd shift might.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)