Help with a Query using 2 tables

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 a Query using 2 tables

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2006-10-18 : 15:29:10

Hi All,

I have a Table Called SalesHistory with the following fields:

Saledate(datetime),ProductID(nvarchar),OutletID(nvarchar),Cost(decimal),Price(decimal),Units(BigInt)

This table holds saleshistory information, that is how many units of a particular product was sold by which outlet on which date for how much price & cost.

There's always only record of each product, per outlet, per date. Remeber each date is 1 week here.

Here's the sample data:

SaleDate,ProductID,OutletID,Cost,Price,Units
2005-10-03,6,4,27.00,19.25,10
2005-10-10,6,4,20.00,19.25,20
2005-10-03,6,5,37.00,19.25,2
2005-10-10,6,6,30.00,19.25,10

Now I have another table called ProductsWithMultiplePromoPrice with the following fields

StartDate(datetime),RevertDate(datetime),ZoneID(nvarchar),ProductID(nvarchar),Price(decimal),Cost(decimal),OuletID(nvarchar)

This table holds promotional price information for a particular product that was sold between a date range (startdate & revertdate) from a particular outlet in each zone.

Here, if we look at product that was sold on a particular date in a particular zone we have multiple records in each zone, because of differnet outlets that exists in each zone, plus also big date ranges which may consists of multiple weeks.

Here's the sample data:
StartDate,RevertDate,ZoneID,ProductID,Price,Cost,OuletID
2005-10-03,2005-10-29,1,6,5.69,4.76,4
2005-10-03,2005-10-29,1,6,5.69,4.76,5
2005-10-03,2005-10-29,1,6,5.69,4.76,6

Now I want to get all fields from ProductsWithMultiplePromoPrice table, and I also want to see the total units (from SalesHistory table) that was sold by each outlet between a particular date range.

For example I want to see the following:

StartDate,RevertDate,ZoneID,ProductID,Price,Cost,OuletID,TotalUnits
Here's the sample data:
StartDate,RevertDate,ZoneID,ProductID,Price,Cost,OuletID,TotalUnits
2005-10-03,2005-10-29,1,6,5.69,4.76,4,30
2005-10-03,2005-10-29,1,6,5.69,4.76,5,2
2005-10-03,2005-10-29,1,6,5.69,4.76,6,10

I would really appreciate if anyone can help me on that.

Thanks,

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-18 : 16:17:05

Since your SalesHistory table only has sales at the week level, you will only be able to get sales for date ranges that start and end on week boundries. Is that the case here?

CODO ERGO SUM

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2006-10-19 : 09:52:51

Well,

I am not following exactly what are you saying.

Look at this again.

Here's the sample data for SalesHistory table:

SaleDate,ProductID,OutletID,Cost,Price,Units
2005-10-03,6,4,27.00,19.25,10
2005-10-10,6,4,20.00,19.25,20
2005-10-03,6,5,37.00,19.25,2
2005-10-10,6,6,30.00,19.25,10

Here's the sample data for ProductsWithMultiplePromoPrice table:
StartDate,RevertDate,ZoneID,ProductID,Price,Cost,OuletID
2005-10-03,2005-10-29,1,6,5.69,4.76,4
2005-10-03,2005-10-29,1,6,5.69,4.76,5
2005-10-03,2005-10-29,1,6,5.69,4.76,6

The output in this case, should be:

StartDate,RevertDate,ZoneID,ProductID,Price,Cost,OuletID,TotalUnits
2005-10-03,2005-10-29,1,6,5.69,4.76,4,30
2005-10-03,2005-10-29,1,6,5.69,4.76,5,2
2005-10-03,2005-10-29,1,6,5.69,4.76,6,10

Look at the first row in the output:
StartDate,RevertDate,ZoneID,ProductID,Price,Cost,OuletID,TotalUnits
2005-10-03,2005-10-29,1,6,5.69,4.76,4,30

We achive a total units of 30 because date ranges between 2005-10-03 and 2005-10-29 for productID 1 from outlet 4 we have the following two records in the saleshistory table.

SaleDate,ProductID,OutletID,Cost,Price,Units
2005-10-03,6,4,27.00,19.25,10
2005-10-10,6,4,20.00,19.25,20

Thats why in the output for the first row we have this record.
StartDate,RevertDate,ZoneID,ProductID,Price,Cost,OuletID,TotalUnits
2005-10-03,2005-10-29,1,6,5.69,4.76,4,30

Now, can someone please tell me how I can achieve this in T-SQL script.

Thanks a million in advance.

Z

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2006-10-19 : 10:18:02

I have got the soultion myself.

select p.*,sum(sh.units)TotalUnits
from ProductsWithMultiplePromoPrice p
join saleshistory sh
on (p.outletid=sh.outletid and p.productid=sh.productid and sh.saledate>=p.startdate and sh.saledate<=p.revertdate)
group by p.startdate,p.revertdate,p.zoneid,p.productid,p.price,p.cost,p.outletid

This will give the following exact output I am looking for:
StartDate,RevertDate,ZoneID,ProductID,Price,Cost,OuletID,TotalUnits
2005-10-03,2005-10-29,1,6,5.69,4.76,4,30
2005-10-03,2005-10-29,1,6,5.69,4.76,5,2
2005-10-03,2005-10-29,1,6,5.69,4.76,6,10

:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources