Rolling up data with case statement-CodePudding

I'm using a case statement but getting multiple values filled with NULLs. How would I roll it up? I must be going crazy because this seems too simple to be this complicated...

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:

CodePudding user response：

Your relationship between the rows is unclear, but it appears you are trying to grab the most recent row where contact# = 7. Something like this should work

Returning Most Recent Row WHERE Contact# = 7

DROP TABLE IF EXISTS  #MedQuantity

CREATE TABLE #MedQuantity (
    ID INT IDENTITY(1,1),
    PatName varchar(100),
    Contact# int,
    MedID int,
    MedQty int,
    EntryDate datetime2(0));


INSERT INTO #MedQuantity(PatName, Contact#,MedID,MedQty, EntryDate)
VALUES ('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)

SELECT
    A.ID
    ,A.PatName
    ,A.MedID
    ,A.MedQty
    ,B.EntryDate
FROM #MedQuantity AS A
OUTER APPLY (
        /*Goes back and finds last row where Contact# = 7 to return EntryDate*/
        SELECT TOP (1) EntryDate
        FROM #MedQuantity AS DTA
        WHERE DTA.Contact# = 7
        AND DTA.ID < A.ID
        ORDER BY ID DESC
) AS B
WHERE A.Contact# = 5

CodePudding user response：

You can try to use aggregate window function in a subquery, then filter the condition which you expected.

SELECT *
FROM (
    SELECT  
        [PatName]
        ,MedID
        ,MedQty
        ,[Entry Date] = MAX(CASE WHEN T1.[Contact#] = 7 THEN T1.[EntryDate] end) OVER(PARTITION BY PatName)
    FROM MedQuantity T1
) t1
WHERE MedID IS NOT NULL AND MedQty IS NOT NULL

sqlfiddle