April 2, 2009 at 4:08 pm
Hello,
I have updated to SQL 2005 and need to refactor some old code and i can't figure out how to convert the following select to ANSI syntax (and get the same result).
this is the old code:
select edl.date_list, r.room_id
from ttc_room_tracker rt, ttc_event_date_list edl, ttc_room r
where rt.event_date =* edl.date_list and
rt.room_id =* r.room_id and
edl.date_list >= '2009-04-02' and
edl.date_list <= '2009-04-09' and
rt.location_id = 2 and r.location_id = 2
order by edl.date_list
and this is the result I get (which is what I need):
02/04/2009 12:00:00.000 AM70
02/04/2009 12:00:00.000 AM440
02/04/2009 12:00:00.000 AM441
02/04/2009 12:00:00.000 AM829
03/04/2009 12:00:00.000 AM70
03/04/2009 12:00:00.000 AM440
03/04/2009 12:00:00.000 AM441
03/04/2009 12:00:00.000 AM829
04/04/2009 12:00:00.000 AM70
04/04/2009 12:00:00.000 AM440
04/04/2009 12:00:00.000 AM441
04/04/2009 12:00:00.000 AM829
05/04/2009 12:00:00.000 AM70
05/04/2009 12:00:00.000 AM440
05/04/2009 12:00:00.000 AM441
05/04/2009 12:00:00.000 AM829
06/04/2009 12:00:00.000 AM70
06/04/2009 12:00:00.000 AM440
06/04/2009 12:00:00.000 AM441
06/04/2009 12:00:00.000 AM829
07/04/2009 12:00:00.000 AM70
07/04/2009 12:00:00.000 AM440
07/04/2009 12:00:00.000 AM441
07/04/2009 12:00:00.000 AM829
08/04/2009 12:00:00.000 AM70
08/04/2009 12:00:00.000 AM440
08/04/2009 12:00:00.000 AM441
08/04/2009 12:00:00.000 AM829
09/04/2009 12:00:00.000 AM70
09/04/2009 12:00:00.000 AM440
09/04/2009 12:00:00.000 AM441
09/04/2009 12:00:00.000 AM829
The new ANSI code to:
select edl.date_list, r.room_id
from ttc_room_tracker rt right outer join ttc_event_date_list edl on (rt.event_date = edl.date_list and
edl.date_list >= '2009-04-02' and
edl.date_list <= '2009-04-09' and
rt.location_id = 2)
right outer join ttc_room r on rt.room_id = r.room_id and r.location_id = 2
order by edl.date_list
and i get this:
[NULL]30
[NULL]31
[NULL]69
[NULL]70
[NULL]440
[NULL]441
[NULL]829
I have tried everything I could think of and no luck.
Any help is greatly appreciated
Thx
Mike
April 3, 2009 at 3:50 am
Try:
FROM ttc_room r
CROSS JOIN ttc_event_date_list edl
LEFT JOIN ttc_room_tracker rt
ON edl.date_list = rt.event_date
AND r.room_id = rt.room_id
AND rt.location_id = 2
WHERE r.location_id = 2
AND edl.date_list BETWEEN '20090402' AND '20090409'
April 3, 2009 at 4:12 am
What about this...
SELECT edl.date_list, r.room_id
FROM ttc_room_tracker rt
RIGHT OUTER JOIN ttc_event_date_list edl
ON rt.event_date = edl.date_list
RIGHT OUTER JOIN ttc_room r
ON rt.room_id = r.room_id
WHERE rt.location_id = 2 AND r.location_id = 2
AND edl.date_list >= '2009-04-02' AND edl.date_list <= '2009-04-09'
"Keep Trying"
April 3, 2009 at 4:15 am
Chirag (4/3/2009)
WHERE rt.location_id = 2
I think this will take out the NULLs in rt
April 3, 2009 at 5:15 am
Thank you all for the quick response.
Mr or Mrs 500 is the correct solution.
I was losing my mind...
You made my day
Thank again
Mike
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy