Help with T-SQL using one table to get the data !!

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 2000 Forums

Transact-SQL (2000)

Help with T-SQL using one table to get the data !!

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-14 : 15:01:46

Hi All,

Based on the following problem I am looking for a script rather than a stored procedure so in case if needed I can make further changes easily to the solution. I would really appreciate if someone helps me & use variables wherever possible so that if I have to make changes I can just change the value of the variable at one place.

I have a table called Saleshistory with the following fields and type.
ZoneID (nvarchar type), SaleDate (Datetime type),ProductID (nvarchar type), Cost (decimal type), Price (decimal type), Units (bigInt type), DollarSales (decimal type)

The records are like this (each column is separated my comma):
ZoneID (nvarchar type), SaleDate (Datetime type),ProductID (nvarchar type), Cost (decimal type), Price (decimal type), Units (bigInt type), DollarSales (decimal type)

700, 2006-09-16,000-09531-10184, 1.16, 1.50, 7514, 11271.00
700, 2006-09-09,000-09531-10184, 1.17, 1.49, 9435, 14058.15
700, 2006-09-02,000-09531-10184, 1.17, 1.49, 8631, 12860.19
700, 2006-08-26,000-09531-10184, 1.16, 1.50, 7310, 10965.00
700, 2006-08-19,000-09531-10184, 1.16, 1.50, 7207, 10810.50
700, 2006-08-12,000-09531-10184, 1.16, 1.88, 4500, 8460.00
700, 2006-08-05,000-09531-10184, 1.16, 1.88, 5725, 10763.00
700, 2006-07-29,000-09531-10184, 1.16, 1.88, 5240, 9851.20
700, 2006-07-22,000-09531-10184, 1.16, 1.88, 5297, 9958.36
700, 2006-07-15,000-09531-10184, 1.16, 1.88, 5575, 10481.00
700, 2006-07-08,000-09531-10184, 1.17, 1.74, 7095, 12345.30
700, 2006-07-01,000-09531-10184, 1.17, 1.49, 8633, 12863.17

Just to give you an idea this Saleshistory table holds information from a grocery retail zone (zone is a bunch of stores grouped together to be priced similarly). Each record in this table represents Price, Cost, Units, & DollarSales of each product that was sold on a particular date in a particular zone. In this table there's always one record of Price, Cost, Units, and DollarSales per ProductID; per ZoneID; per Saledate. Obviously not all products where sold in each zone on each date.
Also not that Saledate is 1 week actually; meaning that in 1 year of SalesHistory there should be 52 distinct Saledate.

The client does not provide us the Promotion information. The promotion information is a decrease in price which usually encourage customer to buy more and thus it increase Units and DollarSales. Promotion tells that during which period a particular product in a particular zone is on promotion with this price & this cost.

The promotion should have the following fields.

ZoneID (nvarchar), ProductID (nvarchar), PromoStartdate (datetime), PromoEnddate (datetime), PromoPrice (decimal), PromoCost (decimal)

Where PromoStartdate and PromoEnddate represents the duration where a product in a particular zone is on promotion during that period. Please note that PromoStartdate and PromoEnddate cannot be same, i.e the promotion duration should be of at least 1 week (for example from 2006-01-01 to 2006-01-08 which is 1 week)

There's always one PromoPrice and PromoCost record per zone; per product; per duration (PromoStartdate & PromoEnddate).

The records are like this (each column is sepetrated my comma):
ZoneID (nvarchar), ProductID (nvarchar), PromoStartdate (datetime), PromoEnddate (datetime), PromoCost (decimal), PromoPrice (decimal)

700, 000-09531-10184, 2006-01-01, 2006-01-24, 1.16, 1.49

Note here that the product (000-09531-10184) is on promotion from 2006-01-01 to 2006-01-24 with a price of $1.16 in Zone 700.

Now since the Client cannot provide us the Promotional information I have to use Saleshistory table to get the Promotional information. For each product per Zone and for a particular period (12 week) I want to get the AvgPrice, AvgCost, AvgUnits, and AvgDollarSales. Then to determine if a product is on promotion I want to compare the Averages with the individual SaleDate numbers.

Please READ BELOW which will explain clearly.

Following are the steps that I am looking for. I am not sure if these steps can be perform together in one step, thats why just to be neat & clear I have define them separately.

STEP 1:
For each product in each zone I need to get Average (Price), Average (Cost), Average (Units), and Average (DollarSales) for consecutive 12 weeks (i.e consecutive12 Saledate). So I will add these three new columns to the Saleshistory table.
I also want to add the Duration breakdown date that is the start of the 12 week (12 SaledDate) duration and the End of the 12 week (12 SaleDate) Duration. Please not the six new columns in the end.

ZoneID (nvarchar type), SaleDate (Datetime type),ProductID (nvarchar type), Cost (decimal type), Price (decimal type), Units (bigInt type), DollarSales (decimal type), AvgPrice (decimal), AvgCost (decimal), AvgUnits (Big Int), AvgDollarSales (decimal), , StartDuration (datetime), EndDuration (datetime)

So the above 12 SalesHistory records will become as follows (each column is separated my comma):

ZoneID (nvarchar type), SaleDate (Datetime type),ProductID (nvarchar type), Cost (decimal type), Price (decimal type), Units (bigInt type), DollarSales (decimal type), AvgPrice (decimal), AvgCost (decimal), AvgUnits (Big Int), AvgDollarSales (decimal), StartDuration (datetime), EndDuration (datetime)

700, 2006-09-16, 000-09531-10184, 1.16, 1.50, 7514, 11271.00, 1.68, 1.16, 6847, 11223.91, 2006-07-01, 2006-09-16
700, 2006-09-09, 000-09531-10184, 1.17, 1.49, 9435, 14058.15, 1.68, 1.16, 6847, 11223.91, 2006-07-01, 2006-09-16
700, 2006-09-02, 000-09531-10184, 1.17, 1.49, 8631, 12860.19, 1.68, 1.16, 6847, 11223.91, 2006-07-01, 2006-09-16
700, 2006-08-26, 000-09531-10184, 1.16, 1.50, 7310, 10965.00, 1.68, 1.16, 6847, 11223.91, 2006-07-01, 2006-09-16
700, 2006-08-19,000-09531-10184, 1.16, 1.50, 7207, 10810.50, 1.68, 1.16, 6847, 11223.91, 2006-07-01, 2006-09-16
700, 2006-08-12, 000-09531-10184, 1.16, 1.88, 4500, 8460.00, 1.68, 1.16, 6847, 11223.91, 2006-07-01, 2006-09-16
700, 2006-08-05, 000-09531-10184, 1.16, 1.88, 5725, 10763.00, 1.68, 1.16, 6847, 11223.91, 2006-07-01, 2006-09-16
700, 2006-07-29, 000-09531-10184, 1.16, 1.88, 5240, 9851.20, 1.68, 1.16, 6847, 11223.91, 2006-07-01, 2006-09-16
700, 2006-07-22, 000-09531-10184, 1.16, 1.88, 5297, 9958.36, 1.68, 1.16 6847, 11223.91, 2006-07-01, 2006-09-16
700, 2006-07-15, 000-09531-10184, 1.16, 1.88, 5575, 10481.00, 1.68, 1.16, 6847, 11223.91, 2006-07-01, 2006-09-16
700, 2006-07-08, 000-09531-10184, 1.17, 1.74, 7095, 12345.30, 1.68, 1.16, 6847, 11223.91, 2006-07-01, 2006-09-16
700, 2006-07-01,000-09531-10184, 1.17, 1.49, 8633, 12863.17, 1.68, 1.16, 6847, 11223.91, 2006-07-01, 2006-09-16

Please note that in this example the AvgPrice is $1.68, AvgCost is $1.16, AvgUnits is 6847, and AvgDollarSales is 1123.91.

Also note that in this 12 week (12 saledate) breakdown the StartDuration is 2006-07-01 and, EndDuration is 2006-09-16.

STEP 2:

I want to add the following 6 more columns for Step 2.
PriceDecrease% (decimal),UnitIncrease% (decimal) , DollarSalesIncrease% (decimal), PriceDecreaseFlag (int), UnitIncreaseFlag (int), and DollarSaleIncreaseFlag (int).
The purpose of these first columns is to calculate how much decrease is there in price. And how much increase there is in Units and DollarSales. This may tell if the product is on promotion or not.

The purpose of the remaining three columns PriceDecreaseFlag, UnitIncreaseFlag, and DollarSaleIncreaseFlag is to set a flag if there is a decrease in Price, increase in Units, or increase in DollarSales respectively..

The formulas are:
PriceDecrease% = ((AvgPrice-Price)/AvgPrice))*100
UnitInCrease% = ((Units-AvgUnits)/Units)*100
DollarSalesInCrease% = ((DollarSales-AvgDollarSales)/DollarSales)*100

Once these formulas are applied I want to set the following.

a) Set PriceDecreaseFlag to 1, if the [PriceDecrease%] >=2. Else Set PriceDecreaseFlag to 0
b) Set UnitIncreaseFlag to 1, if the [UnitInCrease%] >0. Else Set UnitIncreaseFlag to 0
c) Set DollarSaleIncreaseFlag to 1, if the [DollarSalesInCrease%] >0. Else Set DollarSaleIncreaseFlag to 0.

So in this example the end result should look be as follows (each column is separated my comma):
(Please note that rows might not be aligned but remember here each row starts with 700 which is the ZoneID in this example)

ZoneID (nvarchar type), SaleDate (Datetime type),ProductID (nvarchar type), Cost (decimal type), Price (decimal type), Units (bigInt type), DollarSales (decimal type), AvgPrice (decimal), AvgCost (decimal), AvgUnits (Big Int), AvgDollarSales (decimal), StartDuration (datetime), EndDuration (datetime), PriceDecrease% (decimal), UnitInCrease% (decimal), DollarSalesInCrease% (decimal), PriceDecreaseFlag (int), UnitIncreaseFlag (int), and DollarSaleIncreaseFlag (int).

700,2006-09-16, 000-09531-10184, 1.16,1.50,7514,11271.00,1.68,1.16,6847,11223.91,2006-07-01,2006-09-16,10.71,8.88,0.42,1,1,1
700,2006-09-09, 000-09531-10184, 1.17,1.49,9435,14058.15,1.68,1.16,6847,11223.91,2006-07-01,2006-09-16,11.31,27.43,20.16,1,1,1
700,2006-09-02, 000-09531-10184, 1.17,1.49,8631,12860.19,1.68,1.16,6847,11223.91,2006-07-01,2006-09-16,11.31,20.67,12.72,1,1,1
700,2006-08-26, 000-09531-10184, 1.16,1.50,7310,10965.00,1.68,1.16,6847,11223.91,2006-07-01,2006-09-1610.71,6.33,-2.36,1,1,0
700,2006-08-19, 000-09531-10184, 1.16,1.50,7207,10810.50,1.68,1.16,6847,11223.91,2006-07-01,2006-09-1610.71,5.00,-3.82,1,1,0
700,2006-08-12, 000-09531-10184, 1.16,1.88,4500,8460.00,1.68,1.16 ,6847,11223.91,2006-07-01,2006-09-16,-11.90,-52.16,-32.67,0,0,0
700,2006-08-05, 000-09531-10184, 1.16,1.88,5725,10763.00,1.68,1.16,6847,11223.91,2006-07-01,2006-09-16,11.90,-19.60,-4.28,0,0,0
700,2006-07-29, 000-09531-10184, 1.16,1.88,5240,9851.20,1.68,1.16 ,6847,11223.91,2006-07-01,2006-09-16,-11.90,-30.67,-13.93,0,0,0
700,2006-07-22, 000-09531-10184, 1.16,1.88,5297,9958.36,1.68,1.16,6847,11223.91,2006-07-01,2006-09-16-11.90,-29.26,-12.71,0,0,0
700,2006-07-15, 000-09531-10184, 1.16,1.88,5575,10481.00,1.68,1.16,6847,11223.91,2006-07-01,2006-09-16,-11.90,-22.82,-7.09,0,0,0
700,2006-07-08, 000-09531-10184, 1.17,1.74,7095,12345.30,1.68,1.16,6847,11223.91,2006-07-01,2006-09-16,-3.57,3.50,9.08,0,1,1
700,2006-07-01, 000-09531-10184, 1.17,1.49,8633,12863.17,1.68,1.16,6847,11223.91,2006-07-01,2006-09-16,11.31,20.69,12.74,1,1,1

This is not the end of the problem but at this point this is what I am looking for.

CAN SOMEONE PLEASE HELP? I am looking for a quick response.
Thanks a million in advance.

Zee

Kristen
Test

22859 Posts

Posted - 2007-09-14 : 22:24:20

Note sure what your problem is how to write some SQL, and you've gone to a lot of trouble to describe the data and scenario.

STEP1 is just

INSERT INTO SalesHistorySummary ( ... columns ...)
SELECT Average (Price), Average (Cost), Average (Units), Average (DollarSales)
FROM SalesHistory
...

STEP2 is similar. Although I'm not sure I agree with comparing +/1 2% against average. I would reckon the price is on promotion if its price drops suddenly ... but then I'm not in the retail grocery trade!

More specific question about what you are stuck on would help pls.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources