Help w/sum pls

[Mike_B]

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', '4/10/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 'T1'
WHEN datediff(day, day_in, day_out) between 3 and 7 THEN 'T2'
ELSE '' END as TEST_TIME

FROM #testtb

 

[Mike_B]

Here's a better query. Sorry for the confusion:

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

 

[John Vinson]

Here's a better query. Sorry for the confusion:

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

JET SQL does not support the CASE operator. This would be valid T-SQL
in SQL/Server, but that's a different dialect!

John W. Vinson[MVP]