Turning multiple rows into a view of multiple columns, calculating a value

Question

Turning multiple rows into a view of multiple columns, calculating a value

rosewood

Newbie

Points: 2
More actions
January 2, 2010 at 9:21 pm

#386034

I have a fairly simple table for storing sales data with the following columns: Store#, Year, Week, and SaleAmmount (and ID for the Primary Key)
So some records would look like:
00-0001, 2009, 51, 14000
00-0001, 2009, 52, 15000
00-0002, 2009, 51, 13000
00-0002, 2009, 52, 14000
00-0001, 2008, 51, 9000
00-0001, 2008, 52, 9500
00-0002, 2008, 51, 12000
00-0002, 2008, 52, 13000
What I need to do is take that and output it so there is one column that is just the sales weeks #, and each column from there would be each store's sale for a specific year.
(There are no NULL values, but only one year has a 53rd week if that even matters)
Now, if that was not as fun enough, I also need to do some basic calculations.
(That is just takes the sales # from the most recent year, subtracts the previous year and then divides by the previous year for a growth percentage)
Now, This is all on a testing database I am building to test feasibility so if there are any fundamental flaws, I can start from the ground up. As for the calculations, I'm not sure if it makes sense to do this in the reporting tools or in excel.
The general idea here is to reproduce a spreadsheet that had gotten out of hand and had become too much for one person to update. However the view that I describe is how they want to view the data and how they generate all their pretty charts and graphs.
Thank you

Viewing 4 posts - 1 through 3 (of 3 total)

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

rosewood Newbie Points: 2 More actions · Answer 1

I have been working on this all day and I'm still stuck so let me reset this and hopefully I can clear a few things up. So, starting from scratch ...

At the end of each sales week the manager calls corporate office and says "Hi, this is store 00-0001. Week 51 is over for 2009 and we had $14,000 in sales."

Here is my current schema for that record of information and some sample data from two stores for weeks 51 and 52 in years 2008 and 2009:

CREATE TABLE #salestemp

(

ID int,

Store char(7),

Year smallint,

Week tinyint,

SaleAmmount money

)

INSERT INTO #salestemp VALUES (1,'00-0001', 2009, 51, 14000)

INSERT INTO #salestemp VALUES (2,'00-0001', 2009, 52, 15000)

INSERT INTO #salestemp VALUES (3,'00-0002', 2009, 51, 13000)

INSERT INTO #salestemp VALUES (4,'00-0002', 2009, 52, 14000)

INSERT INTO #salestemp VALUES (5,'00-0001', 2008, 51, 9000)

INSERT INTO #salestemp VALUES (6,'00-0001', 2008, 52, 9500)

INSERT INTO #salestemp VALUES (7,'00-0002', 2008, 51, 12000)

INSERT INTO #salestemp VALUES (8,'00-0002', 2008, 52, 13000)

SELECT * FROM #salestemp

What I need to do is create a column for each year and store (So one column for 00-0001's 2009 and another for 00-0001's 2008, and yet another for 00-0002's 2009, and again 00-0002's 2008)

This code works to get me ONE column of data...

SELECT #salestemp.Week, #salestemp.SaleAmmount AS 'Store 00-0001 2009'

FROM #salestemp

WHERE #salestemp.Store = '00-0001' AND #salestemp.Year = '2009'

ORDER BY #salestemp.Week

This is the same screenshot from above that I manually did in excel to hopefully give a better idea of what I'm going for...

I've tried bringing the full table into excel and creating a pivot table there but that isn't working for me because I don't need to sum anything.

I need the data in this presentation format because that is what they are used to looking at, how they want it, and it is the easiest way to compare weeks in each year not only for an individual store but company wide.

I hope this makes sense. I've been wracking my brain on this for a few days now and I'm coming up short.

ricky.chauvin SSC Enthusiast Points: 162 More actions · Answer 2

Do you have SSRS at your disposal? Its a pretty powerful tool for these kind of things. I use it on a fairly regular basis these days and havn't been on any programming courses for it (or anything else for that matter).

You could set up parameters that refer to the current date and do calculations based on that. Im afraid its difficult to explain through here though.

I'll help if/where i can

LutzM SSC Guru Points: 107049 More actions · Answer 3

If you want/need to do it without SSRS you might want to read the two articles I referenced below regarding CrossTab and DynamicCrossTab.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]