February 1, 2018 at 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!! 🙂
February 1, 2018 at 7:51 am
meichmann - Thursday, February 1, 2018 7:14 AMHello 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!! 🙂
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.
February 1, 2018 at 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])
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".
February 1, 2018 at 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!
February 1, 2018 at 9:20 am
You're welcome! I'm very glad it helped.
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".
February 1, 2018 at 4:53 pm
meichmann - Thursday, February 1, 2018 9:14 AMScottPletcher - 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
Change is inevitable... Change for the better is not.
February 2, 2018 at 9:27 am
Jeff Moden - Thursday, February 1, 2018 4:53 PMmeichmann - Thursday, February 1, 2018 9:14 AMScottPletcher - 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 !
February 2, 2018 at 3:40 pm
meichmann - Friday, February 2, 2018 9:27 AMJeff Moden - Thursday, February 1, 2018 4:53 PMmeichmann - Thursday, February 1, 2018 9:14 AMScottPletcher - 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
Change is inevitable... Change for the better is not.
February 3, 2018 at 9:11 pm
meichmann - Thursday, February 1, 2018 7:14 AMHello 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!! 🙂
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.
February 4, 2018 at 8:32 am
jcelko212 32090 - Saturday, February 3, 2018 9:11 PMBad 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-54.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-[highlight="Yellow"]03-02[/highlight] 00:00:00.000
--Jeff Moden
Change is inevitable... Change for the better is not.
February 5, 2018 at 5:56 am
jcelko212 32090 - Saturday, February 3, 2018 9:11 PM... SQL forums require that you post DDL, ...
I never saw that in the terms of use: http://www.sqlservercentral.com/about/terms
A good idea, certainly. More likely that someone will help you, certainly. But required? Nope.
February 5, 2018 at 6:01 am
jcelko212 32090 - Saturday, February 3, 2018 9:11 PMmeichmann - Thursday, February 1, 2018 7:14 AMHello 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!! 🙂
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.
Troll
February 5, 2018 at 6:06 am
Jeff Moden - Friday, February 2, 2018 3:40 PMmeichmann - Friday, February 2, 2018 9:27 AMJeff Moden - Thursday, February 1, 2018 4:53 PMmeichmann - Thursday, February 1, 2018 9:14 AMScottPletcher - 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?
Yes it is. If the month, or even year rolls over during the business week, then it's ok. 🙂
February 5, 2018 at 2:40 pm
meichmann - Monday, February 5, 2018 6:06 AMYes 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?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 6, 2018 at 5:23 am
Jeff Moden - Monday, February 5, 2018 2:40 PMmeichmann - Monday, February 5, 2018 6:06 AMYes 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. 🙂
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply