Solved

Sql Server 2008R2  Select statement to group by weeks showing Mondays dates

Posted on 2015-01-12
3
112 Views
Last Modified: 2015-01-12
I need a simple select statement to group data by week, showing Mondays Date for the groupings.

i.e. I want to group 7 days of data into the beginning of the week date.

20141222   sum
20141229   sum
20150105   sum
20150112   sum
0
Comment
Question by:BFanguy
  • 2
3 Comments
 

Author Comment

by:BFanguy
ID: 40545175
think i answered my own question:
CASE datepart(weekday, ShiftDate) 
                      WHEN 1 THEN (CONVERT(varchar(8), DATEADD(day, - 6, ShiftDate), 112)) WHEN 2 THEN (CONVERT(varchar(8), ShiftDate, 112)) WHEN 3 THEN (CONVERT(varchar(8), 
                      DATEADD(day, - 1, ShiftDate), 112)) WHEN 4 THEN (CONVERT(varchar(8), DATEADD(day, - 2, ShiftDate), 112)) WHEN 5 THEN (CONVERT(varchar(8), DATEADD(day, - 3, 
                      ShiftDate), 112)) WHEN 6 THEN (CONVERT(varchar(8), DATEADD(day, - 4, ShiftDate), 112)) WHEN 7 THEN (CONVERT(varchar(8), DATEADD(day, - 5, ShiftDate), 112)) 
                      END AS Starting_Weekday

Open in new window

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40545230
Below is a much easier way to get the correct date, including stripping the time (if needed).

You can add a CROSS APPLY to assign an alias to that name you can use through the query:

SELECT
    CONVERT(varchar(8), Monday_Date, 112) AS Monday_Date,
    SUM(...)
FROM dbo.table_name
CROSS APPLY (
    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, ShiftDate), 0) AS ShiftDate_Time_Stripped
) AS assign_alias_names1
CROSS APPLY (
    SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, ShiftDate_Time_Stripped) % 7, ShiftDate_Time_Stripped) AS Monday_Date
) AS assign_alias_names2
GROUP BY Monday_Date
ORDER BY Monday_Date
0
 

Author Closing Comment

by:BFanguy
ID: 40545469
Thank you sir.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question