Help with a query involving 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 involving 2 tables.

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2006-10-12 : 12:31:51

All,

I have a table called ProdsWith2Prices with following fields.
StartDate(Datetime),RevertDate(Datetime),ZoneID(nvarchar),ProductID(nvarchar),OutletID(nvarchar).
ProdsWith2Prices holds records of some speicfic products which has 2 promotional prices per zone per outlet in each date range (StartDate and RevertDate).
(This table doesnt contain that Promotional Price. Infact none of the table holds Promotional Price)

I have another table called Table2 with following fields.
ZoneID(nvarchar),OutletID,ProductID(nvarchar),DollarSales(float),Date(datetime)

Table2 holds DollarSales values of each product per zone per outlet per Date.

No I want to get the DollarSalesValues of each product per zone per outlet from Table2, but for a date range that exist in ProdsWith2Prices table for same product.

In my resultset I am looking for following:

StartDate,RevertDate,ZoneID,OutletID,ProductID,DollarSales

(Remember I need to have only 1 record for each product per zone per outlet per date range (StartDate & RevertDate)

Any quick help would be appricated.

Thanks...

Z

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 12:58:33

Any sample data and any output based on that sample data would be really helpful.
Also, for which date do you want the dollarvalue for in the range?

Peter Larsson
Helsingborg, Sweden

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2006-10-12 : 13:12:27

Ok.

here is the sample data.

ProdsWith2Prices
StartDate,RevertDate,ZoneID,ProductID,OutletID
01-01-2006,01-30-2006,Zone1,Prod1,Outlet1
01-01-2006,01-30-2006,Zone2,Prod1,Outlet2

ProdsWith2Prices
ZoneID,OutletID,ProductID,DollarSales,Date
Zone1,Outlet1,Prod1,1000,01-01-2006
Zone1,Outlet1,Prod1,1500,01-08-2006
Zone1,Outlet1,Prod1,1000,01-15-2006

Zone2,Outlet1,Prod1,2000,01-08-2006
Zone2,Outlet1,Prod1,2500,01-15-2006
Zone2,Outlet1,Prod1,2000,01-22-2006

Now My query should have 2 records.
Results:
StartDate,RevertDate,ZoneID,OutletID,ProductID,DollarSales
01-01-2006,01-30-2006,Zone1,Outlet1,Prod1,3500
01-01-2006,01-30-2006,Zone2,Outlet2,Prod1,6500

Note DollarSales is sum up for same records based on the date range.

Any help would be appreciated.

Thanks....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-12 : 13:18:28

[code]
select p1.StartDate, p1.RevertDate, p1.ZoneID, p1.OutletID, p1.ProductID, sum(p2.DollarSense)
from ProdsWith2Prices p1 inner join ProdsWith2Prices2 p2
on p1.ZoneID = p2.ZoneID
and p1.OutletID = p2.OutletID
and p1.ProductID = p2.ProductID
and p2.Date >= p1.Startdate
and p2.Date <= p1.RevertDate
group by p1.StartDate, p1.RevertDate, p1.ZoneID, p1.OutletID, p1.ProductID
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 13:19:04

[code]SELECT pwp.StartDate,
pwp.RevertDate,
pwp.ZoneID,
pwp.OutletID,
pwp.ProductID,
SUM(CASE WHEN t2.Date BETWEEN pwp.StartDate AND pwp.RevertDate THEN t2.DollarSales ELSE 0 END) DollarSales
FROM ProdsWith2Prices pwp
INNER JOIN Table2 t2 ON t2.ZoneID = pwp.ZoneID AND t2.OutletID = pwp.OutletID AND t2.ProductID = pwp.ProductID
GROUP BY pwp.StartDate,
pwp.RevertDate,
pwp.ZoneID,
pwp.OutletID,
pwp.ProductID[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources