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.
