I have Payment Data and I want to show MIN and MAX dates based on different Payment Types and COUNT number of payments and adjustments.

CREATE TABLE Payment
(BillingId int, PaymentId int, PaymentDate date, PaymentType varchar(50), PaymentBy varchar(50));

INSERT INTO Payment
VALUES
(12345, 1, '2022-02-02', 'payment', 'Insurance'),
(12345, 2, '2022-02-02', 'adjustment', 'Insurance'),
(67890, 3, '2022-01-19', 'payment', 'Insurance'),
(67890, 4, '2022-01-19', 'adjustment', 'Insurance'),
(67890, 5, '2022-01-19', 'payment', 'Insurance'),
(67890, 6, '2022-02-02', 'payment', 'Client')

SELECT * FROM Payment

I need to GROUP data BY BillingId and show 6 dates (issue in them, specifically in MIN) and 4 total counts (they produce correct output):

1. MIN date when Insurance made payment
2. MAX date when Insurance made payment
3. MIN date when Client made payment
4. MAX date when Client made payment
5. MIN date when Adjustment was made
6. MAX date when Adjustment was made
7. COUNT of all payments (without adjustments)
8. COUNT amount of payments made by Insurance
9. COUNT amount of payments made by Client
10. COUNT amount of adjustments

P.S. If only 1 payment was made by Insurance, for example, then MIN and MAX dates should be the same.

My code:

SELECT p.BillingId, 

        MIN(CASE WHEN p.PaymentBy = 'Insurance' AND p.PaymentType != 'adjustment' THEN p.PaymentDate ELSE '' END) AS EarliestDateInsurancePaid,
        MAX(CASE WHEN p.PaymentBy = 'Insurance' AND p.PaymentType != 'adjustment'  THEN p.PaymentDate ELSE '' END) AS LatestDateInsurancePaid,
        MIN(CASE WHEN p.PaymentBy = 'Client' AND p.PaymentType != 'adjustment' THEN p.PaymentDate ELSE '' END) AS EarliestDateClientPaid,
        MAX(CASE WHEN p.PaymentBy = 'Client' AND p.PaymentType != 'adjustment' THEN p.PaymentDate ELSE '' END) AS LatestDateClientPaid,
        MIN(CASE WHEN p.PaymentType = 'adjustment' THEN p.PaymentDate ELSE '' END) AS EarliestAdjustmentDate,
        MAX(CASE WHEN p.PaymentType = 'adjustment' THEN p.PaymentDate ELSE '' END) AS LatestAdjustmentDate,

        COUNT(CASE WHEN p.PaymentType != 'adjustment' THEN p.PaymentType END) AS TotalAmountOfPayments,
        COUNT(CASE WHEN p.PaymentBy = 'Insurance' AND p.PaymentType != 'adjustment' THEN p.PaymentBy END) AS AmountOfInsurancePayments,
        COUNT(CASE WHEN p.PaymentBy = 'Client' AND p.PaymentType != 'adjustment' THEN p.PaymentBy END) AS AmountOfClientPayments, 
        COUNT(CASE WHEN p.PaymentType = 'adjustment' THEN p.PaymentType END) AS AmountOfAdjustments
            
FROM Payment AS p
GROUP BY p.BillingId

Output from the above query:

Output I need:

CodePudding user response：

Your use of max(case when..else '' end... is causing the 1900 date, because the max function does not ignore empty/zero length strings, they are converted to date. Use NULL (or no ELSE part, defaults to NULL); MAX and many other aggregate functions ignore NULL values.