Hello All,

I need little help with this query. I want the output similar to the First output shown here and which is correct .

select to_char(a.start_date,'mm/dd/yyyy hhAM') Stdt,to_char(a.end_date,'mm/dd/yyyy hhAM')endt,
a.SHOWTYPE, a.DESCTV
from acntv.SHOWS a,intra.pbb_batch_sum b
where a.Start_date between
to_date('07242003070000','MMDDYYYYHH24MISS')
and to_date('07252003035959','MMDDYYYYHH24MISS')
and a.batch=b.batch(+)
group by to_char(a.start_date,'mm/dd/yyyy hhAM'),to_char(a.end_date,'mm/dd/yyyy hhAM'),
a.SHOWTYPE, a.DESCTV ;
stdt endt SHOWTYPE DESCTV
--------------- --------------- -------------------------------- --------------------
07/24/2003 07AM 07/24/2003 12PM JEWELRY Jewelry with Angie
07/24/2003 10PM 07/25/2003 03AM GEMSTONES Gemstones with Tommy
07/24/2003 12PM 07/24/2003 05PM JEWELRY Jewelry with Heidi
07/24/2003 05PM 07/24/2003 10PM JEWELRY Jewelry with Misty
07/25/2003 03AM 07/25/2003 07AM JEWELRY Jewelry with Robert

If I happen to pass parameter as start date and end date, I get different result,since our date of operation is different and I need the same output as the first result shown above. Can somebody help me?

select to_char(a.start_date,'mm/dd/yyyy hhAM')stdt,to_char(a.end_date,'mm/dd/yyyy hhAM')endt,
a.SHOWTYPE, a.DESCTV
from acntv.SHOWS a,intra.pbb_batch_sum b
where a.Start_date between
to_date('&st','MMDDYYYYHH24MISS')
and to_date('&en','MMDDYYYYHH24MISS')
and a.batch=b.batch(+)
group by to_char(a.start_date,'mm/dd/yyyy hhAM'),to_char(a.end_date,'mm/dd/yyyy hhAM'),
a.SHOWTYPE, a.DESCTV ;
Enter value for st: 07242003
old 7: to_date('&st','MMDDYYYYHH24MISS')
new 7: to_date('07242003','MMDDYYYYHH24MISS')
Enter value for en: 07252003
old 8: and to_date('&en','MMDDYYYYHH24MISS')
new 8: and to_date('07252003','MMDDYYYYHH24MISS')

stdt endt SHOWTYPE DESCTV
--------------- --------------- -------------------------------- --------------------------------
07/24/2003 10PM 07/25/2003 03AM GEMSTONES Gemstones with Tommy
07/24/2003 12PM 07/24/2003 05PM JEWELRY Jewelry with Heidi
07/24/2003 05PM 07/24/2003 10PM JEWELRY Jewelry with Misty
07/24/2003 03AM 07/24/2003 07AM GEMSTONES Gemstones with Geoff and Cheryl
07/24/2003 07AM 07/24/2003 12PM JEWELRY Jewelry with Angie

Pls suggest how I should do it.

Thanks