Table Join issue

Question

Post reply

Table Join issue

sarath.tata

SSCommitted

Points: 1640
More actions
April 6, 2015 at 10:35 am

#398286

I have a table which has holidays listed.
tblHoliday
01/01/2015
04/25/2015
I have another table which has Start and End Dates
tblPeriod
01/01/2015, 01/07/2015
01/08/2015, 01/15/2015
04/20/2015, 04/30/2015
I am trying to identify the period that has a holiday date in it. I'm trying to get output like this where 1 and 0 indicates the boolean value of holiday
01/01/2015, 01/07/2015, 1
01/08/2015, 01/15/2015, 0
04/20/2015, 04/30/2015, 1
I'm trying the below query which shows only the rows that are in holidays. How do I expand this to get the output I expect?
SELECT StartDate, EndDate FROM tblPeriod p
JOIN tblHoliday t ON t.HolidayDate BETWEEN p.StartDate AND p.EndDate
I get an error "The multi-part identifier "t.HolidayDate" could not be bound.", if I try to put like this
SELECT StartDate, EndDate, t.HolidayDate FROM tblPeriod p
LEFT JOIN tblHoliday t ON t.HolidayDate BETWEEN p.StartDate AND p.EndDate
Thanks for your tip on the issue with my query.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

I'm using your same code but I don't get an error.

CREATE TABLE tblHoliday( HolidayDate date);

INSERT INTO tblHoliday

VALUES('20150101'),

('20150425');

CREATE TABLE tblPeriod( StartDate date, EndDate date);

INSERT INTO tblPeriod

VALUES('20150101', '20150107'),

('20150108', '20150115'),

('20150420', '20150430');

SELECT StartDate, EndDate FROM tblPeriod p

JOIN tblHoliday t ON t.HolidayDate BETWEEN p.StartDate AND p.EndDate

SELECT StartDate, EndDate, t.HolidayDate FROM tblPeriod p

LEFT JOIN tblHoliday t ON t.HolidayDate BETWEEN p.StartDate AND p.EndDate

GO

DROP TABLE tblHoliday;

DROP TABLE tblPeriod;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sarath.tata SSCommitted Points: 1640 More actions · Answer 2

It is strange!! I had this query as part of another Pivot query. The error might be because of that. I need to look into this now. Thanks for your quick help

sarath.tata SSCommitted Points: 1640 More actions · Answer 3

I could n't give complete executable query as it is part of a dynamic query which requires plenty of tables.

This is my pivot. Do you think I missed some order?

SELECT DutyRosterPeriodID, StartDate, EndDate, t.HolidayDate, ' + @cols + ' FROM Result r
LEFT JOIN #TempHolidays t ON t.HolidayDate BETWEEN r.StartDate AND r.EndDate
PIVOT ( MAX(Name) FOR Category IN (' + @cols + ')) AS PVT ORDER BY StartDate

sarath.tata SSCommitted Points: 1640 More actions · Answer 4

I don't understand the reason but when I removed alias in column name it worked!!

SELECT DutyRosterPeriodID, StartDate, EndDate, CASE WHEN HolidayDate IS NULL THEN 0 ELSE 1 END AS IsHolidayWeek, ' + @cols + ' FROM Result r
LEFT JOIN #TempHolidays t ON t.HolidayDate BETWEEN r.StartDate AND r.EndDate
PIVOT ( MAX(Name) FOR Category IN (' + @cols + ')) AS PVT ORDER BY StartDate

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

This might surprise you, but Holidaydate column is now part of the pivot columns.

SELECT DutyRosterPeriodID,

StartDate,

EndDate,

pvt.HolidayDate,

[Something],

[Something Else]

FROM Result r

LEFT JOIN #TempHolidays t ON t.HolidayDate BETWEEN r.StartDate AND r.EndDate

PIVOT ( MAX(Name) FOR Category IN ([Something],[Something Else])) AS PVT

ORDER BY StartDate

Yet another reason for me to prefer cross tabs[/url].

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2