SQL Joins Question

Question

Post reply

SQL Joins Question

DiabloSlayer

SSCrazy

Points: 2232
More actions
August 15, 2015 at 3:33 pm

#320641

Hello SQL Gurus,
I have a JOIN question, I have tried several ways to accomplish the desired results but can't get it to work, basically, I want to keep all the records from table A and JOIN it with table B to get SHIFTS for the days when hours were worked in table A and SHIFTS were assigned in table B.
Following are the two tables, of course in the actual table there are many more columns for table A but just to get the point across these are the main columns for each tables.
Thanks in advance everyone...
Table A has columns
WITH SampleData (PERSON, STARTDATE, ENDDATE, INPUNCH, OUTPUNCH, HOURS,PAYCODE) AS
(
SELECT 1125,'08/11/2015','08/11/2015','08:00', '12:00',4.0, 'REGULAR'
UNION ALL SELECT 1125,'08/11/2015','08/11/2015','13:00', '17:00',4.0, 'SICK'
UNION ALL SELECT 1125,'08/12/2015','08/12/2015','09:00', '17:00',4.0, 'REGULAR'
UNION ALL SELECT 1125,'08/13/2015','08/13/2015','08:00', '16:00',8.0, 'REGULAR'
UNION ALL SELECT 1125,'08/14/2015','08/14/2015','10:00', '12:00',2.0, 'ABSENT'
UNION ALL SELECT 1125,'08/14/2015','08/14/2015','13:00', '17:00',4.0, 'REGULAR'
UNION ALL SELECT 9916,'08/12/2015','08/12/2015','08:00', '13:00',5.0, 'REGULAR'
UNION ALL SELECT 9916,'08/12/2015','08/12/2015','14:00', '17:00',3.0, 'SICK'
)
SELECT *
FROM SampleData
Table B has columns
WITH SampleData2 (PERSON, STARTDATE, ENDDATE, SHIFT) AS
(
SELECT 1125,'08/12/2015','08/12/2015','SHIFTA'
UNION ALL SELECT 1125,'08/13/2015','08/13/2015','SHIFTA'
UNION ALL SELECT 1125,'08/14/2015','08/14/2015','SHIFTB'
)
SELECT *
FROM SampleData2
Desired Results should be
PERSONSTARTDATEENDDATE INPUNCHOUTPUNCH HOURSPAYCODE SHIFT
112508/11/201508/11/201508:0012:00 4.0REGULAR
112508/11/201508/11/201513:0017:00 4.0SICK
112508/12/201508/12/201509:0017:00 4.0REGULAR SHIFTA
112508/13/201508/13/201508:0016:00 8.0REGULAR SHIFTA
112508/14/201508/14/201510:0012:00 2.0ABSENT SHIFTB
112508/14/201508/14/201513:0017:00 4.0REGULAR SHIFTB
991608/12/201508/12/201508:0013:00 5.0REGULAR
991608/12/201508/12/201514:0017:00 3.0SICK

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

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Quick suggestion

😎

USE tempdb;

GO

SET NOCOUNT ON;

GO

;WITH SampleData (PERSON, STARTDATE, ENDDATE, INPUNCH, OUTPUNCH, HOURS,PAYCODE) AS

(

SELECT 1125,'08/11/2015','08/11/2015','08:00', '12:00',4.0, 'REGULAR'

UNION ALL SELECT 1125,'08/11/2015','08/11/2015','13:00', '17:00',4.0, 'SICK'

UNION ALL SELECT 1125,'08/12/2015','08/12/2015','09:00', '17:00',4.0, 'REGULAR'

UNION ALL SELECT 1125,'08/13/2015','08/13/2015','08:00', '16:00',8.0, 'REGULAR'

UNION ALL SELECT 1125,'08/14/2015','08/14/2015','10:00', '12:00',2.0, 'ABSENT'

UNION ALL SELECT 1125,'08/14/2015','08/14/2015','13:00', '17:00',4.0, 'REGULAR'

UNION ALL SELECT 9916,'08/12/2015','08/12/2015','08:00', '13:00',5.0, 'REGULAR'

UNION ALL SELECT 9916,'08/12/2015','08/12/2015','14:00', '17:00',3.0, 'SICK'

)

,SampleData2 (PERSON, STARTDATE, ENDDATE, [SHIFT]) AS

(

SELECT 1125,'08/12/2015','08/12/2015','SHIFTA'

UNION ALL SELECT 1125,'08/13/2015','08/13/2015','SHIFTA'

UNION ALL SELECT 1125,'08/14/2015','08/14/2015','SHIFTB'

)

SELECT

SD.PERSON

,SD.STARTDATE

,SD.ENDDATE

,SD.INPUNCH

,SD.OUTPUNCH

,SD.HOURS

,SD.PAYCODE

,ISNULL(SS.[SHIFT],'') AS [SHIFT]

FROM SampleData SD

LEFT OUTER JOIN SampleData2 SS

ON SD.PERSON = SS.PERSON

AND SD.STARTDATE = SS.STARTDATE;

Results

PERSON STARTDATE ENDDATE INPUNCH OUTPUNCH HOURS PAYCODE SHIFT

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

1125 08/11/2015 08/11/2015 08:00 12:00 4.0 REGULAR

1125 08/11/2015 08/11/2015 13:00 17:00 4.0 SICK

1125 08/12/2015 08/12/2015 09:00 17:00 4.0 REGULAR SHIFTA

1125 08/13/2015 08/13/2015 08:00 16:00 8.0 REGULAR SHIFTA

1125 08/14/2015 08/14/2015 10:00 12:00 2.0 ABSENT SHIFTB

1125 08/14/2015 08/14/2015 13:00 17:00 4.0 REGULAR SHIFTB

9916 08/12/2015 08/12/2015 08:00 13:00 5.0 REGULAR

9916 08/12/2015 08/12/2015 14:00 17:00 3.0 SICK

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 2

Hi Eirikur Eiriksson,

Yes, this is what I needed.

You are awesome and thank you very much for the quick help.

Regards,

DZA

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

DiabloZA (8/16/2015)
Hi Eirikur Eiriksson,
Yes, this is what I needed.
You are awesome and thank you very much for the quick help.
Regards,
DZA

Thanks for the feedback and you are most welcome.

😎

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Gosh, I'd be real careful here, Eirikur. While your good code certainly does what was asked, it will fail if table SampleData2 is ever correctly used for its apparent intended purpose where someone includes a multi-day date range.

--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)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

Jeff Moden (8/16/2015)
Gosh, I'd be real careful here, Eirikur. While your good code certainly does what was asked, it will fail if table SampleData2 is ever correctly used for its apparent intended purpose where someone includes a multi-day date range.

Good point Jeff, this would apply to multi date entries for both tables of course and if such exist then one must unpack the period using a calendar table etc..

😎

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Eirikur Eiriksson (8/16/2015)
Jeff Moden (8/16/2015)
Gosh, I'd be real careful here, Eirikur. While your good code certainly does what was asked, it will fail if table SampleData2 is ever correctly used for its apparent intended purpose where someone includes a multi-day date range.
Good point Jeff, this would apply to multi date entries for both tables of course and if such exist then one must unpack the period using a calendar table etc..
😎

I'm thinking that you don't actually need a Calendar table for this because the SampleData2 acts as the necessary Calendar table. The criteria of the query just needs to be changed to use the EndDate of that table. Perhaps, something like this.

FROM SampleData SD

LEFT OUTER JOIN SampleData2 SS

ON SD.PERSON = SS.PERSON

AND SD.STARTDATE >= SS.STARTDATE

AND SD.EndDate < DATEADD(dd,1,SS.EndDate)

;

--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)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 7

Jeff Moden (8/16/2015)
Eirikur Eiriksson (8/16/2015)
Jeff Moden (8/16/2015)
Gosh, I'd be real careful here, Eirikur. While your good code certainly does what was asked, it will fail if table SampleData2 is ever correctly used for its apparent intended purpose where someone includes a multi-day date range.
Good point Jeff, this would apply to multi date entries for both tables of course and if such exist then one must unpack the period using a calendar table etc..
😎
I'm thinking that you don't actually need a Calendar table for this because the SampleData2 acts as the necessary Calendar table. The criteria of the query just needs to be changed to use the EndDate of that table. Perhaps, something like this.
FROM SampleData SD
LEFT OUTER JOIN SampleData2 SS
ON SD.PERSON = SS.PERSON
AND SD.STARTDATE >= SS.STARTDATE
AND SD.EndDate < DATEADD(dd,1,SS.EndDate)
;

Spot on if either of the two has the full granularity

😎

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

Eirikur Eiriksson (8/16/2015)
Jeff Moden (8/16/2015)
Eirikur Eiriksson (8/16/2015)
Jeff Moden (8/16/2015)
Gosh, I'd be real careful here, Eirikur. While your good code certainly does what was asked, it will fail if table SampleData2 is ever correctly used for its apparent intended purpose where someone includes a multi-day date range.
Good point Jeff, this would apply to multi date entries for both tables of course and if such exist then one must unpack the period using a calendar table etc..
😎
I'm thinking that you don't actually need a Calendar table for this because the SampleData2 acts as the necessary Calendar table. The criteria of the query just needs to be changed to use the EndDate of that table. Perhaps, something like this.
FROM SampleData SD
LEFT OUTER JOIN SampleData2 SS
ON SD.PERSON = SS.PERSON
AND SD.STARTDATE >= SS.STARTDATE
AND SD.EndDate < DATEADD(dd,1,SS.EndDate)
;
Spot on if either of the two has the full granularity
😎

Even if they only have full day granularity, if the start and end dates of the SampleData2 rows are different, it'll be handled. Equating only to the start date won't handle such a thing.

--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)