sum based on max production date and min production date MTD,WTD, YTD SQL Server

Question

Hello I am trying to create a automated query that displays month to date, year to date, and week to date and creates a column for each. I need to sum balance amount if the production date is the maximum - the minimum production date sum of deposits. This will give me a YTD column. I also need to do month to date and week to date if anyone has any ideas. Any help with this would be appreciated. Thanks!

P.S. I am using microsoft sql server management studio Here is what I have so far:

select SUM([curr_bal_amt]) as total_amt , [prod_dt] as date123
from [dbo].[DEPOSIT_TEST]
group by [prod_dt];

this results in a chart like:

Overall I need to calculate year to date as subtracting the max date i have minus the min date i have. Later on when i import more data i need to do mtd and wtd. Thanks

Edit: I am looking to use my current table so maybe it would help to edit this table as I forgot to mention that I have 3 day gaps in data.

-also for my prod_dt column i have multiple balances that I must sum if the prod_dt is the same. Is there a simple query to just subtract the most recent date's sum of curr_balance amt - the first date of the last month's sum of curr_balance amt. Thanks for your help Shawn it is greatly appreciated!

this is an example of one of my data imports for one of my days

Please if you could use the names of my columns it would be very beneficial so that I could learn better. Thank you! the name of my table is Deposit_Test and the column names are just like the ones in the picture. Thank you again

Answer 1

This should give you a good idea of how to get at those totals. I don't know what other data you're after in your tables, but you should be able to modify the below query to get at it.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

/********************************CALENDAR********************************/
/* 
  My original answer made use of a Calendar Table, but I realized it 
  was overkill for this situation. I still think every database should
  have both a Calendar Table and a Numbers Table. They are both very 
  useful. I use the ct here just to populate my test table, but I've 
  left some very basic creation to show you how it can be done. Calcs 
  done here allow your final query to JOIN to it and avoid RBAR to be 
  more set-based, and save a lot of processing for large tables.  
  NOTE: This original date table concept is from Aaron Bertrand.
*/

CREATE TABLE datedim (
      theDate           date        PRIMARY KEY
    , theDay            AS DATEPART(day, theDate)           --int
    , theWeek           AS DATEPART(week, theDate)          --int 
    , theMonth          AS DATEPART(month, theDate)         --int
    , theYear           AS DATEPART(year, theDate)          --int
    , yyyymmdd          AS CONVERT(char(8), theDate, 112)   /* yyyymmdd */
);

/************************************************************************/
/* 
  Use the catalog views to generate as many rows as we need. This example
  creates a date dimension for all of 2018.
*/
INSERT INTO datedim ( theDate ) 
SELECT d
FROM (
    SELECT d = DATEADD(day, rn - 1, '20180101')
    FROM 
    (
        SELECT TOP (DATEDIFF(day, '20180101', '20190101')) 
            rn = ROW_NUMBER() OVER (ORDER BY s1.object_id)
        FROM sys.all_objects AS s1
        CROSS JOIN sys.all_objects AS s2
        ORDER BY s1.object_id
    ) AS x
) AS y;
/************************************************************************/

/***** TEST TABLE SETUP *****/
CREATE TABLE t1 ( id int identity, entryDate date, cnt int) ;

INSERT INTO t1 (entryDate, cnt)
SELECT theDate, 2
FROM datedim
;

/* Remove a few "random" records to test our counts. */
DELETE FROM t1 
WHERE datePart(day,entryDate) IN (10,6,14,22) OR datepart(month,entryDate) = 6
;

Main Query:

/* Make sure the first day or our week is consistent. */
SET DATEFIRST 7 ; /* SUNDAY */ 

/* Then build out our query needs with CTEs. */
; WITH theDate AS (
  SELECT d.dt FROM ( VALUES ( '2018-05-17' ) ) d(dt) 
)
, base AS (
  SELECT t1.entryDate
    , t1.cnt
    , theDate.dt
    , datepart(year,theDate.dt) AS theYear
    , datepart(month,theDate.dt) AS theMonth
    , datepart(week,theDate.dt) AS theWeek
  FROM t1
  CROSS APPLY theDate
  WHERE t1.EntryDate <= theDate.dt
    AND datePart(year,t1.EntryDate) = datePart(year,theDate.dt)
)
/* Year-to-date totals */
, ytd AS ( 
  SELECT b.theYear, sum(cnt) AS s
  FROM base b
  GROUP BY b.theYear
)
/* Month-to-date totals */
, mtd AS (
  SELECT b2.theYear, b2.theMonth, sum(cnt) AS s
  FROM base b2
  WHERE b2.theMonth = datePart(month,b2.EntryDate)
  GROUP BY b2.theYear, b2.theMonth
)
/* Week-to-date totals */
, wtd AS (
  SELECT b3.theYear, b3.theMonth, sum(cnt) AS s
  FROM base b3
  WHERE b3.theWeek = datePart(week,b3.EntryDate)
  GROUP BY b3.theYear, b3.theMonth
)
SELECT blah = 'CountRow'
  , ytd.s AS ytdAmt
  , mtd.s AS mtdAmt
  , wtd.s AS wtdAmt
FROM ytd 
CROSS APPLY mtd
CROSS APPLY wtd 

Results:

|     blah | ytdAmt | mtdAmt | wtdAmt |
|----------|--------|--------|--------|
| CountRow |    236 |     28 |      8 |

Again, the data that you need to get will likely change the overall query, but this should point in the right direction. You can use each CTE to verify the YTD, MTD and WTD totals.