Help with a Query involving one table !!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with a Query involving one table !!!

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-30 : 13:00:04

Hi All,
I have a table called [History] with the following fields and types.

Date(datetime), PriceZone(varchar), ProductID(varchar), ProductName(varchar), Category(varchar), SubCategory(varchar), Price(decimal 2 places), Cost(decimal 2 places), Units(Big Int), DollarSales(decimal 2 places), Margin(decimal 2 places), Profit(decimal 2 places)

Each record represent information for each ProductID, per PriceZone, per Date. There's always one record per product; per zone; per Date.
Here is the sample of the records (Just for example I am just using one product in one zone)

Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost, Units, DollarSales, Margin, Profit

2008-04-30,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,10,20,25,5
2008-04-29,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,8,16,25,4
2008-04-28,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,15,30,25,7.5
2008-04-25,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,13,26,25,6.5
2008-04-22,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,11,22,25,5.5
2008-04-21,Zone1,001,Lays Chips,Snacks,Chips,1.98,1.5,15,29.7,24.24,7.2
2008-04-20,Zone1,001,Lays Chips,Snacks,Chips,1.98,1.5,15,29.7,24.24,7.2
2008-04-19,Zone1,001,Lays Chips,Snacks,Chips,1.95,1.5,18,35.1,23.08,8.1
2008-04-16,Zone1,001,Lays Chips,Snacks,Chips,1.9,1.5,20,38,21.05,8
2008-04-15,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.5,40,72,16.67,12
2008-04-10,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.5,32,57.6,16.67,9.6
2008-04-09,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.5,27,48.6,16.67,8.1
2008-04-08,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.4,29,52.2,22.22,11.6
2008-04-01,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.4,30,54,22.22,12

Now using the table information, I want to run a script to get the following fields;

Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost,UnitsSum_ForLast4Dates, UnitsSum_ForLast12Dates, DollarSalesSum_ForLast4Dates, DollarSalesSum_ForLast12Dates, AvgMargin_ForLast4Dates, AvgMargin_ForLast12Dates, ProfitSum_ForLast4Dates, ProfitSum_ForLast12Dates

So output in the above example would be;

Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost,UnitsSum_ForLast4Dates, UnitsSum_ForLast12Dates, DollarSalesSum_ForLast4Dates, DollarSalesSum_ForLast12Dates, AvgMargin_ForLast4Dates, AvgMargin_ForLast12Dates, ProfitSum_ForLast4Dates, ProfitSum_ForLast12Dates

2008-04-30,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,46,224,92.00,424.70,25,22.30,5.00,88.70

Here;
Date is the maximum date available in the entire [History] table. Meaning the "Date" value will be same for all the records in the output.
Price is the Price for that product in that zone on Date
Cost is the cost for that product in that zone on Date

About "Last4Dates" & "Last12Dates":
"Last4Dates" is the last 4 dates in the entire table from 'Date" value , including "Date" value and prior 3 dats before the "Date" value. In the above data set example it would be from "2008-04-25" to "2008-04-30"
"Last12Dates" is the last 12 dates in the entire table from 'Date" value, including "Date" value and prior 11 Dates before the "Date" value. In the above data set example it would be from "2008-04-09" to "2008-04-30"

UnitsSum_ForLast4Dates and UnitsSum_ForLast12Dates is the sum of Units for 4 and 12 weeks respectively; per product, per zone

DollarSalesSum_ForLast4Dates and DollarSalesSum_ForLast12Dates is the sum of DollarSales for 4 and 12 weeks respectively; per product, per zone

AvgMargin_ForLast4Dates and AvgMargin_ForLast12Dates is the Average of DollarSales for 4 and 12 weeks respectively; per product, per zone

ProfitSum_ForLast4Dates and ProfitSum_ForLast12Dates is the sum of Profit for 4 and 12 weeks respectively; per product, per zone

How Can I achieve this in SQL?

Can someone please help me as soon as possible.

Thanks,

Zee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 13:09:25

Are you using SQL 2005 or 2000?

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2008-04-30 : 13:18:46

I am using SQL 2005.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 13:31:48

Try this then:-

SELECT PriceZone, ProductID, ProductName, Category, SubCategory,
MAX(Date) AS Date,
SUM(CASE WHEN RowNo=1 THEN Price ELSE 0 END) AS Price,
SUM(CASE WHEN RowNo=1 THEN Cost ELSE 0 END) AS Cost,
SUM(CASE WHEN RowNo<=4 THEN Units ELSE 0 END) AS UnitsSum_ForLast4Dates,
SUM(CASE WHEN RowNo<=12 THEN Units ELSE 0 END) AS UnitsSum_ForLast12Dates,
SUM(CASE WHEN RowNo<=4 THEN DollarSales ELSE 0 END) AS DollarSalesSum_ForLast4Dates,
SUM(CASE WHEN RowNo<=12 THEN DollarSales ELSE 0 END) AS , DollarSalesSum_ForLast12Dates, 
AVG(CASE WHEN RowNo<=4 THEN Margin ELSE 0 END) AS AvgMargin_ForLast4Dates, 
AVG(CASE WHEN RowNo<=12 THEN Margin ELSE 0 END) AS AvgMargin_ForLast12Dates,
SUM(CASE WHEN RowNo<=4 THEN Profit ELSE 0 END) AS  ProfitSum_ForLast4Dates, 
SUM(CASE WHEN RowNo<=12 THEN Profit ELSE 0 END) AS ProfitSum_ForLast12Dates
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY PriceZone, ProductID, ProductName, Category, SubCategory ORDER BY Date DESC) AS RowNo,
Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost,Units, DollarSales, Margin, Profit
FROM History
)t
GROUP BY PriceZone, ProductID, ProductName, Category, SubCategory

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources