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

Help Reqd Urgently (SQL Query)

Pls consider the following table:--

Table Name: ChemoAdmin

Fields:

Field Name: Field Type: Typical Value:

patientID (Varchar 10) XYZABC001
stationDate (DateTime) 09/17/2004
drugName (Varchar 25) Drug 1
dose (Numeric) 5
doseUnit (Varchar 5) mg

I require the following output using one query:--

DATE 09/17/2004 09/21/2004

Drug 1 5 mg Nil
Drug 2 2 mg 4 mg
Drug 3 Nil 1 mg

Pls help.You mean you'd like a specify a daterange and have it shown above the other results returned by the same select-statement (seperated by an empty line)?

Is there any reason why you need this in a single sql-statement?|||It is better if this can be done using one select statement. Because, I would like to create a view that would give the output as shown.|||I don't see a way of doing that in a view. You might want to create a stored procedure instead that produces both the daterange as well as the select output. However, I feel that your aim is off.|||this requires a full outer join

however, i personally never write a full outer join, particularly when there is a join condition on only one of the tables, as in this case

i always write a full outer join as a left outer join unioned with a right outer join where there's no matching row (and then i always flip the right outer join over into a left)
select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t1
left outer
join ChemoAdmin as t2
on t1.drugName
= t2.drugName
and t2.stationDate = '2004-09-21'
where t1.stationDate = '2004-09-17'
union all
select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t2
left outer
join ChemoAdmin as t1
on t2.drugName
= t1.drugName
and t1.stationDate = '2004-09-17'
where t2.stationDate = '2004-09-21'
and t1.drugName is null|||this requires a full outer join

however, i personally never write a full outer join, particularly when there is a join condition on only one of the tables, as in this caseOk, that just begs the question: Why do you avoid FULL OUTER JOIN when it does exactly what is needed? While I use FULL and CROSS joins rather sparingly, when they do exactly what I want I'll cheerfully use the little beggars.

-PatP|||because not every database supports FULL OUTER (yes, i know that microsoft sql server does, but it's easier remembering the workaround than trying to remember which database supports it)

but more importantly, in this instance the join condition include conditions on only one table or the other

i'm not certain that this will produce the same results:select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t1
full outer
join ChemoAdmin as t2
on t1.drugName
= t2.drugName
and t1.stationDate = '2004-09-17'
and t2.stationDate = '2004-09-21'
feel like testing it for us? :)|||Because your code tests both tables, it effectively reduces the join to a conventional INNER join. Any result set rows that "miss" (either right or left) will fail due to the comparison with NULL. The only difference between the two (FULL versus unions of left and right) would be if you used UNION instead of UNION ALL.

-PatP|||so you're saying that the FULL OUTER example that i posted will not work?

so what would you do to make it work in this particular example?

don't forget, you pooh-poohed my original solution and suggested i should've used a FULL OUTER, and i'd like to see it|||Can any drug in your list have more than 2 dosages ? if so joining the table just twice wont work. Use the following query to find the maximum number of doses for any drug in that table.

select max(counter) max_number from
(select drugName, count(*) counter from ChemoAdmin
group by ChemoAdmin) derived

if the max_number is very high and likely to be a variable then the best way is to use a stored procedure.

if it is just 2 or 3 then, the you can use the self left outer joins not a full outer join

for instance if the max_number is 2 then

select t1.drugName
, t1.dose
, t1.doseUnit
, t2.dose
, t2.doseUnit
from ChemoAdmin as t1
left outer
join ChemoAdmin as t2
on t1.drugName
= t2.drugName
and t1.stationDate >= '2004-09-17'
and t1.stationDate < '2004-09-21'
and t2.stationDate >= '2004-09-17'
and t2.stationDate < '2004-09-21'
and t1.dose <> t2.dose
and t1.doseUnit <> t2.doseUnit

if the max_number is 3 then join the result again with the table and so on, but do not join too many times though because it would cause performance problems.|||so you're saying that the FULL OUTER example that i posted will not work?

so what would you do to make it work in this particular example?

don't forget, you pooh-poohed my original solution and suggested i should've used a FULL OUTER, and i'd like to see itI didn't pooh on anything. In my first post I just asked why you avoided a FULL join when I use them occaisionally.

The point that I raised in my second post is that if you look at things from the set based perspective, if either set it empty (returning NULL in SQL) then the WHERE clause comparison has to fail, reducing the result set to the intersection (an INNER join in SQL) or less.

Sorry if you got the impression that I was denigrating your work around for a full join. I didn't mean to do that at all.

I'm still not at all convinced that we understand what the original poster wanted. They showed us a third of the equation (the results they want), without giving us either the input data or the rules they used to determine the output. Before we debate the relative merits of solutions, we need to understand what the rules are!

-PatP|||I'm still not at all convinced that we understand what the original poster wanted. so true

but then, the most interesting threads are where the original poster asks something innocuous (to them) and we just run with it in all sorts of directions

:rolleyes:

Help Reqd Urgently (SQL Query)

Pls consider the following table:--

Table Name: ChemoAdmin

Fields:

Field Name: Field Type: Typical Value:

patientID (Varchar 10) XYZABC001
stationDate (DateTime) 09/17/2004
drugName (Varchar 25) Drug 1
dose (Numeric) 5
doseUnit (Varchar 5) mg

I require the following output using one query:--

DATE 09/17/2004 09/21/2004

Drug 1 5 mg Nil
Drug 2 2 mg 4 mg
Drug 3 Nil 1 mg

Pls help.Sorry, but I'm confused. What is your question?

-PatP