October 26, 2011 at 4:03 pm
When pulling data, I need to summarize totals by Week of the current month.
e.g. if the 1st of the current month falls on a Wednesday, Week 1 would be Wednesday through Sunday that week. Week 2 Would be the next Monday through Sunday - and so forth - so it's not as straight forward as 7 days counts as a week.
Any ideas how to do this in SQL?
October 27, 2011 at 1:20 am
I'm not exactly clear on what you're after, but take a look at the code below. I make no claims about it's performance, other than to strongly suggest that you optimize this before deploying. But it may work as a jumping off point. I simply grouped by the combination of month and week (of year), which I believe may account for your issue of needing to start week 1 on the first of every month, but still end that week on Sunday. I used the RANK() function to give your weeks more report-friendly numbers.
SET DATEFIRST 1; -- Makes Monday the first day of the week
DECLARE @AsOfDate DATE = '5/12/2009';
-- Get first date of current month
DECLARE @BeginDate DATE = CAST(CAST(DATEPART(MONTH, @AsOfDate) AS VARCHAR(2)) + '/1/' + CAST(DATEPART(YEAR, @AsOfDate) AS VARCHAR(4)) AS DATE);
-- Get first day of next month
DECLARE @EndDate DATE = DATEADD(MONTH, 1, @BeginDate);
SELECT DATEPART(MONTH, SystemDate) AS SalesMonth, RANK() OVER (ORDER BY DATEPART(WEEK, SystemDate)) AS SalesWeek, SUM(Quantity) AS TotalQuantity
FROM OrderDetails
WHERE (SystemDate >= @BeginDate AND SystemDate < @EndDate)
GROUP BY DATEPART(MONTH, SystemDate), DATEPART(WEEK, SystemDate)
ORDER BY DATEPART(MONTH, SystemDate), DATEPART(WEEK, SystemDate);
October 27, 2011 at 1:40 am
Totally possible if you have a calendar table designed for your needs. Else we need to play with SET DATEFIRST statments and then can achieve the result.
it would immensely help us if you could provide sample data to work on along with ur table structure.
October 27, 2011 at 3:07 am
iluvmyelement (10/26/2011)
When pulling data, I need to summarize totals by Week of the current month.
e.g. if the 1st of the current month falls on a Wednesday, Week 1 would be Wednesday through Sunday that week. Week 2 Would be the next Monday through Sunday - and so forth - so it's not as straight forward as 7 days counts as a week.
Any ideas how to do this in SQL?
Hello and welcome to SSC!
As others have already pointed out, your DDL script has become detached from your post, or perhaps you were unaware of the benefits of providing one.
When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
For now, I've had a "best guess" at your issue.
First, lets create a test environment to play with: -
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME) AS transactionDate,
ABS(CHECKSUM(NewId())) % 1000.25 + 1 AS transactionAmount
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
--Add a Primary Key
ALTER TABLE #testEnvironment
ADD CONSTRAINT testEnvironment_PK_ID
PRIMARY KEY CLUSTERED (ID) WITH FILLFACTOR = 100
--Add date / transaction index
CREATE NONCLUSTERED INDEX tmp_testEnvironment__on__transactionDate__inc__transactionAmount
ON #testEnvironment ([transactionDate])
INCLUDE ([transactionAmount])
GO
Now, lets look at the code.
SELECT wks.wk_number AS week_number, wks.wk_start AS week_start, wks.wk_fin AS week_end,
SUM(a.transactionAmount) AS transactionAmount
FROM (SELECT DATEADD(dd,0,DATEDIFF(dd,0,transactionDate)) AS transactionDate,
SUM(transactionAmount) AS transactionAmount
FROM #testEnvironment
WHERE transactionDate BETWEEN DATEADD(mm,DATEDIFF(mm,0,GETDATE()), 0) AND DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1, 0)
GROUP BY DATEADD(dd,0,DATEDIFF(dd,0,transactionDate))) a
LEFT OUTER JOIN (SELECT wk_number,
CASE WHEN raw_wks.wk_start < DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1, 0)
THEN raw_wks.wk_start
ELSE NULL END AS wk_start,
CASE WHEN raw_wks.wk_start < DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1, 0)
THEN CASE WHEN raw_wks.wk_fin < DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1, 0)
THEN raw_wks.wk_fin
ELSE DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1, 0)) END
ELSE NULL END AS wk_fin
FROM (SELECT 1 AS wk_number,
DATEADD(mm, DATEDIFF(m,0,GETDATE()),0) AS wk_start,
DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AS wk_fin
UNION ALL
SELECT 2, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))),
DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))
UNION ALL
SELECT 3, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))),
DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))))
UNION ALL
SELECT 4, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))))),
DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))))))
UNION ALL
SELECT 5, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))))))),
DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))))))))
UNION ALL
SELECT 6, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))))))))),
DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))))))))))
) raw_wks ) wks ON a.transactionDate >= wks.wk_start AND a.transactionDate <= wks.wk_fin
GROUP BY wks.wk_number, wks.wk_start, wks.wk_fin
And some tests for performance.
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME) AS transactionDate,
ABS(CHECKSUM(NewId())) % 1000.25 + 1 AS transactionAmount
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
--Add a Primary Key
ALTER TABLE #testEnvironment
ADD CONSTRAINT testEnvironment_PK_ID
PRIMARY KEY CLUSTERED (ID) WITH FILLFACTOR = 100
--Add date / transaction index
CREATE NONCLUSTERED INDEX tmp_testEnvironment__on__transactionDate__inc__transactionAmount
ON #testEnvironment ([transactionDate])
INCLUDE ([transactionAmount])
GO
PRINT '========== BASELINE =========='
SET STATISTICS TIME ON
SELECT COUNT(*) FROM #testEnvironment
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
PRINT '========== CODE =========='
SET STATISTICS TIME ON
SELECT wks.wk_number AS week_number, wks.wk_start AS week_start, wks.wk_fin AS week_end,
SUM(a.transactionAmount) AS transactionAmount
FROM (SELECT DATEADD(dd,0,DATEDIFF(dd,0,transactionDate)) AS transactionDate,
SUM(transactionAmount) AS transactionAmount
FROM #testEnvironment
WHERE transactionDate BETWEEN DATEADD(mm,DATEDIFF(mm,0,GETDATE()), 0) AND DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1, 0)
GROUP BY DATEADD(dd,0,DATEDIFF(dd,0,transactionDate))) a
LEFT OUTER JOIN (SELECT wk_number,
CASE WHEN raw_wks.wk_start < DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1, 0)
THEN raw_wks.wk_start
ELSE NULL END AS wk_start,
CASE WHEN raw_wks.wk_start < DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1, 0)
THEN CASE WHEN raw_wks.wk_fin < DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1, 0)
THEN raw_wks.wk_fin
ELSE DATEADD(dd,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 1, 0)) END
ELSE NULL END AS wk_fin
FROM (SELECT 1 AS wk_number,
DATEADD(mm, DATEDIFF(m,0,GETDATE()),0) AS wk_start,
DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AS wk_fin
UNION ALL
SELECT 2, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))),
DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))
UNION ALL
SELECT 3, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))),
DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))))
UNION ALL
SELECT 4, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))))),
DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))))))
UNION ALL
SELECT 5, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))))))),
DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))))))))
UNION ALL
SELECT 6, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))))))))),
DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))))%7, DATEADD(dd,1,DATEADD(dd,(8-DATEPART(dw,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))%7, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))))))))))))
) raw_wks ) wks ON a.transactionDate >= wks.wk_start AND a.transactionDate <= wks.wk_fin
GROUP BY wks.wk_number, wks.wk_start, wks.wk_fin
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
(1000000 row(s) affected)
========== BASELINE ==========
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 79 ms.
================================================================================
========== CODE ==========
(6 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 4 ms.
================================================================================
Am I on the right track?
October 27, 2011 at 7:27 am
I was able to use an ISO Week function, then just subtract the week number of the first of the month....add 1 to that and come up with my result on the fly. So far it's looking like that is doing what I want with very little code.
October 27, 2011 at 7:49 am
Here is a fairly simple way to get what you're looking for.
SELECT Year(ms.MonthStart), Datename(Month, MonthStart), WeekOfMonth, Sum(YourAmountField)
FROM YourTable AS l
CROSS APPLY ( SELECT DateAdd(Month, DateDiff(Month, 0, GetDate()), 0) AS MonthStart ) AS ms
CROSS APPLY ( SELECT (Day(YourDateField)-1)/7 + 1 AS WeekOfMonth ) AS wm
WHERE YourDateField >= MonthStart
GROUP BY MonthStart, WeekOfMonth
ORDER BY MonthStart, WeekOfMonth
I've used the CROSS APPLYs to name the calculations, since they're used in both the SELECT statement and the GROUP BY statement.
Drew
PS: It has been repeatedly shown that converting to strings and back in order to do date manipulation is horribly inefficient. Use DateAdd() and DateDiff() instead.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply