Rolling up data with case expression
I'm using a case expression but getting multiple values filled with NULLs. How would I roll it up?
To create the table:
CREATE TABLE MedQuantity (
PatName varchar(1),
Contact# int,
MedID int,
MedQty int,
EntryDate date);
INSERT INTO MedQuantity(PatName, Contact#,MedID,MedQty, EntryDate)
VALUES ('RODI, FEZ', 7, NULL, NULL, '2021-07-01'),
('RODI, FEZ',5, 32324, 185, NULL),
('RODI, FEZ',5, 4081, 15, NULL),
('RODI, FEZ',5, 4081, 50, NULL)
Which gives this output:
Name Contact# MedID MedQty EntryDate
RODI, FEZ 7 NULL NULL 2021-07-01 08:40:00
RODI, FEZ 5 32324 185 NULL
RODI, FEZ 5 4081 15 NULL
RODI, FEZ 5 4081 50 NULL
Below is my query:
SELECT
[PatName]
, [Medication ID] = MAX(CASE WHEN T1.[Contact#] = 5 THEN T1.[MedID] END)
, [Medication Qty] = MAX(CASE WHEN T1.[Contact#] = 5 THEN T1.[MedQty] END)
, [Entry Date] = MAX(CASE WHEN T1.[Contact#] = 7 THEN T1.[EntryDate] END)
FROM MedQuantity T1
GROUP By T1.[PatName]
Which gives this result:
Name Medication ID Medication Qty Entry Date
RODI, FEZ 4081 50 2021-07-01 08:40:00
However, I'm expecting:
