Fill forward for YTD calculations

Transact-SQL
1

What is the most efficient way to fill forward the YTD calculations for the months that don't have records? Right now they don't show up. Thank you!

Here is my current query:

SELECT A.Incurred_Year
,A.Incurred_Month_Name
,A.Tableau_Date
,A.LOB
,A.Payer
,A.CIN
,A.Market
,A.BSMH_HCG_Group_Rollup
,A.Amt_Paid MTD_Paid
,SUM(A.Amt_Paid) OVER (PARTITION BY A.LOB, A.Payer, A.CIN, A.Market, A.BSMH_HCG_Group_Rollup ORDER BY A.Tableau_Date) AS YTD_Paid
FROM(SELECT H.[Incurred_Year]
,H.[Incurred_Month_Name]
,D.[Tableau_Date]
,L.[LOB]
,H.[Payer]
,H.[CIN]
,H.[Market]
,H.[BSMH_HCG_Group_Rollup]
,SUM(H.[Amt_Paid]) Amt_Paid
FROM [Population_Health_Prod].[dbo].[HCG] H
LEFT JOIN [Population_Health_Prod].[dbo].[Date_Table] D ON
H.Incurred_Year = D.[Year]
AND H.Incurred_Month_Name = D.Month_Name
JOIN [Population_Health_Prod].[dbo].[Contract_LOB] L ON
H.Payer = L.Payer
GROUP BY H.[Incurred_Year]
,H.[Incurred_Month_Name]
,D.[Tableau_Date]
,L.[LOB]
,H.[Payer]
,H.[CIN]
,H.[Market]
,H.[BSMH_HCG_Group_Rollup]
) A
GROUP BY A.Incurred_Year
,A.Incurred_Month_Name
,A.Tableau_Date
,A.LOB
,A.Payer
,A.CIN
,A.Market
,A.BSMH_HCG_Group_Rollup
,A.Amt_Paid

SQL YTD
SQL YTD492×361 7.88 KB

2

hi

hope this helps

;WITH monthscte
     AS (SELECT DISTINCT [Year] = Year(D.tableau_date),
                         [Month] = Month(D.tableau_date),
                         [Month_Name] = Datename(month, D.tableau_date)
         FROM   [Population_Health_Prod].[dbo].[date_table] D
         WHERE  D.tableau_date >= (SELECT Min(tableau_date)
                                   FROM   [Population_Health_Prod].[dbo].[hcg])
                AND D.tableau_date <= (SELECT Max(tableau_date)
                                       FROM
                    [Population_Health_Prod].[dbo].[hcg])),
     monthlydatacte
     AS (SELECT H.incurred_year,
                H.incurred_month_name,
                D.tableau_date,
                L.lob,
                H.payer,
                H.cin,
                H.market,
                H.bsmh_hcg_group_rollup,
                Amt_Paid = Sum(H.amt_paid)
         FROM   [Population_Health_Prod].[dbo].[hcg] H
                LEFT JOIN [Population_Health_Prod].[dbo].[date_table] D
                       ON H.incurred_year = D.year
                          AND H.incurred_month_name = D.month_name
                JOIN [Population_Health_Prod].[dbo].[contract_lob] L
                  ON H.payer = L.payer
         GROUP  BY H.incurred_year,
                   H.incurred_month_name,
                   D.tableau_date,
                   L.lob,
                   H.payer,
                   H.cin,
                   H.market,
                   H.bsmh_hcg_group_rollup)
SELECT A.incurred_year,
       A.incurred_month_name,
       A.tableau_date,
       A.lob,
       A.payer,
       A.cin,
       A.market,
       A.bsmh_hcg_group_rollup,
       A.amt_paid                                      AS MTD_Paid,
       Sum(A.amt_paid)
         OVER (
           partition BY A.lob, A.payer, A.cin, A.market, A.bsmh_hcg_group_rollup
           ORDER BY A.tableau_date, A.incurred_month ) AS YTD_Paid
FROM   monthlydatacte A
ORDER  BY A.lob,
          A.payer,
          A.cin,
          A.market,
          A.bsmh_hcg_group_rollup,
          A.tableau_date;
1 Like
3

hi

hope this helps

Create Tables
Sample Data

-- Create Date_Table  
CREATE TABLE Date_Table (  
    [Year] INT,  
    [Month_Name] NVARCHAR(20),  
    [Date_Table] DATE  
);  

-- Create Contract_LOB table  
CREATE TABLE Contract_LOB (  
    [LOB] NVARCHAR(100)  
);  

-- Create HCG table  
CREATE TABLE HCG (  
    [Incurred_Year] INT,  
    [Incurred_Month_Name] NVARCHAR(20),  
    [Payer] NVARCHAR(100),  
    [CIN] NVARCHAR(100),  
    [Market] NVARCHAR(100),  
    [BSMH_HCG_Group_Rollup] NVARCHAR(100),  
    [Amt_Paid] DECIMAL(18, 2)  
);  

-- Insert sample data  
INSERT INTO Date_Table ([Year], [Month_Name], [Date_Table])  
VALUES  
(2023, 'January', '2023-01-01'),  
(2023, 'February', '2023-02-01'),  
(2023, 'March', '2023-03-01'),  
(2023, 'April', '2023-04-01'),  
(2023, 'May', '2023-05-01'),  
(2023, 'June', '2023-06-01'),  
(2023, 'July', '2023-07-01'),  
(2023, 'August', '2023-08-01'),  
(2023, 'September', '2023-09-01'),  
(2023, 'October', '2023-10-01'),  
(2023, 'November', '2023-11-01'),  
(2023, 'December', '2023-12-01'),  
(2024, 'January', '2024-01-01');  

INSERT INTO Contract_LOB ([LOB])  
VALUES  
('Commercial'),  
('Medicaid'),  
('Medicare'),  
('Other');  

INSERT INTO HCG ([Incurred_Year], [Incurred_Month_Name], [Payer], [CIN], [Market], [BSMH_HCG_Group_Rollup], [Amt_Paid])  
VALUES  
(2023, 'January', 'Payer1', 'CIN1', 'Market1', 'Group1', 1000.00),  
(2023, 'March', 'Payer1', 'CIN1', 'Market1', 'Group1', 1500.00),  
(2023, 'May', 'Payer1', 'CIN1', 'Market1', 'Group1', 2000.00),  
(2023, 'July', 'Payer1', 'CIN1', 'Market1', 'Group1', 1200.00),  
(2023, 'September', 'Payer1', 'CIN1', 'Market1', 'Group1', 1800.00),  
(2023, 'November', 'Payer1', 'CIN1', 'Market1', 'Group1', 2500.00),  
(2023, 'January', 'Payer2', 'CIN2', 'Market2', 'Group2', 500.00),  
(2023, 'April', 'Payer2', 'CIN2', 'Market2', 'Group2', 600.00),  
(2023, 'June', 'Payer2', 'CIN2', 'Market2', 'Group2', 700.00),  
(2023, 'August', 'Payer2', 'CIN2', 'Market2', 'Group2', 800.00),  
(2023, 'October', 'Payer2', 'CIN2', 'Market2', 'Group2', 900.00),  
(2023, 'December', 'Payer2', 'CIN2', 'Market2', 'Group2', 1000.00);
1 Like