MIN and MAX of dates producing wrong output

Question

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

BillingId	PaymentId	PaymentDate	PaymentType	PaymentBy
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

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:

BillingId	EarliestDateInsurancePaid	LatestDateInsurancePaid	EarliestDateClientPaid	LatestDateClientPaid	EarliestAdjustmentDate	LatestAdjustmentDate	TotalAmountOfPayments	AmountOfInsurancePayments	AmountOfClientPayments	AmountOfAdjustments
12345	1900-01-01	2022-02-02	1900-01-01	1900-01-01	1900-01-01	2022-02-02	1	1	0	1
67890	1900-01-01	2022-01-19	1900-01-01	2022-02-02	1900-01-01	2022-01-19	3	2	1	1

Output I need:

BillingId	EarliestDateInsurancePaid	LatestDateInsurancePaid	EarliestDateClientPaid	LatestDateClientPaid	EarliestAdjustmentDate	LatestAdjustmentDate	TotalAmountOfPayments	AmountOfInsurancePayments	AmountOfClientPayments	AmountOfAdjustments
12345	2022-02-02	2022-02-02	1900-01-01	1900-01-01	2022-02-02	2022-02-02	1	1	0	1
67890	2022-01-19	2022-01-19	2022-02-02	2022-02-02	2022-01-19	2022-01-19	3	2	1	1

Answer 1

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.