asked on

SQL Query syntax to force a set number of records returned

I need to set up a query that has to return whether a patient has made their copay for procedures performed. The report always must always return 3 copays. So here is a rough table outline.

(CHRGTBLE)Charge Table: PatientID, Date,ChargeAmount
(PINSTBLE)Patient Ins Table: PatientID,InsCompID,CopayAmount

so the data may look something like this:
Charges:
1000,12/31/14,4000.00
1001,01/31/14,2000.00

Patient Insurances:
1000,INS01,20.00
1000,INS02,30.00
1000,INS03,20.00
1001,INS01,15.00
1001,INS02,20.00

The output needs to look like:
1000,20.00,30.00,20.00
1001,15.00,20.00,0.00

Flipping the data from rows to columns can be done with a CASE statement but how do I always force it to retrieve 3 rows even when there may be less than three rows?

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwheeler23

ASKER

Wow, that is a construct I have never seen before. It works like a charm however I need to take it to the next level. What this script does is give me a list of the top three copays for each patient. The next step is to include this in a script that will look at each charge for a patient and show me the copay made for each patient per charge. There is a distinct possibility that no copay was collected or paid. That is what this report is all about. Finding when a copay has not been made.

I have modified your excellent example with the real field names that also includes a join to the charges table plus provided the field names for the charge and payments table. The field tempchrg.TRANSACTIONNO = temppaym.CHRGTRXNMBR. This is the field that links the charge transaction number to the payment transaction charge to which the payment was applied. Right now I see what the copays are supposed to be per charge. The final step is to show what the copays actually were per charge

SELECT * FROM
(SELECT AccountNumber,
ISNULL(MAX(CASE WHEN row_num = 1 THEN InsCopay END), 0) AS Copay1,
ISNULL(MAX(CASE WHEN row_num = 2 THEN InsCopay END), 0) AS Copay2,
ISNULL(MAX(CASE WHEN row_num = 3 THEN InsCopay END), 0) AS Copay3
FROM (
SELECT AccountNumber, COALESCE(CONVERT(DECIMAL(19,5),InsCopay),0.00) as Inscopay, ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY InsuranceNumber) AS row_num
FROM PATIENTINSURANCES
) AS derived
GROUP BY AccountNumber) T1
INNER JOIN TEMPCHRG T2 ON T1.AccountNumber=T2.ACCOUNTNO
ORDER BY AccountNumber

-- Charges table
SELECT [TRANSACTIONNO],[POSTINGDATE],[ACCOUNTNO],[DEPARTMENT],[CPT],[SQLAMOUNT],[SQLAMTPAID],[POS],[OPERATOR],[POSTORDER],[SERVICEDATE]
FROM [BIZOFFRPT].[dbo].[TEMPCHRG]

-- Payments table
SELECT [CHRGTRXNMBR],[POSTINGDATE],[ACCOUNTNO],[DEPARTMENT],[INVERSEFINANCEKEY],[TRANSACTIONAMOUNT],[POS],[OPERATOR],[TRANSACTIONDATE],[AMOUNT],[PAYMTRXNMBR],[ID],[iCODE],[DESCRIPTION]
FROM [BIZOFFRPT].[dbo].[TEMPPAYM]

rwheeler23

ASKER

By the way, I was just informed that temppaym.department = 'CP' that means it was a copayment. Does this look correct to you?

SELECT * FROM
(SELECT AccountNumber,
ISNULL(MAX(CASE WHEN row_num = 1 THEN InsCopay END), 0) AS Copay1,
ISNULL(MAX(CASE WHEN row_num = 2 THEN InsCopay END), 0) AS Copay2,
ISNULL(MAX(CASE WHEN row_num = 3 THEN InsCopay END), 0) AS Copay3
FROM (
SELECT AccountNumber, COALESCE(CONVERT(DECIMAL(19,5),InsCopay),0.00) as Inscopay, ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY InsuranceNumber) AS row_num
FROM PATIENTINSURANCES
) AS derived
GROUP BY AccountNumber) T1
INNER JOIN TEMPCHRG T2 ON T1.AccountNumber=T2.ACCOUNTNO
LEFT OUTER JOIN TEMPPAYM T3 ON T2.TRANSACTIONNO = T3.PAYMTRXNMBR AND T3.DEPARTMENT='CP'

ORDER BY AccountNumber,T2.POSTINGDATE

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwheeler23

ASKER

Here is the final product. The data is extracted out of a Mumps database and sometimes numbers are store as characters. The final version adds patient and insurance company names.

SELECT T1.AccountNumber as [Account No],T8.Name,T2.SERVICEDATE as DOS,T2.CPT,T1.Copay1,T1.Copay2,T1.Copay3,ISNULL(CONVERT(DECIMAL(19,5),T3.TRANSACTIONAMOUNT)/100.00,0.00) AS [Trx Amount],ISNULL(T3.AMOUNT/100.00,0.00) AS Amount,ISNULL(T5.InsName,'') as [Ins Name 1],ISNULL(T6.InsName,'') as [Ins Name 2],ISNULL(T7.InsName,'') AS [Ins Name 3] FROM
(SELECT AccountNumber,
ISNULL(MAX(CASE WHEN row_num = 1 THEN InsCopay/100.00 END), 0) AS Copay1,
ISNULL(MAX(CASE WHEN row_num = 2 THEN InsCopay/100.00 END), 0) AS Copay2,
ISNULL(MAX(CASE WHEN row_num = 3 THEN InsCopay/100.00 END), 0) AS Copay3,
       ISNULL(MAX(CASE WHEN row_num = 1 THEN InsuranceCode END) ,'') AS InsCode1,
       ISNULL(MAX(CASE WHEN row_num = 2 THEN InsuranceCode END) ,'') AS InsCode2,
       ISNULL(MAX(CASE WHEN row_num = 3 THEN InsuranceCode END) ,'') AS InsCode3
FROM (
SELECT AccountNumber,InsuranceCode, COALESCE(CONVERT(DECIMAL(19,5),InsCopay),0.00) as Inscopay, ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY InsuranceNumber) AS row_num
FROM PATIENTINSURANCES
) AS derived
GROUP BY AccountNumber) T1
INNER JOIN TEMPCHRG T2 ON T1.AccountNumber=T2.ACCOUNTNO
LEFT OUTER JOIN TEMPPAYM T3 ON T2.TRANSACTIONNO = T3.PAYMTRXNMBR AND T3.DEPARTMENT='CP'
LEFT OUTER JOIN INSURANCES T5 ON T1.InsCode1=T5.InsCode
LEFT OUTER JOIN INSURANCES T6 ON T1.InsCode2=T6.InsCode
LEFT OUTER JOIN INSURANCES T7 ON T1.InsCode3=T7.InsCode
INNER JOIN DEMOGRAPHICS T8 ON T1.AccountNumber=T8.ACCOUNT
ORDER BY T2.SERVICEDATE

rwheeler23

ASKER

Thanks for helping with this.