Dynamically SELECT a column based on value of row at first of month

The following query:

  SELECT Confirmed, Interim, Declared, Date    
    FROM Interest_Hist                         
   WHERE Date BETWEEN '2019-08-01' AND '2019-12-04'          
ORDER BY Date ASC

Returns the following sample data:

Confirmed   Interim   Declared   Date
Y           0.314     0.0788     2019-08-01
            0.317     0          2019-08-02
...
            0.245     0          2019-08-31
            0.222     0.219      2019-09-01
            0.198     0          2019-09-02
...
Y           0.50      0.454      2019-12-01
            0.51      0          2019-12-02
            0.52      0          2019-12-03
            0.53      0          2019-12-04

Where on the first of the month, Confirmed = Y, I need to return the Declared column for that month.

Note, Confirmed = Y will only exist on the first of the month. That column is blank in all other cases

Otherwise, I need to return each Interim column for the month.

Thus far, I have been able to return the SUM of either column, but not the individual values.

SELECT                                                                   
   CASE WHEN SUM(CASE WHEN IRc_Confirmed = 'Y' THEN 1 ELSE 0 END) = 0   
     THEN Interim                                     
     ELSE Declared                                     
  END AS Rate                                                    
 FROM Fund_Interest                                                
WHERE Date BETWEEN '2019-08-01' AND '2019-12-04'           
GROUP BY                                                                   
  DATEADD(month, DATEDIFF(month, 0, Date), 0), Interim, Declared                 
ORDER BY                                                                   
  DATEADD(month, DATEDIFF(month, 0, Date), 0)

The expected output given the data at the top is as follows

0.0788
0
...
0
0.222
0.198
...
0.454
0
0
0

Answer

This took me way longer than it probably should have.

SELECT IIF( MAX(Confirmed) OVER(PARTITION BY CONVERT(VARCHAR(6), Date, 112)) = 'Y', Declared, Interim) Interest_Rate 
FROM Interest_Hist
WHERE DateBETWEEN '01-AUG-2019' AND '04-DEC-2019'         
ORDER BY Date

Advertisement

Answer