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