Query Formating in TeraData??

Database

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-31

The 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

6 REPLIES
Max
N/A

Re: Query Formating in TeraData??

hello...

you can try this

select
start_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 0
END) Result
FROM

Massimiliano

Re: Query Formating in TeraData??

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
N/A

Re: Query Formating in TeraData??

>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

Opss...that's right !! I've forgotten that...
Thanks joedsilva

Re: Query Formating in TeraData??

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-31

The 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

Re: Query Formating in TeraData??

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;

Re: Query Formating in TeraData??

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