February 20, 2014 at 8:07 am
Hi everyone
I have asales table with the following structure:
Store varchar(50),
Area varchar(50),
Sales decimal(18,6),
Stock decimal(18,6)
I would like to split the Sales/Stock totals into weekly sums ie if there were 5,000 sales for the the year for Store A in Area B i would like this to be divided by 52 and for there to be 52 records for the store rather than the current 1.
There are no date fields in the data at the moment so I cant use any of those functions.
Hope someone out there can help...
BO
February 20, 2014 at 9:16 am
If you have no date columns then how can you tell if a group of sales all occurred in one year? It doesn't make much sense to me.
In any case you could do it with some variation of this (with sample data):
CREATE TABLE #blah
(Store varchar(50),
Area varchar(50),
Sales decimal(18,6),
Stock decimal(18,6))
INSERT INTO #blah (Store, Area, Sales, Stock)
VALUES ('Store 2', 'Area 2', 11, 1100)
GO 1000
INSERT INTO #blah (Store, Area, Sales, Stock)
VALUES ('Store 1', 'Area 1', 10, 100)
GO 1000
with fiftytwo as
(
SELECT
store,
area,
SUM(sales) / 52 AS sales
FROM #blah
GROUP BYstore,
area)
SELECT
b.store,
b.area,
ft.sales
FROM #blah b
INNER JOIN
fiftytwo ft
ON ft.store = b.store
AND ft.area = b.area
February 20, 2014 at 9:43 am
Thanks for the speedy response...
The whole purpose is actually to try and forecast for the forthcoming year based on last years performance but the file does not contain the Sales by date - ridiculous I know...
Your code splits the data as I would like but I need each of the new records to be given a week number (1 through to 52) so that I then have the data split by weeks....this would be a new column I guess...
Any further help greatly appreciated...
BO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply