help with complex query - SQL Server Forums

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

SQL Server Development (2000)

help with complex query

Author

Topic

jbezanson
Starting Member

35 Posts

Posted - 2006-12-04 : 13:15:49

First off, I cannot change the structure of the tables or their fields.

I have the following 4 tables and the below is the current query I am working with

tvwr_ContractInvoices
---------------------------
InvoiceNumber INT
ContractNumber INT

tblContractDetail
---------------------------
ContractNumber INT
ItemID VARCHAR
YearlyPrice    FLOAT

tblPriceLevels
---------------------------
PriceLevelsKeyID INT
Description VARCHAR

tblPriceLevelOverride
---------------------------
PriceLevelOverrideKeyID INT
FkPriceLevels INT
FlatPrice FLOAT

SELECT
InvoiceNumber,
c.ContractNumber,
c.ItemID,
YearlyPrice,
FkPriceLevels,
Description,
FlatPrice
FROM tvwr_ContractInvoices i, tblContractDetail c, tblPriceLevelOverride po, tblPriceLevels p
WHERE i.ContractNumber = c.ContractNumber AND c.ItemID = po.ItemID AND po.FkPriceLevels = p.PriceLevelsKeyID AND i.InvoiceNumber = 31
GROUP BY
InvoiceNumber,
c.ContractNumber,
c.ItemID,
YearlyPrice,
FkPriceLevels,
Description,
FlatPrice

With a query like that I get results like:


Inv#  Con#  ItemID  Yr Price  Price Level  PL Desc         Flat Price
31    51     Item1  249.99    18           Price Level 18  219.99
31    51     Item1  249.99    19           Price Level 19  199.99
31    51     Item1  249.99    20           Price Level 20  189.99
31    51     Item1  249.99    21           Price Level 21  159.99

But what I want is something like:


Inv#  Con#  ItemID  Yr Price  PL 18 Price  PL 19 Price  PL 20 Price  PL 21 Price
31    51     Item1  249.99    219.99       199.99       189.99       159.99

Is this possible within the limitations I have to work? I am able to create views but I cannot modify any existing tables or views.

Thanks

Justin Bezanson
www.aspnetguy.com

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-04 : 13:29:29

This might be helpful

http://weblogs.sqlteam.com/brettk/archive/2005/02/23/4171.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jbezanson
Starting Member

35 Posts

Posted - 2006-12-04 : 13:36:42

Thanks Brett that looks just like what I need :)

Justin Bezanson
www.aspnetguy.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources