Help with a query

  • 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

  • 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

  • If clinic is inpatient then it is inpatient.Else it is Outpatient.

  • 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

  • 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