June 6, 2009 at 1:02 am
Hi
We have tables called Customers, product, and OrderHistory. I need to query sales data based on weekely basis for reporting purpose. Our current calender has begun from December 28, 2008 and ends on December 27 2009. How can I split this calender year into 52 weeks and retrieve the sales amount for each week . For example:
Week1(12/28/2008-01/03/2009) - 1,55,000
Week2(01/04/2009-01/10/2009) - 1,35,000
and so on.
I would appreciate your suggestions.
Thanks
June 6, 2009 at 1:39 am
You might want to look up DATEPART function in BOL.
Then you can use it to do a SUM() GROUP BY DATEPART().
If you need a more detailed answer you should provide more detailed information including table definition(s), sample data, expected result set based on sample data, what you've tried so far and what you're struggling with.
For details on how to post data please see http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 6, 2009 at 9:22 am
My challange is as follows
Business Calender year starts from 12/28/2007 and ends on 12/27/2008
A part of the table is:
BusinessDate(DateTime)QuantitySold(int)
12/28/2007 25000
01/02/2008 35000
01/09/2008 45000
............... ........
12/25/2008 20000
Expected Output(for Reporting)
PeriodQuantitySold
WK1(12/28/2007 - 01/03/200860000
WK2(01/04/2008 - 01/10/200845000
………………….………………………………
WK52(2/21/2008 - 12/27/200820000
June 6, 2009 at 9:32 am
My challenge is as follows
Business Calender year starts from 12/28/2007 and ends on 12/27/2008
A part of the table is:
BusinessDate(DateTime), QuantitySold(int)
12/28/2007, 25000
01/02/2008, 35000
01/09/2008, 45000
..............., ........
12/25/2008, 20000
Expected Output(for Reporting)
Period, QuantitySold
WK1(12/28/2007 - 01/03/2008), 60000
WK2(01/04/2008 - 01/10/2008), 45000
…………………., ………………………………
WK52(2/21/2008 - 12/27/2008), 20000
I am not able to aggregate by week
How can I Break a calender year( 12/28/2007 - 12/27/2008) into 52 weeks?
June 6, 2009 at 11:26 am
Did you look into BooksOnline (aka BOL), the SQL Server Help that usually is installed together with SQL Server? You can open it by using {F1} from the Management Studio.
There you can type "DATEPART" in the search field and you'll find more information on DATEPART.
Don't get me wrong but I'm under the impression that you neither looked into BOL nor followed the link I posted above on how to post sample data.
But, if you don't want to use DATEPART, you could use the fact that a week has seven days...
SELECT DATEDIFF(dd,'12/28/2007',BusinessDate)/7 +1 would give you the week with reference to your first business day.
If you frequently need to work with your business calender and do some grouped reporting based on it you might want to consider using a calendar table.
This table could have a separate column identifying the business week for any given date and maybe other business year specific columns (e.g. fiscal_year, fiscal_quarter, is_working_day, a.s.o.)
Then you would just need to join your table to the calendar table. This way it would also make it a lot easier to get the first and the last day of the week.
June 6, 2009 at 12:11 pm
Hi Lutz,
Thanks for your reply.
This is my first posting. I will follow your suggestion from next posting.
Again,
I thank you for your advice
G
June 8, 2009 at 7:51 am
It sounds as if you are trying to report based upon a FISCAL Calendar which SQL does little support. In a true fiscal Calendar you break the year into exactly 52 weeks and each month will have exactly 4 or 5 weeks per month. A week will not start in one month and end in another month. The challenge to this is that Feb 1 could belong to Jan.
Is this what your requirements are calling for?
June 8, 2009 at 1:59 pm
Yes, I am trying to create a report based on fiscal year.
We have many tables such as Customer, Product, Order, OrderHistory etc in SQL Server 2005. When the customer buy the product, transaction date(datetime) field is recorded on the database.
But I need to create a query to run the report on weekly sales. I have attached he sample requirement as an attachment.
The fiscal year begins on December 28 2007(Sunday) and ends on December 27 2008(Saturday).
The clint does not care about the month. He wants weekly total sales quantity from Sunday to Saturday beginning from December 28, 2008 in a Matrix form. So I should have 52 columns in the report.
I can join all the tables and find the total Quantity by product by fiscal year for that year.
However, I am unable to query the total quantity by product by week.
In sum, Can I make 52 weeks in my calender year(12/28/2007 to 12/27/2008) by using SQL datetime function?
I thank you for your cooperation.
Ganesh
June 8, 2009 at 3:05 pm
SQL Server date functions don't calculate FISCAL dates very well mainly because of the multiple ways you can assign the 5th week of the month. I understand that your requirements do not call for this level but month is part of the natural time hierarchy. Also remember that a true fiscal year does not start on the same day every year.
Typically the easiest way to handle this is to create a table that you can have your predefined fiscal periods in.
June 8, 2009 at 10:55 pm
{edit} Thanks to David Jackson. I was missing the WHERE clause in the final dynamic SQL and the caused the totals at the right of the output to be totals for the entire ten years instead of just the current fiscal year. I've added his correction to the code below. The weekly numbers were ok.
What we need here, is some code. 😛
[font="Courier New"]--DROP TABLE JBMTest
GO
--===== Create and populate a 1,000,000 row test table.'
-- THIS IS NOT A PART OF THE SOLUTION. IT'S A TEST TABLE.
-- Jeff Moden
SELECT TOP 1000000
SomeID = IDENTITY(INT,1,1),
ProductName = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
BusinessDate = CAST(ABS(CHECKSUM(NEWID()))%3653+36524 AS DATETIME),
QuantitySold = ABS(CHECKSUM(NEWID()))%100+1
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1
CROSS JOIN Master.dbo.SysColumns t2
--===== Add a clustered key
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (SomeID)
--==============================================================================
-- Solution starts here
--==============================================================================
--===== Declare a couple of obviously named variables
DECLARE @FiscalYearStartDate DATETIME,
@SQL VARCHAR(MAX)
--===== Define the beginning of the fiscal year
SELECT @FiscalYearStartDate = '12/28/2008'
--===== Define the first static part of the dynamic SQL
SELECT @SQL = 'SELECT ProductName,' + CHAR(10)
--===== Define the dynamic select list of the dynamic SQL
;WITH
cteDates AS
(
SELECT CAST(v.Number + 1 AS VARCHAR(2)) AS Week,
DATEADD(wk, v.Number, @FiscalYearStartDate) AS StartDate,
DATEADD(wk, v.Number+1, @FiscalYearStartDate)-1 AS EndDate,
DATEADD(wk, v.Number+1, @FiscalYearStartDate) AS NextStartDate
FROM Master.dbo.spt_Values v
WHERE Type = 'P'
AND v.Number BETWEEN 0 and 53
AND YEAR(DATEADD(wk, v.Number+1, @FiscalYearStartDate)) = ''' + CONVERT(CHAR(10),StartDate,101)
+ ''' AND BusinessDate = ''' + CONVERT(CHAR(10),@FiscalYearStartDate,101)
+ ''' AND BusinessDate <= DateAdd(yy,1,''' + CONVERT(CHAR(10),@FiscalYearStartDate,101) + '''))' + CHAR(10)
+ 'GROUP BY ProductName' + CHAR(10)
+ 'ORDER BY ProductName' + CHAR(10)
--===== Print out the Dynamic SQL so we can see it, then execute it
PRINT @SQL
EXEC (@SQL)
[/font]
For more information on how that was done, please see the following article...
http://www.sqlservercentral.com/articles/cross+tab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2009 at 10:58 pm
ganeshlohani,
Part of the reason that you only got suggestions instead of code is because you didn't post your problem correctly so that people had some readily consumable data to test against. Please see the article as the link in my signature line below for how to post properly which will get you much better answers much more quickly.
People DID suggest it a couple of times but you didn't take their suggestion. You may not get someone like me to take pity on your problem the next time without posting correctly. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2009 at 11:07 pm
Paul M. Corley (6/8/2009)
SQL Server date functions don't calculate FISCAL dates very well mainly because of the multiple ways you can assign the 5th week of the month. I understand that your requirements do not call for this level but month is part of the natural time hierarchy. Also remember that a true fiscal year does not start on the same day every year.Typically the easiest way to handle this is to create a table that you can have your predefined fiscal periods in.
Not necessarily true. Two of the companies I last worked for were easy to calculate Fiscal Periods. Fiscal Year started on the same day each year and ended on the same day each year, for instance, FY2001 at one company started 12/1/2000 and ended 11/30/2001. Each month was a fiscal month. Real easy. Now when I worked for Data General, that was a different story. The third month in each fiscal quarter was 5 weeks long, except one fiscal year where the last month of the fourth quarter was actually 6 weeks long.
June 9, 2009 at 12:01 am
Paul M. Corley (6/8/2009)
Also remember that a true fiscal year does not start on the same day every year.
Heh... "it depends".
--Jeff Moden
Change is inevitable... Change for the better is not.
June 9, 2009 at 9:08 am
Hello all,
I thank you for your suggestions
Finally, after struggling many days, I am able to deliver weekly sales report to my client.
Ganesh
June 10, 2009 at 7:28 am
I like Jeff's solution as an example of how to do this in code.
We use a Fiscal Calendar table for a few of reasons.
It's easy to maintain, and it can be easily reused (both for SQL and in our Cube).
And we can add additional fields (shop floor work days, sales days) to do additional calculations (daily rates) very easily.
Make sure your client knows how to maintain whatever solution you choose.
Fiscal Calendars can change. And in Jeff's example, what happens next year?
Greg E
Jeff -
I like my chops well done, with some Gates BBQ sauce. :w00t:
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply