December 18, 2011 at 4:18 am
Dear Gurus,
I have a table whose format and data is given below.I want a help with a query which will give me the results as shown below -
DateOutpatients InpatientsUCCWomens Clinic
1-Jul-11 1 1 1 0
2-Jul-11 9 0 0 5
3-Jul-11 5 1 1 0
The Table and its data is below - Any helo will be greatly appreciated.
CREATE TABLE XXOH_DAILY
(
Patient_Id NVARCHAR(10),
Visit_Date DATETIME,
Clinic NVARCHAR(50),
Birth VARCHAR(3)
)
----------------------
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'2275', '07/01/2011 12:00:00 AM', 'UCC', '2')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'182638', '07/01/2011 12:00:00 AM', 'Inpatient'
, '1')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'180552', '07/02/2011 12:00:00 AM', 'Womens Clinic'
, '2')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'55168', '07/02/2011 12:00:00 AM', 'Womens Clinic'
, '2')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'182696', '07/02/2011 12:00:00 AM', 'Internal Med. Clinic'
, '2')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'99289', '07/02/2011 12:00:00 AM', 'Womens Clinic'
, '2')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'176510', '07/02/2011 12:00:00 AM', 'Pediatrics Clinic'
, '2')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'113101', '07/02/2011 12:00:00 AM', 'Internal Med. Clinic'
, '2')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'166826', '07/02/2011 12:00:00 AM', 'Surgery Clinic'
, '2')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'167440', '07/02/2011 12:00:00 AM', 'Womens Clinic'
, '2')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'148813', '07/02/2011 12:00:00 AM', 'Womens Clinic'
, '2')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'99979', '07/03/2011 12:00:00 AM', 'Womens Clinic'
, '2')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'94236', '07/03/2011 12:00:00 AM', 'Pharmacy'
, '2')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'15732', '07/03/2011 12:00:00 AM', 'Radiology'
, '2')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'43558', '07/03/2011 12:00:00 AM', 'UCC', '2')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'97379', '07/03/2011 12:00:00 AM', 'Ophthalmology Clinic'
, '2')
GO
INSERT INTO XXOH_DAILY ( PATIENT_ID, VISIT_DATE, CLINIC, BIRTH ) VALUES (
'182638', '07/03/2011 12:00:00 AM', 'Inpatient'
, '2')
GO
December 18, 2011 at 4:46 am
I don't understand the way you get the outpatients value. This is the closest I can get you without more info.
SELECT VISIT_DATE
, COUNT(*) AS OutPatients
, SUM(CASE WHEN CLINIC = 'Inpatient' THEN 1 ELSE 0 END) AS Inpatient
, SUM(CASE WHEN CLINIC = 'UCC' THEN 1 ELSE 0 END) AS UCC
, SUM(CASE WHEN CLINIC = 'Womens Clinic' THEN 1 ELSE 0 END) AS 'Women''s Clinic'
FROM XXOH_DAILY GROUP BY VISIT_DATE
December 18, 2011 at 4:55 am
If clinic is inpatient then it is inpatient.Else it is Outpatient.
December 18, 2011 at 4:59 am
SELECT VISIT_DATE
, SUM(CASE WHEN CLINIC <> 'Inpatient' THEN 1 ELSE 0 END) AS OutPatients
, SUM(CASE WHEN CLINIC = 'Inpatient' THEN 1 ELSE 0 END) AS Inpatient
, SUM(CASE WHEN CLINIC = 'UCC' THEN 1 ELSE 0 END) AS UCC
, SUM(CASE WHEN CLINIC = 'Womens Clinic' THEN 1 ELSE 0 END) AS 'Women''s Clinic'
FROM XXOH_DAILY GROUP BY VISIT_DATE
December 18, 2011 at 5:00 am
Thanks a lot.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply