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?
LVL 1
rwheeler23Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
SELECT PatientID,
    ISNULL(MAX(CASE WHEN row_num = 1 THEN CopayAmount END), 0) AS Copay1,
    ISNULL(MAX(CASE WHEN row_num = 2 THEN CopayAmount END), 0) AS Copay2,
    ISNULL(MAX(CASE WHEN row_num = 3 THEN CopayAmount END), 0) AS Copay3
FROM (
    SELECT PatientID, CopayAmount, ROW_NUMBER() OVER(PARTITION BY PatientID ORDER BY InsCompID) AS row_num
    FROM PINSTBLE
) AS derived
GROUP BY PatientID
--ORDER BY PatientID
0
 
rwheeler23Author Commented:
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]
0
 
rwheeler23Author Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PortletPaulfreelancerCommented:
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

What is the data type of [Inscopay]?
i.e. does it really need to be converted?

Can  [Inscopay] actually be NULL?
i.e. while a payment may be missing that means a whole row is missing not just that  [Inscopay] is null.

I don't believe that coalesce() is needed & the pivoted figures are handled by ISNULL() anyway.


INNER JOIN TEMPCHRG T2 ON T1.AccountNumber=T2.ACCOUNTNO
-- maybe, know nothing about that table

LEFT OUTER JOIN TEMPPAYM T3 ON T2.TRANSACTIONNO = T3.PAYMTRXNMBR AND T3.DEPARTMENT='CP'
-- maybe, again nothing much is known about the table except temppaym.department = 'CP'
-- it is valid syntax for a join condition

ps:
In my experience it is easier in the long run to start out with full tables definitions using full names, rather than starting with pseudo names for tables/fields
0
 
rwheeler23Author Commented:
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
0
 
rwheeler23Author Commented:
Thanks for helping with this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.