Calculate MTD from same table (SQL)

Transact-SQL
1

Below is my SQL code which I am using to generate the MTD numbers , however I am facing an issue with the results as they are not correct , the issue occurring as some of the partner data is available in the first day but not in the next day data set.

how I can include first day ( Missing ) data to be part of the next in order to get the correct MTD number .

SELECT CalendarID,
	   PartnerID,
   DAY_QTY,
	   SUM (DAY_QTY) OVER (PARTITION BY CalendarID,PartnerID ORDER BY CalendarID) AS MTD_QTY
FROM TEST
ORDER BY CalendarID

My Data Set :
> CalendarID \ PartnerID \ DAY_QTY \ MTD_QTY

20220101	222180		245	
20220101	362798		12	
20220101	363248		10	
20220101	381773		5	
20220101	397523		28	
20220102	220202		1	
20220102	240857		4	
20220102	252074		50	
20220102	263986		7	
20220102	397523		163	
20220102	397680		6
2

Not sure what you are expecting for output. Also, for CalendarID, is 20220102 Feb 1 or Jan 2. It'd be better to store as a date unless you have a table that identifies the actual Date, Month, Year, etc... for this date. Based on what you have, my guess is something like this, but it doesn't take into account months and years

SELECT CalendarID,
	   PartnerID,
   DAY_QTY,
	   SUM (DAY_QTY) OVER (PARTITION BY PartnerID ORDER BY CalendarID) AS MTD_QTY
FROM TEST
ORDER BY CalendarID
3

Hi Mike01 ,

Thanks for the reply , Calendar ID is a date in ( YYYY MM DD ) format .
so the data set in only for 01-Jan-2022 and 02-Jan-2022 .

your code brings the same result which am getting . let me try to explain the outcome I am looking for :

Day 1 ( 2022 01 01 ) : total QTY is 300
Day 2 ( 2022 01 02 ) : total QTY is 231

Day 1 : MTD should be 300
Day 2 : MTD should be (DAY 1 QTY) + 231 ( the result I am trying to achieve )

The result for Day 2 MTD I am getting is :
259 ( based on my & your code )

where it should come :
531

4

It would have been easier if you had provided sample data. Here are a couple of options:

Declare @testData table (CalendarID date, PartnerID int, day_qty int);
 Insert Into @testData (CalendarID, PartnerID, day_qty)
 Values ('2022-01-01', 222180, 245)
      , ('2022-01-01', 362798, 12)
      , ('2022-01-01', 363248, 10)
      , ('2022-01-01', 381773, 5)
      , ('2022-01-01', 397523, 28)
      , ('2022-01-02', 220202, 1)
      , ('2022-01-02', 240857, 4)
      , ('2022-01-02', 252074, 50)
      , ('2022-01-02', 263986, 7)
      , ('2022-01-02', 397523, 163)
      , ('2022-01-02', 397680, 6);

 Select td.CalendarID
      , sum(td.day_qty)
   From @testData                       td
  Group By Grouping Sets (
        ()
      , (CalendarID)
        );

   With subTotals
     As (
 Select td.CalendarID
      , day_total = sum(td.day_qty)
   From @testData                       td
  Group By 
        td.CalendarID
        )
 Select *
      , mtd_totals = sum(st.day_total) Over(Order By st.CalendarID)
   From subTotals                       st;

The first one provides a totals row using GROUPING SETS. The second provides a running total by day.

5

Hi Jeffw8713,

can we achieve the same result by adding partner in the code ? I tried but could not succeed .

6

Not sure what you mean. Sample data and expected results will help.