July 18, 2012 at 8:18 am
Hi,
I need to count the last year's first 4 week's sales data.I have DDL script below.
Please help me to build query.
Thanks in advances,
CREATE TABLE [dbo].[SalesHistory](
[StoreNo] [int] NOT NULL,
[WeekEndDt] [datetime] NOT NULL,
[WeekEndDtInt] [int] NULL,
[Sales] [money] NULL
)
GO
Insert Into SalesHistory Values(101,'2011-01-07 00:00:00.000',20110107,91397.67)
Insert Into SalesHistory Values(101,'2011-01-14 00:00:00.000',20110114,80166.89)
Insert into SalesHistory Values (23876,'2011-01-21 00:00:00.000',20110121,9878.86)
Insert into SalesHistory Values (23876,'2011-02-28 00:00:00.000',20110228,5378.86)
Insert into SalesHistory Values (23876,'2011-01-07 00:00:00.000',20110107,3128.86)
Insert into SalesHistory Values (23876,'2011-02-14 00:00:00.000',20110214,41278.86)
July 18, 2012 at 8:25 am
Can you also provide us the expected results based on your sample data?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 18, 2012 at 8:52 am
here is my expected result looks
WeekEndDate SalesTotal
2011-01-07 94526.53
2011-01-14 121445.75
2011-01-21 9878.86
2011-01-28 5378.86
July 18, 2012 at 9:59 am
A couple things, there is no date for 1/28/2011 as you specify in your expected result so I'm assuming that was a typo on the insert statement. I see another possible typo for 2/14, should that also be 1/14?
Second comment is to define what a "week" is. January of 2011 is a perfect example as it has 6 weeks in the month. Is a week defined as 7 consecutive days or Sun-Sat?
Here's something that should get you started
SELECT WeekEndDt,SUM(Sales) sales
FROM SalesHistory
WHERE DATEPART(yyyy,weekenddt) = DATEPART(yyyy,current_timestamp) - 1
AND DATEPART(WEEK,weekenddt) between 1 and 4
group by WeekEndDt
July 18, 2012 at 10:04 am
What makes November the first week of last year?
{Edit: Dint look at the sample data properly}
July 18, 2012 at 10:12 am
First, by "count of" sales I assume you mean SUM of sales. Otherwise we'd be looking at a result set a little more like:
WeekEndDate SalesTotal
2011-01-07 1
2011-01-14 1
2011-01-21 1
2011-01-28 1
Lets try this...
Sample Data:
IF OBJECT_ID('tempdb..#SalesHistory') IS NOT NULL
DROP TABLE #SalesHistory
CREATE TABLE #SalesHistory
(
[StoreNo] [int] NOT NULL,
[WeekEndDt] [datetime] NOT NULL,
[WeekEndDtInt] [int] NULL,
[Sales] [money] NULL
) ;
Insert Into #SalesHistory Values(101,'2011-01-07 00:00:00.000',20110107,91397.67)
Insert Into #SalesHistory Values(101,'2011-01-14 00:00:00.000',20110114,80166.89)
Insert into #SalesHistory Values (23876,'2011-01-21 00:00:00.000',20110121,9878.86)
Insert into #SalesHistory Values (23876,'2011-02-28 00:00:00.000',20110228,5378.86)
Insert into #SalesHistory Values (23876,'2011-01-07 00:00:00.000',20110107,3128.86)
Insert into #SalesHistory Values (23876,'2011-02-14 00:00:00.000',20110214,41278.86)
-- Add some sales for this year and one for 1/28/2012 for testing
Insert into #SalesHistory Values (555555,'2011-01-28 00:00:00.000',20110128,77777.22)
Insert into #SalesHistory Values (889999,'2012-02-10 00:00:00.000',20120210,12780.55)
Insert into #SalesHistory Values (889999,'2012-02-01 00:00:00.000',20120201,51275.66)
I will take what you said litterally and, by "last year", will assume you are talking about the year before this one. In other words, by "last year" you are talking about a dynamic value that changes anually (in 2013, last year = 2012).
First, lets get last year:
DECLARE @ly int = (SELECT DATEPART(YY,GETDATE())-1) --Last year
Now lets get you the SUM of your Sales by WeekEndDt:
SELECT WeekEndDt, SUM([Sales]) [Sales]
FROM #SalesHistory
GROUP BY WeekEndDt
That will get you what you are looking for except for the desired date range; now let's get that too.
DECLARE @ly int = (SELECT DATEPART(YY,GETDATE())-1) --Last year
-- Base
SELECT WeekEndDt, SUM([Sales]) [Sales]
FROM #SalesHistory
-- criteria
WHERE DATEPART(YY,WeekEndDt)= @ly --using WeekEndDt becuase WeekEndDtInt is Nullable
AND DATEPART(MM,WeekEndDt) = 1
AND DATEPART(DD,WeekEndDt) <=28
--grouping
GROUP BY WeekEndDt
Finally, lets put it together:
IF OBJECT_ID('tempdb..#SalesHistory') IS NOT NULL
DROP TABLE #SalesHistory
CREATE TABLE #SalesHistory
(
[StoreNo] [int] NOT NULL,
[WeekEndDt] [datetime] NOT NULL,
[WeekEndDtInt] [int] NULL,
[Sales] [money] NULL
) ;
Insert Into #SalesHistory Values(101,'2011-01-07 00:00:00.000',20110107,91397.67)
Insert Into #SalesHistory Values(101,'2011-01-14 00:00:00.000',20110114,80166.89)
Insert into #SalesHistory Values (23876,'2011-01-21 00:00:00.000',20110121,9878.86)
Insert into #SalesHistory Values (23876,'2011-02-28 00:00:00.000',20110228,5378.86)
Insert into #SalesHistory Values (23876,'2011-01-07 00:00:00.000',20110107,3128.86)
Insert into #SalesHistory Values (23876,'2011-02-14 00:00:00.000',20110214,41278.86)
-- Add some sales for this year and one for 1/28/2012 for testing
Insert into #SalesHistory Values (555555,'2011-01-28 00:00:00.000',20110128,77777.22)
Insert into #SalesHistory Values (889999,'2012-02-10 00:00:00.000',20120210,12780.55)
Insert into #SalesHistory Values (889999,'2012-02-01 00:00:00.000',20120201,51275.66)
--SELECT * FROM #SalesHistory
DECLARE @ly int = (SELECT DATEPART(YY,GETDATE())-1) --Last year
-- Base
SELECT WeekEndDt, SUM([Sales]) [Sales]
FROM #SalesHistory
-- criteria
WHERE DATEPART(YY,WeekEndDt)= @ly --using WeekEndDt becuase WeekEndDtInt is Nullable
AND DATEPART(MM,WeekEndDt) = 1
AND DATEPART(DD,WeekEndDt) <=28
--grouping
GROUP BY WeekEndDt
Hope that helps.
-- Itzik Ben-Gan 2001
July 18, 2012 at 12:21 pm
Thanks to Calvo, Coldcoffee and Special thanks to Alan.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply