SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need counts of orders per week grouped by week


Need counts of orders per week grouped by week

Author
Message
meichmann
meichmann
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 263

Hello Everyone,
I hope someone can help me out with this. I have an order table that stores order information.

Order Number

Order Date

1

01/01/18

2

01/04/18

3

01/09/18

4

01/17/18

5

01/18/18

What I need is to get total counts per week so the result would look like this:

Week of

Count

01/01/08

2

01/08/18

1

01/15/18

2


I also need this to be date driven so the user can put in a start and end date.

Thanks for all your help!! Smile



Subramaniam Chandrasekar
Subramaniam Chandrasekar
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2214 Visits: 527
meichmann - Thursday, February 1, 2018 7:14 AM

Hello Everyone,
I hope someone can help me out with this. I have an order table that stores order information.

Order Number

Order Date

1

01/01/18

2

01/04/18

3

01/09/18

4

01/17/18

5

01/18/18

What I need is to get total counts per week so the result would look like this:

Week of

Count

01/01/08

2

01/08/18

1

01/15/18

2


I also need this to be date driven so the user can put in a start and end date.

Thanks for all your help!! Smile



At the moment, I've not logged on. So I'd suggest you the below,

Please group the table by week of Order date.

Create some aliases like

01/01/18 to 01/07/2018 as '01/01/18'

01/08/18 to 01/15/2018 as '01/08/18'

01/16/18 to 01/23/2018 as '01/16/08' ( date names are your wish)

Once you've developed the query, You can create SSRS report and a data driven subscription for that. This will satisfy your requirement.

ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65230 Visits: 8732

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])



SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
meichmann
meichmann
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 263
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])



This code is exactly what I need! Thank you so much!
ScottPletcher
ScottPletcher
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65230 Visits: 8732
You're welcome! I'm very glad it helped.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)

Group: General Forum Members
Points: 689557 Visits: 45611
meichmann - Thursday, February 1, 2018 9:14 AM
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])



This code is exactly what I need! Thank you so much!

Fast forward 1 year to 2019 using the same day of the month for January. Because January 2019 starts on a Tuesday instead of a Monday, what would you want for the answer?


--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
meichmann
meichmann
Say Hey Kid
Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)Say Hey Kid (694 reputation)

Group: General Forum Members
Points: 694 Visits: 263
Jeff Moden - Thursday, February 1, 2018 4:53 PM
meichmann - Thursday, February 1, 2018 9:14 AM
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])



This code is exactly what I need! Thank you so much!

Fast forward 1 year to 2019 using the same day of the month for January. Because January 2019 starts on a Tuesday instead of a Monday, what would you want for the answer?


I'm not concerned what day the month or year starts. I'm more concerned with the start of the week. this query works as needed. but thanks !
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)

Group: General Forum Members
Points: 689557 Visits: 45611
meichmann - Friday, February 2, 2018 9:27 AM
Jeff Moden - Thursday, February 1, 2018 4:53 PM
meichmann - Thursday, February 1, 2018 9:14 AM
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])



This code is exactly what I need! Thank you so much!

Fast forward 1 year to 2019 using the same day of the month for January. Because January 2019 starts on a Tuesday instead of a Monday, what would you want for the answer?


I'm not concerned what day the month or year starts. I'm more concerned with the start of the week. this query works as needed. but thanks !


I get that... just remember that if you believe that Monday's should be the first day of the week (and, I do), that the first day of the first week in January of 2019 is actually the last day of the year for 2018. Is that going to be acceptable for your reporting purposes?

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Joe Celko
Joe Celko
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4279 Visits: 2919
meichmann - Thursday, February 1, 2018 7:14 AM

Hello Everyone,
I hope someone can help me out with this. I have an order table that stores order information.

Order Number

Order Date

1

01/01/18

2

01/04/18

3

01/09/18

4

01/17/18

5

01/18/18

What I need is to get total counts per week so the result would look like this:

Week of

Count

01/01/08

2

01/08/18

1

01/15/18

2


I also need this to be date driven so the user can put in a start and end date.

Thanks for all your help!! Smile




Bad manners and ignorance are not a good way to go through a career in IT. SQL forums require that you post DDL, so the people that are giving you free consulting do not have to transcribe data from the pictures on their screens. Not only did you post pictures, but you even seem to know that standard ANSI/ISO Standard SQL only allows for dates to be formatted as "yyyy-mm-dd" strings.

This is based on the ISO 8601 temporal display standards. Anybody in IT should know what because the second most popular standard after the metric system. Would you trust an engineer who doesn't know the metric system and uses cubits?

This standard includes the ISO weekly date format,.: "yyyyW[1-5][0-9]-[1-7]" the first four digits are the year, the next token is a W, followed by the number of the week within the year (1 to 52 or 53) another piece of punctuation the –, and the day of the week (Monday =1). This calendar is very popular in the Nordic countries, and you can find conversion tables on the Internet. Would you can download into your calendar table. If you don't know what a calendar table is, please Google it; it's a fundamental programming idiom in SQL.

Please post DDL and follow ANSI/ISO standards when asking for help.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)SSC Guru (689K reputation)

Group: General Forum Members
Points: 689557 Visits: 45611
jcelko212 32090 - Saturday, February 3, 2018 9:11 PM
Bad manners and ignorance are not a good way to go through a career in IT.

Considering your normal responses (your most recent above being no exception) to people, Joe, you have ZERO room to give this type of advice to anyone until you first straighten yourself out so that you don't also post apparently ignorant responses.

Not only did you post pictures, but you even seem to know that standard ANSI/ISO Standard SQL only allows for dates to be formatted as "yyyy-mm-dd" strings.
...{snip}...
This is based on the ISO 8601 temporal display standards. Anybody in IT should know what because the second most popular standard after the metric system.

You used the word "ignorance" in your response to the OP. Perhaps you should work on your own ignorance a bit because your statement above is totally false. The basic acceptable format in the standards you've cited is YYYYMMDD and they list YYYY-MM-DD as an acceptable extension. The following is a quote from the ISO/WD 8601-1 standard (dated 2016-02-16) that you keep citing and, apparently, haven't read.


4.1.2.2 Complete representations

When the application identifies the need for a complete representation of a calendar date, it shall be one of the numeric expressions as follows, where [YYYY] represents a calendar year, [MM] the ordinal number of a calendar month within the calendar year, and [DD] the ordinal number of a calendar day within the calendar month.

Basic format: YYYYMMDD Example: 19850412
Extended format: YYYY-MM-DD Example: 1985-04-12



You also speak of the ISO week format...

This standard includes the ISO weekly date format,.: "yyyyW[1-5][0-9]-[1-7]" the first four digits are the year, the next token is a W, followed by the number of the week within the year (1 to 52 or 53) another piece of punctuation the –, and the day of the week (Monday =1).


While that's correct, it certainly isn't the only format (implied by your use of the word "the"). The following is from the ISO/WD 8601-1 standard, which allows for 3 other formats as well.

4.1.4.2 Complete representations

When the application identifies the need for a complete representation of a week date, it shall be one of the alphanumeric expressions as follows, where [YYYY] represents a calendar year, [W] is the week designator, [ww] represents the ordinal number of a calendar week within the year, and [D] represents the ordinal number of a calendar day within the calendar week.

Basic format: YYYYWwwD Example: 1985W155
Extended format: YYYY-Www-D Example: 1985-W15-5

4.1.4.3 Representations with reduced accuracy

If the degree of accuracy required permits, one digit may be omitted from the representation in 4.1.4.2.

A specific week
Basic format: YYYYWww Example: 1985W15
Extended format: YYYY-Www Example: 1985-W15


Unfortunately, such a format isn't normally acceptable for human consumption because humans cannot quickly resolve such representations to a Calendar Date never mind the fact that the first week of an ISO year may contain days from the previous year and the last week of an ISO year may contain days from the next year. Except for extremely esoteric reports and date stamps on cans of cat food and the like (which is also stupid because most consumers can't interpret what it means), the ISO week format should generally and usually be avoided for reporting purposes. Reports intended for human consumption should use one of the two acceptable ISO formats for the date of the beginning of an ISO week, instead and, contrary to popular belief, it's actually ok to customize the date formats for the intended audience. It's just not appropriate to store any formatted dates in the source data tables of a data tables.

Last but not least, standards are only good if the manufacturers of things like relational databases actually follow them. For example, the highly touted ISO standard of YYYY-MM-DD totally falls apart if the default language doesn't actually support it. For example, if the default language for an SQL Server installation is "French", the format silently changes to YYYY-DD-MM. That means that the Extended ISO format of allowing dashes to separate the date parts provides insitu and esoteric danger and should probably be avoided altogether.


SET NOCOUNT ON
;
PRINT '===== YYYYMMDD is interpreted correctly in SQL Server in the following cases.';
SET LANGUAGE english;
SELECT CONVERT(DATETIME,'20180203');
GO
SET LANGUAGE french;
SELECT CONVERT(DATETIME,'20180203');
GO
PRINT '===== YYYYMMDD is NOT interpreted correctly in SQL Server in the following cases.';
SET LANGUAGE english;
SELECT CONVERT(DATETIME,'2018-02-03');
GO
SET LANGUAGE french;
SELECT CONVERT(DATETIME,'2018-02-03');
GO

Results from above...

===== YYYYMMDD is interpreted correctly in SQL Server in the following cases.
Changed language setting to us_english.

-----------------------
2018-02-03 00:00:00.000

Le paramètre de langue est passé à Français.

-----------------------
2018-02-03 00:00:00.000

===== YYYYMMDD is NOT interpreted correctly in SQL Server in at least one case.
Changed language setting to us_english.
-----------------------
2018-02-03 00:00:00.000

Le paramètre de langue est passé à Français.
-----------------------
2018-03-02
00:00:00.000




--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search