Q

Showing one line per patient per day with GROUP BY, part 2

Continued from Part 1...

Creating and Populating the Tables

--  ***********************
--  **  p r o v i d e r  **
--  ***********************

DROP TABLE    provider;

CREATE TABLE  provider
(
    provider_id         VARCHAR2 (10)  PRIMARY KEY,
    provider_specialty  NUMBER (8)     UNIQUE
);

INSERT INTO provider (provider_id, provider_specialty)
              VALUES ('M',         1);
INSERT INTO provider (provider_id, provider_specialty)
              VALUES ('R',         3);
INSERT INTO provider (provider_id, provider_specialty)
              VALUES ('Y',         2);


--  *********************
--  **  s e r v i c e  **
--  *********************

DROP TABLE    service;

CREATE TABLE  service
(
    patient_id    NUMBER (8),
    service_date  DATE,
    provider_id   VARCHAR2 (10)
);

INSERT INTO service (patient_id, service_date, provider_id)
     VALUES         (1           '1-Jan-2003', 'M');
INSERT INTO service (patient_id, service_date, provider_id)
     VALUES         (1,          '1-Jan-2003', 'Y');
INSERT INTO service (patient_id, service_date, provider_id)
     VALUES         (2,          '1-Jan-2003', 'R');
INSERT INTO service (patient_id, service_date, provider_id)
     VALUES         (2,          '1-Jan-2003', 'M');
INSERT INTO service (patient_id, service_date, provider_id)
     VALUES         (3,          '1-Jan-2003', 'Y');

Initial Query

COLUMN provider_id FORMAT A11
COLUMN service_date FORMAT A12

SELECT    patient_id,
          TO_CHAR (service_date, 'MM/DD/YYYY')
              AS service_date,
          provider_id,
          provider_specialty
FROM      service  s    JOIN
          provider p    USING (provider_id)
ORDER BY  service_date,
          patient_id,
          provider_specialty;

This was first published in September 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close