CODEWITHSUNDEEP
c#asp.netoracle-databaseplsql
pavuluri vijay kumar
pavuluri vijay kumar

Reputation: 11

patient details based on from date and to date selection and ipno,prno selection

By default I need to get pr no,Ip no,patient name,age,sex,discharge date,department,pay type,status based on from date and to date and also i need to get details based on pr no and ip no CREATE OR REPLACE PROCEDURE SP_CASE_SHEET_ISUBMISSION_LIST

( IFROMDATE DATE, ITODATE DATE, IPRNO NUMBER, IPNO NUMBER, IPAYTYPE VARCHAR, IDEPTCODE VARCHAR, mCursor OUT SYS_REFCURSOR ) AS BEGIN OPEN mCursor FOR SELECT t.PRNO,t.IPATIENTID,t.FIRSTNAME,t.Age,t.SEX,T.DEPTNAME,t.DISGDATE,t.PAYTYPE,nvl( ca.status,'Not Submited') As Status

     FROM V_IPADMISSIONDETAILS t,CA_CASESHEETREPORTSAVE ca;
     IF  IPRNO AND  IPNO IS NULL THEN 
     WHERE t.PAYTYPE = CASE WHEN IPAYTYPE = '0' THEN t.PAYTYPE ELSE IPAYTYPE END

     AND T.PRNO = CASE WHEN IPRNO = 0 THEN T.PRNO ELSE IPRNO END
     AND T.IPATIENTID = CASE WHEN IPNO = 0 THEN T.IPATIENTID ELSE IPNO END
     AND T.DEPTCODE = CASE WHEN IDEPTCODE = '0' THEN T.DEPTCODE ELSE IDEPTCODE END
     AND t.prno=ca.prno(+) AND t.IPATIENTID=ca.ipatientid(+)
     AND ca.status(+)='Received';      
       ELSE           
     WHERE t.Disdate BETWEEN IFROMDATE AND ITODATE 
     AND t.PAYTYPE = CASE WHEN IPAYTYPE = '0' THEN t.PAYTYPE ELSE IPAYTYPE END
     AND T.PRNO = CASE WHEN IPRNO = 0 THEN T.PRNO ELSE IPRNO END
     AND T.IPATIENTID = CASE WHEN IPNO = 0 THEN T.IPATIENTID ELSE IPNO END
     AND T.DEPTCODE = CASE WHEN IDEPTCODE = '0' THEN T.DEPTCODE ELSE IDEPTCODE END
     AND t.prno=ca.prno(+) AND t.IPATIENTID=ca.ipatientid(+)
     AND ca.status(+)='Received'      
     ORDER BY T.DISGDATE;
     END IF;

END;

Upvotes: 0

Views: 759

Answers (1)

Popeye
Popeye

Reputation: 35920

As the only difference in two WHERE clause is t.Disdate BETWEEN IFROMDATE AND ITODATE, it can be handled using CASE ... WHEN as following:

SELECT
    T.PRNO,
    T.IPATIENTID,
    T.FIRSTNAME,
    T.AGE,
    T.SEX,
    T.DEPTNAME,
    T.DISGDATE,
    T.PAYTYPE,
    NVL(CA.STATUS, 'Not Submited') AS STATUS
FROM
    V_IPADMISSIONDETAILS T
    LEFT JOIN CA_CASESHEETREPORTSAVE CA 
    ON ( T.PRNO = CA.PRNO
        AND T.IPATIENTID = CA.IPATIENTID )
WHERE
    T.PAYTYPE = CASE WHEN IPAYTYPE = '0' 
                THEN T.PAYTYPE ELSE IPAYTYPE END
    AND T.PRNO = CASE WHEN IPRNO = 0 
                THEN T.PRNO ELSE IPRNO END
    AND T.IPATIENTID = CASE WHEN IPNO = 0 
                THEN T.IPATIENTID ELSE IPNO END
    AND T.DEPTCODE = CASE WHEN IDEPTCODE = '0' 
                THEN T.DEPTCODE ELSE IDEPTCODE END
    AND CA.STATUS = 'Received'
    AND CASE 
        WHEN IPRNO IS NULL AND IPNO IS NULL THEN 1
        WHEN T.DISDATE BETWEEN IFROMDATE AND ITODATE THEN 1
        ELSE 0
    END = 1
ORDER BY
    CASE WHEN IPRNO IS NOT NULL
        AND IPNO IS NOT NULL THEN T.DISGDATE
    END;

Note: Always use standard ANSI joins.

Cheers!!

Upvotes: 0

Related Questions