NEED HELP WITH CASE STATEMENT [message #18480] |
Fri, 01 February 2002 08:38 |
FRED LAVIGAT
Messages: 2 Registered: February 2002
|
Junior Member |
|
|
HELLO EVERYONE,
I AM TRYING TO USE THE CASE STATEMENT TO DETERMINE A COUNT OF DOCTORS WHO A LEAST HAS WRITTEN A PRESCRIPTION. HOWEVER, I WAS UNSUCCESSFUL!
CAN SOMEONE HELP!!!!!!!?
MY SCRIP LOOKS AS FOLLOW.
SELECT DEPTNO,
SUM((CASE WHEN MONTH_RX_COLUMN>0 THEN 1 ELSE 0 END)) AS MONTLY_MDS,
SUM((CASE WHEN ANNUAL_RX_COLUMN>0 THEN 1 ELSE 0 END)) AS ANNUAL_MDS
FROM RX_DATA
GROUP BY DEPTNO
PLEASE NOTICE THAT I AM LOOKING FOR A VALUE THAT IS GREATER THAN 0.
THANK YOU IN ADVANCE FOR YOUR HELP!
|
|
|
|
|
Re: NEED HELP WITH CASE STATEMENT [message #18521 is a reply to message #18480] |
Mon, 04 February 2002 03:27 |
Jon
Messages: 483 Registered: May 2001
|
Senior Member |
|
|
It seems to be doing what it is supposed to. As Suresh suggested, if this isn't what you were looking for, please provide what you ARE looking for and perhaps we can help.
08:18:35 ==> create table rx_data (deptno number,
08:18:35 2 month_rx_column number,
08:18:35 3 annual_rx_column number);
Table created.
08:18:35 ==> insert into rx_data values(1,3,15);
1 row created.
08:18:35 ==> insert into rx_data values(1,0,15);
1 row created.
08:18:35 ==> insert into rx_data values(1,3,18);
1 row created.
08:18:35 ==> insert into rx_data values(2,0,0);
1 row created.
08:18:36 ==> insert into rx_data values(3,13,13);
1 row created.
08:18:36 ==> insert into rx_data values(3,8,21);
1 row created.
08:18:36 ==> SELECT DEPTNO,
08:18:56 2 SUM((CASE WHEN MONTH_RX_COLUMN>0 THEN 1 ELSE 0 END)) AS MONTLY_MDS,
08:18:56 3 SUM((CASE WHEN ANNUAL_RX_COLUMN>0 THEN 1 ELSE 0 END)) AS ANNUAL_MDS
08:18:56 4 FROM RX_DATA
08:18:56 5 GROUP BY DEPTNO
08:18:56 6
08:18:56 ==> ;
1 SELECT DEPTNO,
2 SUM((CASE WHEN MONTH_RX_COLUMN>0 THEN 1 ELSE 0 END)) AS MONTLY_MDS,
3 SUM((CASE WHEN ANNUAL_RX_COLUMN>0 THEN 1 ELSE 0 END)) AS ANNUAL_MDS
4 FROM RX_DATA
5* GROUP BY DEPTNO
08:18:57 ==> /
DEPTNO MONTLY_MDS ANNUAL_MDS
--------- ---------- ----------
1 2 3
2 0 0
3 2 2
|
|
|