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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.