Help with SELECT 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 SELECT involving 2 tables !!!

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2007-09-27 : 16:39:32

Hi All,

I have a table called #Promo with following fields and types.

StartDate (datetime)
EndDate (datetime)
ClientOutletID (nvarchar)
ClientProductID (nvarchar)
PromoPrice (decimal)
PromoCost (decimal)
PromotionType (nvarchar)

The records are like this: (each field is seperated by comma):

StartDate, EndDate, ClientOutletID, ClientProductID, PromoPrice, PromoCost, PromotionType
2005-12-10, 2005-12-17, 00402, 000-24200-04917, 2.99, 2.33,Price Promotion

I have another table called #sh with following fields & types.
Saledate (datetime)
ClientProductID (nvarchar)
ClientOutletID (nvarchar)
SaleCost (decimal)
SalePrice (decimal)

The records are like this in #sh table: (each field is seperated by comma):

Saledate,ClientProductID,ClientOutletID,SaleCost,SalePrice
2005-12-03, 000-11110-37050, 00402,1.02,2.97
2005-12-24, 000-11110-37050, 00402,1.03,2.99
2006-02-25, 000-11110-37050, 00402,.95,2.99
2006-05-13, 000-11110-37050, 00402,.95,2.99

Please bear in mind that I want to join these 2 tables based on ClientOutletID and ClientProductID (both these fields exist in each table)

In my select I want all fields from #Promo table and #sh.SalePrice and #sh.SaleCost from #sh table where the #sh.Saledate is the earliest date available but greater than #promo.EnDDate.

So in the above example my OUTPUT would be:

StartDate, EndDate, ClientOutletID, ClientProductID, PromoPrice, PromoCost, PromotionType, SaleCost, SalePrice
2005-12-10, 2005-12-17, 00402, 000-24200-04917, 2.99, 2.33,Price Promotion,1.03,2.99

Explanation for the output:
The script will check if its same ClientoutletID and same ClientProductID which in this example it is. Then it will check the earliest #promo.EnDDate which is greater than #sh.Saledate. In this example it is 2005-12-24. And then it will get the #sh.SaleCost(1.03) and #sh.SalePrice(2.99) on 2005-12-24.

If there's no earliest #promo.EnDDate which is greater than #sh.Saledate then in the SELECT SalePrice will be same as PromoPrice and SaleCost will be same as PromoCost

In any case the SELECT should return all th records from #promo table.

How can I write a SQL script to do this.

Any help would be highly appreciated.

Thanks,

Zee

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources