Help w/sum pls

Hi,
I'd like to add the results of T1 and T2 in the query below but I just
cannot see to get it rt.
I tried Sum(T1) and Sum(cast(T1 as int)) but no go.
What's the trick?
TIA
Mike
DROP TABLE #testtb
CREATE TABLE #testtb (day_in datetime, day_out datetime)
INSERT INTO #testtb (day_in, day_out) VALUES ('3/30/2006', '3/31/2006')
INSERT INTO #testtb (day_in, day_out) VALUES ('2/28/2006', '3/3/2006')
INSERT INTO #testtb (day_in, day_out) VALUES ('1/2/2006', '5/5/2006')
INSERT INTO #testtb (day_in, day_out) VALUES ('3/30/2006','4/5/2006')
SELECT day_in, day_out,
CASE WHEN datediff(day, day_in, day_out) between 0 and 3 THEN 1 ELSE 0 END
AS T1,
CASE WHEN datediff(day, day_in, day_out) between 3 and 7 THEN 1 ELSE 0 END
AS T2
FROM #testtb
Results should be:
2 periods meet the condition in T1
1 period meet the conditions in T2
Therefore:
Sum of T1 = 2
Sum of T2 = 1Mike
I'm . I got the output four rows as
t1 t2
1 1
1 0
0 1
0 0
What is supposed to be? I think for the t2 should be 2 periods as well ,
isn't?
"Mike_B" <nospam@.yahoo.com> wrote in message
news:eL%23JBQUWGHA.752@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I'd like to add the results of T1 and T2 in the query below but I just
> cannot see to get it rt.
> I tried Sum(T1) and Sum(cast(T1 as int)) but no go.
> What's the trick?
> TIA
> Mike
> DROP TABLE #testtb
> CREATE TABLE #testtb (day_in datetime, day_out datetime)
> INSERT INTO #testtb (day_in, day_out) VALUES ('3/30/2006', '3/31/2006')
> INSERT INTO #testtb (day_in, day_out) VALUES ('2/28/2006', '3/3/2006')
> INSERT INTO #testtb (day_in, day_out) VALUES ('1/2/2006', '5/5/2006')
> INSERT INTO #testtb (day_in, day_out) VALUES ('3/30/2006','4/5/2006')
>
> SELECT day_in, day_out,
> CASE WHEN datediff(day, day_in, day_out) between 0 and 3 THEN 1 ELSE 0 END
> AS T1,
> CASE WHEN datediff(day, day_in, day_out) between 3 and 7 THEN 1 ELSE 0 END
> AS T2
> FROM #testtb
> --
> Results should be:
> 2 periods meet the condition in T1
> 1 period meet the conditions in T2
> Therefore:
> Sum of T1 = 2
> Sum of T2 = 1
>|||Is this what you want?
Note that BETWEEN is inclusive so the second between should
be from 4 to 7.
SELECT
SUM(CASE WHEN datediff(day, day_in, day_out) between 0 and 3 THEN 1
ELSE 0 END)
AS T1,
SUM(CASE WHEN datediff(day, day_in, day_out) between 4 and 7 THEN 1
ELSE 0 END)
AS T2
FROM #testtb|||Hi Mike,
First I think you have a logical bug. The second line you insert to the
table, has a difference of 3 days, which means it is evaluated both by T1 an
d
T2 thouse cousing the sum results of them to be T1=2 and T2=2.
Second:
you can get the sum only if you do not return the dates (otherwise you will
get a sum only for dates that are alike both for the in_day and the out_day.
the query should be like this:
SELECT
SUM(CASE WHEN datediff(day, day_in, day_out) between 0 and 3 THEN 1 ELSE 0
END)
AS T1,
SUM(CASE WHEN datediff(day, day_in, day_out) between 3 and 7 THEN 1 ELSE 0
END)
AS T2
FROM #testtb
GuyBar
"Mike_B" wrote:

> Hi,
> I'd like to add the results of T1 and T2 in the query below but I just
> cannot see to get it rt.
> I tried Sum(T1) and Sum(cast(T1 as int)) but no go.
> What's the trick?
> TIA
> Mike
> DROP TABLE #testtb
> CREATE TABLE #testtb (day_in datetime, day_out datetime)
> INSERT INTO #testtb (day_in, day_out) VALUES ('3/30/2006', '3/31/2006')
> INSERT INTO #testtb (day_in, day_out) VALUES ('2/28/2006', '3/3/2006')
> INSERT INTO #testtb (day_in, day_out) VALUES ('1/2/2006', '5/5/2006')
> INSERT INTO #testtb (day_in, day_out) VALUES ('3/30/2006','4/5/2006')
>
> SELECT day_in, day_out,
> CASE WHEN datediff(day, day_in, day_out) between 0 and 3 THEN 1 ELSE 0 EN
D
> AS T1,
> CASE WHEN datediff(day, day_in, day_out) between 3 and 7 THEN 1 ELSE 0 EN
D
> AS T2
> FROM #testtb
> --
> Results should be:
> 2 periods meet the condition in T1
> 1 period meet the conditions in T2
> Therefore:
> Sum of T1 = 2
> Sum of T2 = 1
>
>|||Between includes both start and end limit.
so fot t2 it should be 4 and 7.
Regards
Amish Shah