I need to GROUP Payments Table BY BillingId, to get below columns (have problems with bold columns):
Billing Id
Total Number Of ANY RESPONSES Made by Insurance
Earliest ANY RESPONSE (Made by Insurance) Received On
Earliest ANY RESPONSE (Made by Insurance) Insurance
Earliest ANY RESPONSE (Made by Insurance) Type
Earliest ANY RESPONSE (Made by Insurance) Amount
Total Number Of PAYMENTS Made by Insurance
Latest PAYMENT (Made by Insurance) Received On
Latest PAYMENT (Made by Insurance) Insurance
Latest PAYMENT (Made by Insurance) Type
Latest PAYMENT (Made by Insurance) Amount
Total Amount Adjustment
Total Amount Paid
And then SAVE output to the TEMPORARY TABLE / OBJECT (#GroupedPaymentsData)
Original Data:
CREATE TABLE Payments
(PaymentId INT, PaymentRecordDate DATE, BillingId INT, InsuranceName VARCHAR(25), PaymentType VARCHAR(25), PaymentAmount FLOAT, PaymentIsCopay VARCHAR(25));
INSERT INTO Payments
VALUES
(1, '2022-02-11', 11111, 'Kaiser', 'Cash', 25.0, 'No'),
(2, '2022-02-05', 11111, 'Kaiser', 'Check', 100.0, 'No'),
(3, '2022-07-01', 11111, 'Cigna', 'Electronic', 50.0, 'No'),
(4, '2022-06-25', 33333, 'Patient', 'Electronic', 100.0, 'Yes'),
(5, '2022-07-15', 33333, 'Cigna', 'Adjustment', 50.0, 'No'),
(6, '2022-01-10', 77777, 'Tricare', 'Electronic', 25.0, 'No'),
(7, '2022-02-11', 77777, 'Tricare', 'Adjustment', 35.0, 'No'),
(8, '2022-01-15', 77777, 'Patient', 'Cash', 50.0, 'Yes'),
(9, '2022-01-05', 77777, 'Tricare', 'Credit Card', 100.0, 'No')
SELECT * FROM Payments
| PaymentId | PaymentRecordDate | BillingId | InsuranceName | PaymentType | PaymentAmount | PaymentIsCopay |
|---|---|---|---|---|---|---|
| 1 | 2022-02-11 | 11111 | Kaiser | Cash | 25.0 | No |
| 2 | 2022-02-05 | 11111 | Kaiser | Check | 100.0 | No |
| 3 | 2022-07-01 | 11111 | Cigna | Electronic | 50.0 | No |
| 4 | 2022-06-25 | 33333 | Patient | Electronic | 100.0 | Yes |
| 5 | 2022-07-15 | 33333 | Cigna | Adjustment | 50.0 | No |
| 6 | 2022-01-10 | 77777 | Tricare | Electronic | 25.0 | No |
| 7 | 2022-02-11 | 77777 | Tricare | Adjustment | 35.0 | No |
| 8 | 2022-01-15 | 77777 | Patient | Cash | 50.0 | Yes |
| 9 | 2022-01-05 | 77777 | Tricare | Credit Card | 100.0 | No |
My Code:
IF OBJECT_ID ('tempdb..#GroupedPaymentsData') IS NOT NULL
DROP TABLE tempdb..#GroupedPaymentsData
IF OBJECT_ID('tempdb..#GroupedPaymentsData') IS NULL (
SELECT
MainTable.BillingId AS [Billing Id],
COUNT(CASE WHEN (MainTable.PaymentIsCopay = 'No') THEN 1 END) AS [Total Number Of ANY RESPONSES Made by Insurance],
-- AS [Earliest ANY RESPONSE (Made by Insurance) Received On],
-- AS [Earliest ANY RESPONSE (Made by Insurance) Insurance],
-- AS [Earliest ANY RESPONSE (Made by Insurance) Type],
-- AS [Earliest ANY RESPONSE (Made by Insurance) Amount],
COUNT(CASE WHEN MainTable.PaymentType IN ('Electronic', 'Cash', 'Check', 'Credit Card') AND (MainTable.PaymentIsCopay = 'No') AND (MainTable.PaymentAmount > 0) THEN 1 END) AS [Total Number Of PAYMENTS Made by Insurance],
-- AS [Latest PAYMENT (Made by Insurance) Received On],
-- AS [Latest PAYMENT (Made by Insurance) Insurance],
-- AS [Latest PAYMENT (Made by Insurance) Type],
-- AS [Latest PAYMENT (Made by Insurance) Amount],
SUM(CASE WHEN MainTable.PaymentType IN ('Adjustment') THEN MainTable.PaymentAmount ELSE 0 END) AS [Total Amount Adjustment],
SUM(CASE WHEN MainTable.PaymentType NOT IN ('Adjustment') THEN MainTable.PaymentAmount ELSE 0 END) AS [Total Amount Paid]
INTO #GroupedPaymentsData
FROM (
SELECT p.PaymentId,
p.PaymentRecordDate,
p.BillingId,
p.InsuranceName,
p.PaymentType,
p.PaymentAmount,
p.PaymentIsCopay
FROM Payments as p
) AS MainTable
GROUP BY MainTable.BillingId
);
SELECT * FROM #GroupedPaymentsData
| Billing Id | Total Number Of ANY RESPONSES Made by Insurance | Total Number Of PAYMENTS Made by Insurance | Total Amount Adjustment | Total Amount Paid |
|---|---|---|---|---|
| 11111 | 3 | 3 | 0 | 175 |
| 33333 | 1 | 0 | 50 | 100 |
| 77777 | 3 | 2 | 35 | 175 |
I was able to get 4 desired columns, however, I can't find the way to pull data associated with the Earliest ANY RESPONSE (Made by Insurance) and Latest PAYMENT (Made by Insurance). The reason I need all this data in a single row, because I have another column (from different table that will be later joined using BillingId) that represent the date when Claim was generated and sent to the Insurance and I need to measure how long it takes to get INITIAL (Earliest) ANY RESPONSE from them and also how long it takes for them to send us FINAL (Latest) PAYMENT.
Output I'm looking for:
| Billing Id | Total Number Of ANY RESPONSES Made by Insurance | Earliest ANY RESPONSE (Made by Insurance) Received On | Earliest ANY RESPONSE (Made by Insurance) Payor | Earliest ANY RESPONSE (Made by Insurance) Type | Earliest ANY RESPONSE (Made by Insurance) Amount | Total Number Of PAYMENTS Made by Insurance | Latest PAYMENT (Made by Insurance) Received On | Latest PAYMENT (Made by Insurance) Payor | Latest PAYMENT (Made by Insurance) Type | Latest PAYMENT (Made by Insurance) Amount | Total Amount Adjustment | Total Amount Paid |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 11111 | 3 | 2022-02-05 | Kaiser | Check | 100 | 3 | 2022-07-01 | Cigna | Electronic | 50 | 0 | 175 |
| 33333 | 1 | 2022-07-15 | Cigna | Adjustment | 50 | 0 | 50 | 100 | ||||
| 77777 | 3 | 2022-01-05 | Tricare | Credit Card | 100 | 2 | 2022-01-10 | Tricare | Electronic | 25 | 35 | 175 |
Notes:
You can use window functions to determine the first/last rows of a partition as you require.
SELECT
BillingId AS [Billing Id]
, COUNT(CASE WHEN PaymentIsCopay = 'No' THEN 1 END) AS [Total Number Of ANY RESPONSES Made by Insurance]
, MIN(CASE WHEN FirstResponse = 1 THEN PaymentRecordDate END) AS [Earliest ANY RESPONSE (Made by Insurance) Received On]
, MIN(CASE WHEN FirstResponse = 1 THEN InsuranceName END) AS [Earliest ANY RESPONSE (Made by Insurance) Insurance]
, MIN(CASE WHEN FirstResponse = 1 THEN PaymentType END) AS [Earliest ANY RESPONSE (Made by Insurance) Type]
, MIN(CASE WHEN FirstResponse = 1 THEN PaymentAmount END) AS [Earliest ANY RESPONSE (Made by Insurance) Amount]
, COUNT(CASE WHEN PaymentType IN ('Electronic', 'Cash', 'Check', 'Credit Card') AND PaymentIsCopay = 'No' AND PaymentAmount > 0 THEN 1 END) AS [Total Number Of PAYMENTS Made by Insurance]
, MIN(CASE WHEN LatestPayment = 1 THEN PaymentRecordDate END) AS [Earliest ANY RESPONSE (Made by Insurance) Received On]
, MIN(CASE WHEN LatestPayment = 1 THEN InsuranceName END) AS [Earliest ANY RESPONSE (Made by Insurance) Insurance]
, MIN(CASE WHEN LatestPayment = 1 THEN PaymentType END) AS [Earliest ANY RESPONSE (Made by Insurance) Type]
, MIN(CASE WHEN LatestPayment = 1 THEN PaymentAmount END) AS [Earliest ANY RESPONSE (Made by Insurance) Amount]
, SUM(CASE WHEN PaymentType IN ('Adjustment') THEN PaymentAmount ELSE 0 END) AS [Total Amount Adjustment]
, SUM(CASE WHEN PaymentType NOT IN ('Adjustment') THEN PaymentAmount ELSE 0 END) AS [Total Amount Paid]
FROM (
SELECT *
-- Use a window function to number the rows (from first to last) within a billing window, split by the logic as to whether to count them or not
, CASE WHEN PaymentIsCopay = 'No' THEN ROW_NUMBER() OVER (PARTITION BY BillingId, PaymentIsCopay ORDER BY PaymentRecordDate ASC) ELSE NULL END FirstResponse
-- Use a window function to number the rows (from last to first) within a billing window, split by the logic as to whether to count them or not
, CASE WHEN PaymentType NOT IN ('Adjustment') AND PaymentIsCopay = 'No' THEN ROW_NUMBER() OVER (PARTITION BY BillingId, CASE WHEN PaymentType NOT IN ('Adjustment') AND PaymentIsCopay = 'No' THEN 1 ELSE 0 END ORDER BY PaymentRecordDate DESC) ELSE NULL END LatestPayment
FROM Payments
) AS p
GROUP BY BillingId;
Note: Do not use FLOAT to store money, use DECIMAL else you will get unexpected errors.