Need counts of orders per week grouped by week

Question

Need counts of orders per week grouped by week

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 1

meichmann - Tuesday, February 6, 2018 5:23 AM
Jeff Moden - Monday, February 5, 2018 2:40 PM
meichmann - Monday, February 5, 2018 6:06 AM
Yes it is. If the month, or even year rolls over during the business week, then it's ok. 🙂
Perfect (and smart, too). Are you folks actually following an ISO calendar or is it just for this report?
It's just for this report. Not following an ISO calendar. 🙂

K. Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

meichmann SSCommitted Points: 1900 More actions · Answer 2

Jeff Moden - Tuesday, February 6, 2018 7:01 AM
meichmann - Tuesday, February 6, 2018 5:23 AM
Jeff Moden - Monday, February 5, 2018 2:40 PM
meichmann - Monday, February 5, 2018 6:06 AM
Yes it is. If the month, or even year rolls over during the business week, then it's ok. 🙂
Perfect (and smart, too). Are you folks actually following an ISO calendar or is it just for this report?
It's just for this report. Not following an ISO calendar. 🙂
K. Thanks for the feedback.

Thank you!

omelo Grasshopper Points: 24 More actions · Answer 3

ScottPletcher - Thursday, February 1, 2018 7:58 AM
SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, [Order Date]) % 7, [Order Date]) AS [Week of], COUNT(*) AS Count FROM dbo.your_table_name GROUP BY DATEADD(DAY, -DATEDIFF(DAY, 0, [Order Date]) % 7, [Order Date])

Hi Scott,
I'm doing some research here to resolve an issue an came across your posting. Which is perfect if the week starts on a Monday.
But our week starts on Sunday. My report has start date and end date parameters and users are instructed to always select a Sunday to start.
Could you help me.
Thank you

Keith Oliver SSC Veteran Points: 288 More actions · Answer 4

Keith Oliver

SSC Veteran

Points: 288

March 4, 2019 at 7:26 am

#2023431

Bravo Jeff +1

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

omelo - Sunday, March 3, 2019 12:03 PM
ScottPletcher - Thursday, February 1, 2018 7:58 AM
SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, [Order Date]) % 7, [Order Date]) AS [Week of], COUNT(*) AS Count FROM dbo.your_table_name GROUP BY DATEADD(DAY, -DATEDIFF(DAY, 0, [Order Date]) % 7, [Order Date])
Hi Scott,
I'm doing some research here to resolve an issue an came across your posting. Which is perfect if the week starts on a Monday.
But our week starts on Sunday. My report has start date and end date parameters and users are instructed to always select a Sunday to start.
Could you help me.
Thank you

Yes. Change the 0s to 6s. It should be just that easy. That's one reason I stick to that pattern for this type of calc. And it's logical if you think about it: if day 0 is Monday, then day 1 would be Tuesday, ..., 6 would be Sunday.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

omelo - Sunday, March 3, 2019 12:03 PM
ScottPletcher - Thursday, February 1, 2018 7:58 AM
SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, [Order Date]) % 7, [Order Date]) AS [Week of], COUNT(*) AS Count FROM dbo.your_table_name GROUP BY DATEADD(DAY, -DATEDIFF(DAY, 0, [Order Date]) % 7, [Order Date])
Hi Scott,
I'm doing some research here to resolve an issue an came across your posting. Which is perfect if the week starts on a Monday.
But our week starts on Sunday. My report has start date and end date parameters and users are instructed to always select a Sunday to start.
Could you help me.
Thank you

Just use a -1 instead of the zero. -1 equals '1899-12-31' which was a Sunday.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

omelo Grasshopper Points: 24 More actions · Answer 7

ScottPletcher - Monday, March 4, 2019 9:32 AM
omelo - Sunday, March 3, 2019 12:03 PM
ScottPletcher - Thursday, February 1, 2018 7:58 AM
SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, [Order Date]) % 7, [Order Date]) AS [Week of], COUNT(*) AS Count FROM dbo.your_table_name GROUP BY DATEADD(DAY, -DATEDIFF(DAY, 0, [Order Date]) % 7, [Order Date])
Hi Scott,
I'm doing some research here to resolve an issue an came across your posting. Which is perfect if the week starts on a Monday.
But our week starts on Sunday. My report has start date and end date parameters and users are instructed to always select a Sunday to start.
Could you help me.
Thank you
Yes. Change the 0s to 6s. It should be just that easy. That's one reason I stick to that pattern for this type of calc. And it's logical if you think about it: if day 0 is Monday, then day 1 would be Tuesday, ..., 6 would be Sunday.

It works perfectly!!!😀
DATEADD(DAY, -DATEDIFF(DAY, 6, [transdate]) % 7, [transdate]) AS Week
I get the results from sunday to Saturday, even for the weeks that overlap years. Like Dec 30/2018 to Jan 5/2019.
I appreciated all responses!

joewhite SSC Veteran Points: 269 More actions · Answer 8

hello ScottPletcher,

would you please help me here:

https://www.sqlservercentral.com/forums/topic/need-counts-of-payment-amounts-per-week-grouped-by-week#post-3916693

it's almost similar problem but syntax not working and couldn't translate it