All Forums Database
krishna.a 8 posts Joined 04/07
02 May 2007
Query Formating in TeraData??

This is the table I have with me.Can one help me in sorting out the query. start_date end_date bus_hol 2007-04-14 2007-05-01 ? 2007-04-03 2007-05-01 2007-11-01 2007-04-18 2007-05-01 2007-11-09 2007-04-01 2007-05-01 2007-09-19 2007-04-02 2007-05-01 2007-09-02 2007-03-29 2007-05-01 2007-08-15 2007-04-04 2007-05-01 2007-11-08 2007-04-07 2007-05-01 2007-12-21 2007-04-17 2007-05-01 ? 2007-04-09 2007-05-01 ? 2007-03-31 2007-05-01 2007-01-26 2007-04-15 2007-05-01 ? 2007-04-10 2007-05-01 ? 2007-04-13 2007-05-01 2007-12-24 2007-04-05 2007-05-01 ? 2007-03-28 2007-05-01 2007-04-06 2007-04-11 2007-05-01 ? 2007-04-12 2007-05-01 ? 2007-03-30 2007-05-01 2007-01-01 2007-04-08 2007-05-01 2007-12-25 2007-04-06 2007-05-01 ? 2007-04-16 2007-05-01 2007-12-31The output should be as follows.The bus_hol should be between start_date and end_date Start_date end_date count(bus_hol) 2007-04-14 2007-05-01 0 . . . . . . 2007-04-05 2007-05-01 1 (bec 2007-04-06 is betwwen start_ date and end_date)Thanks in Advance

Max 13 posts Joined 09/06
02 May 2007

hello...you can try thisselectstart_date,end_date,(CASE WHEN bus_hol BETWEEN start_date AND end_date THEN 1 WHEN bus_hol NOT BETWEEN start_date AND end_date THEN 0END) ResultFROM Massimiliano

joedsilva 505 posts Joined 07/05
02 May 2007

if you want the count to be taken as zero even when bus_hol is null then you will have to use an ELSE 0 clause instead of the last WHEN - THEN clause in the above query.Having said that, why do you call it count ? do you perceive a duplicate combination for the start_date and end_date column values ?

Max 13 posts Joined 09/06
02 May 2007

>if you want the count to be taken as zero even when bus_hol is null then you >will have to use an ELSE 0 >clause instead of the last WHEN - THEN clause in the above queryOpss...that's right !! I've forgotten that...Thanks joedsilva

krishna.a 8 posts Joined 04/07
02 May 2007

Hi all,Im trying to write a query for the following table.This is the table I have with me.Can u help me in sorting out the query? start_date end_date bus_hol 2007-04-14 2007-05-01 ? 2007-04-03 2007-05-01 2007-11-01 2007-04-18 2007-05-01 2007-11-09 2007-04-01 2007-05-01 2007-09-19 2007-04-02 2007-05-01 2007-09-02 2007-03-29 2007-05-01 2007-08-15 2007-04-04 2007-05-01 2007-11-08 2007-04-07 2007-05-01 2007-12-21 2007-04-17 2007-05-01 ? 2007-04-09 2007-05-01 ? 2007-03-31 2007-05-01 2007-01-26 2007-04-15 2007-05-01 ? 2007-04-10 2007-05-01 ? 2007-04-13 2007-05-01 2007-12-24 2007-04-05 2007-05-01 ? 2007-03-28 2007-05-01 2007-04-06 2007-04-11 2007-05-01 ? 2007-04-12 2007-05-01 ? 2007-03-30 2007-05-01 2007-01-01 2007-04-08 2007-05-01 2007-12-25 2007-04-06 2007-05-01 ? 2007-04-16 2007-05-01 2007-12-31The output should be as follows.The bus_hol be between start_date and end_date.we have to check the whole bus_hol column for every start_date and end_date.Every bus_hol have to taken and we have to check bettwn every start_date and end_date,and have to get the count of bus_hol betwn the two dates.For ex:- Start_date end_date count (bus_hol) 2007-04-14 2007-05-01 0 2007-04-03 2007-05-01 1 2007-04-18 2007-05-01 0 . . . . . . . . .Im working with the same for past a week.I didn’t get any idea to implement the same.Thanks in Advance

Barry-1604 176 posts Joined 07/05
02 May 2007

Will this work?select start_dt ,end_dt ,sum(CASE WHEN DT1.hol_dt BETWEEN start_dt and end_dt THEN 1 ELSE 0 END) FROM my_table LEFT OUTER JOIN (SELECT hol_dt FROM my_table GROUP BY 1) DT1 ON 1 = 1 GROUP BY 1,2;

krishna.a 8 posts Joined 04/07
03 May 2007

Thank you very much Barry :-)thats solve my problem.its working.

You must sign in to leave a comment.