October 23, 2007 at 6:00 am
I need a variable JOIN condition in the view below:
This view reads data from tblRESERVATION, which contains bookings for rooms and conference halls. Room and Hall bookings share tblRESERVATION and tblRESERVATIONROOM, but all other related tables are in duplicated: SEASONS (for Rooms) and SEASONS1 (for Halls), BOARD (for Rooms) and BOARD1 (for Halls), etc. Likewise for Types, Rates, Items, etc. The table structures match but the records are different.
I am now making a report which needs to be able to retrieve individual FK data for the separate tables (which I have thankfully managed). However, since Rooms are checked out the following day while Halls have to be checked out the same evening, the JOIN condition has to be dynamic in order to return accurate results.
[Code]/* RECORDSET START */
SELECT D.THEDATE AS DATE,
COALESCE(RT.RESNO, '') AS RESNO,
COALESCE(RT.NOPAX, '') AS NOOFPAX,
CASE WHEN RT.BOARDTYPE < 0
THEN COALESCE(B1.DESCRIPTION, '')
ELSE COALESCE(B.DESCRIPTION, '')
END AS MEALPLAN,
COALESCE(RT.NOROOMS, '') AS NOOFROOMS,
CASE
WHEN COALESCE(C.CNAME,'') = ''
THEN G.FIRST_NAME + ' ' + G.LAST_NAME
ELSE C.CNAME
END AS NAME,
CASE R.CONF
WHEN 0 THEN 'TENTATIVE'
WHEN 1 THEN 'CONFIRMED'
WHEN 3 THEN 'CHECKEDIN'
WHEN 4 THEN 'CHECKEDOUT'
END AS CONF
FROM @MYTABLE D
LEFT OUTER JOIN @TEMPTABLE RT
ON D.THEDATE >= RT.CHECKIN
--This is where I want a dynamic JOIN
-- (RT.BOARDTYPE is negative for Halls)
IF RT.BOARDTYPE < 0 THEN
AND D.THEDATE <= RT.CHECKOUT
ELSE
AND D.THEDATE < RT.CHECKOUT
END
LEFT OUTER JOIN BOARD B
ON RT.BOARDTYPE = B.BOARD_NO
AND B.COM_COMPANYID_N = @CID
LEFT OUTER JOIN BOARD1 B1
ON ABS(RT.BOARDTYPE) = B1.BOARD_NO
AND B1.COM_COMPANYID_N = @CID
LEFT OUTER JOIN RESERVATION R
ON RT.RESNO = R.RES_NO
AND R.COM_COMPANYID_N = @CID
LEFT OUTER JOIN GUEST G
ON R.GUEST_ID = G.GUEST_ID
AND G.COM_COMPANYID_N = @CID
LEFT OUTER JOIN dbo.COMPANY C
ON R.COMPANYID = C.CID
AND C.COM_COMPANYID_N = @CID
ORDER BY DATE, RESNO, MEALPLAN
/* RECORDSET END */[/Code]
October 23, 2007 at 6:11 am
kindly spilt your view / query & point the area where u need the answer
October 23, 2007 at 6:19 am
FROM @MYTABLE D
LEFT OUTER JOIN @TEMPTABLE RT
ON D.THEDATE >= RT.CHECKIN
AND
(
D.THEDATE <= (CASE WHEN RT.BOARDTYPE < 0 THEN RT.CHECKOUT ELSE D.THEDATE END)
OR
D.THEDATE = 0 THEN RT.CHECKOUT ELSE D.THEDATE END)
)
--EDIT
I just can't figure why its showing D.THEDATE = 0 THEN RT.CHECKOUT ELSE D.THEDATE END
instead of [p]D.THEDATE < (CASE WHEN RT.BOARDTYPE >= 0 THEN RT.CHECKOUT ELSE D.THEDATE END). I was forced to add /p tag to show what exactly the code was...[/p]
--Ramesh
October 23, 2007 at 6:22 am
October 23, 2007 at 6:23 am
It looks to me that you need and OR clause and not a CASE.
LEFT OUTER JOIN @TEMPTABLE RT
ON D.THEDATE >= RT.CHECKIN
AND ((RT.BOARDTYPE < 0 AND D.THEDATE <= RT.CHECKOUT)
OR
(RT.BOARDTYPE >= 0 AND D.THEDATE < RT.CHECKOUT)
)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 23, 2007 at 6:52 am
Ramesh, thanks for grasping the idea so fast, I am grateful.
The idea is a Hall Reservation will be JOINed with "<=" and a Room Reservation with "<" only.
I adapted your suggestion as below but get error message:
Line 80: Incorrect syntax near '='.
[Code]LEFT OUTER JOIN @TEMPTABLE RT
ON D.THEDATE >= RT.CHECKIN
(
CASE WHEN (RT.BOARDTYPE < 0)
THEN (AND D.THEDATE <= RT.CHECKOUT)
ELSE
(AND D.THEDATE < RT.CHECKOUT)
END
)[/Code]
Please help to fix it.
October 23, 2007 at 7:14 am
October 23, 2007 at 7:32 am
goodguy (10/23/2007)
Ramesh, thanks for grasping the idea so fast, I am grateful.The idea is a Hall Reservation will be JOINed with "<=" and a Room Reservation with "<" only.
I adapted your suggestion as below but get error message:
Line 80: Incorrect syntax near '='.
[Code]LEFT OUTER JOIN @TEMPTABLE RT
ON D.THEDATE >= RT.CHECKIN
(
CASE WHEN (RT.BOARDTYPE < 0)
THEN (AND D.THEDATE <= RT.CHECKOUT)
ELSE
(AND D.THEDATE < RT.CHECKOUT)
END
)[/Code]
Please help to fix it.
It was the t-sql code in plain text swallowed the CASE statement here...:w00t:
Anyways, Jason has given a much better & a readable solution.....
--Ramesh
October 23, 2007 at 7:37 am
Thanks Ramesh... 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply