Advertising
- Unnamed
- Wednesday, May 16th, 2012 at 9:24:46am MDT
- CREATE OR REPLACE VIEW OPERATING_ROOM_DATA AS
- SELECT DISTINCT P.ROOM_NUMBER, --Operating room number
- TO_CHAR(START_DATE,'MM/DD/YYYY') AS USAGE_DATE, --Date
- SUM(ROUND((CAST(END_DATE AS DATE) - CAST(START_DATE AS DATE))*24)) OVER (PARTITION BY TO_CHAR(START_DATE,'MMDDYYYY')) AS TOTAL_HOURS, --Total Usage that day
- SUM(ROUND((CAST(END_DATE AS DATE) - CAST(START_DATE AS DATE))*24)) OVER (PARTITION BY TO_CHAR(START_DATE,'MMDDYYYY')) * O.COST AS TOTAL_COST, --Total charge
- COUNT(START_DATE) OVER (PARTITION BY TO_CHAR(START_DATE,'MMDDYYYY')) AS TOTAL_PEOPLE --Number of Patients
- FROM PATIENT_OPERATION P
- INNER JOIN OPERATING_ROOM O ON O.ROOM_NUMBER = P.ROOM_NUMBER
- ORDER BY P.ROOM_NUMBER, TO_CHAR(START_DATE,'MM/DD/YYYY');
- --E: re investigate the joins
- CREATE VIEW STATISTICS_BY_DEPARTMENT AS
- SELECT CNT.DEPARTMENT, --DEPARTMENT NAME
- NVL(X.YEAR,0) AS YEAR, --YEAR
- CNT.TOTALBEDS AS TOTAL_BEDS, --TOTAL BEDS IN DEPARTMENT
- NVL(X.DAYS,0) AS NUMBER_DAYS --NUMBER DAYS USED
- FROM
- (SELECT D.DEPARTMENT AS DEPARTMENT,
- COUNT(RB.BED_ID) AS TOTALBEDS
- FROM DEPARTMENT D
- LEFT OUTER JOIN DEPARTMENT_ROOM DR ON DR.DEPARTMENT = D.DEPARTMENT
- LEFT OUTER JOIN ROOM R ON R.ROOM_NUMBER = DR.ROOM_NUMBER
- LEFT OUTER JOIN ROOM_BED RB ON RB.ROOM_NUMBER = R.ROOM_NUMBER
- GROUP BY D.DEPARTMENT
- ) CNT
- LEFT OUTER JOIN
- (SELECT DEPARTMENT,
- TO_CHAR(DATE_ADMITTED,'YYYY') AS YEAR,
- SUM(DATE_DISCHARGED-DATE_ADMITTED) AS DAYS
- FROM PATIENT_ADMISSION
- GROUP BY DEPARTMENT,
- TO_CHAR(DATE_ADMITTED,'YYYY')
- ) X
- ON X.DEPARTMENT = CNT.DEPARTMENT;
advertising
Update the Post
Either update this post and resubmit it with changes, or make a new post.
You may also comment on this post.
Please note that information posted here will expire by default in one month. If you do not want it to expire, please set the expiry time above. If it is set to expire, web search engines will not be allowed to index it prior to it expiring. Items that are not marked to expire will be indexable by search engines. Be careful with your passwords. All illegal activities will be reported and any information will be handed over to the authorities, so be good.