Need help in T-sql to get the Last Year's first 4 week sales data

Question

Post reply

Need help in T-sql to get the Last Year's first 4 week sales data

Jay Pete

SSCommitted

Points: 1945
More actions
July 18, 2012 at 8:18 am

#259090

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)

Viewing 7 posts - 1 through 6 (of 6 total)

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

Can you also provide us the expected results based on your sample data?

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jay Pete SSCommitted Points: 1945 More actions · Answer 2

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

calvo SSChampion Points: 12930 More actions · Answer 3

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

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

~~What makes November the first week of last year?~~

{Edit: Dint look at the sample data properly}

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

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: