Select 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 2005 Forums

Transact-SQL (2005)

Select Query

Author

Topic

dnagahawatte
Starting Member

24 Posts

Posted - 2009-02-24 : 19:03:32

I have three tables
Projects - (ProjectID)
Orders - (ProjectID,OrderNumber, Value)
OrderLookup - (OrderFrom,OrderTo)
Example Data as follows

Projects
---------
ProjectID
1
2

Orders
---------
ProjectID---OrderNumber---Value
1-----------POR100--------20.00
1-----------POR101--------30.00
1-----------POR102--------35.00
1-----------POR103--------40.00
1-----------POR104--------28.00
1-----------POR105--------32.00
1-----------PIR101--------30.00
1-----------PIR102--------20.00
1-----------PIR103--------15.00
1-----------PIR104--------10.00
1-----------PIR105--------05.00

OrderLookup
-----------
OrderFrom---OrderTo
POR101------PIR101
POR102------PIR102
POR102------PIR103
POR100------PIR104
POR100------PIR105

Projects - Project Details
Orders - Orders(POR numbers) and Invoices(PIR Numbers)
OrderLookup - (OrderFrom,OrderTo) Determins which Order links to which invoice. Some times their can be more than one invoice linked to one order number.

result set should be
ProjectID / OrderLookup.OrderFrom (or Orders.OrderNumber only starts with POR)/Value (for POR)/sum(Value) of relavent PIR's

example
------
1-----------POR100--------20.00----------15.00
1-----------POR101--------30.00----------30.00
1-----------POR102--------35.00----------35.00
1-----------POR103--------40.00----------NULL
1-----------POR104--------28.00----------NULL
1-----------POR105--------32.00----------NULL

How can i acheive this??.
your help greatly appreciated.
Thanks you in advance

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-02-25 : 01:02:52

Try this


;WITH CTE AS
(
SELECT O.ProjectId, F.Orderfrom, F.OrderTo,O.Value
FROM Orders O JOIN OrderLookup F
ON O.OrderNumber = F.OrderTo
WHERE O.OrderNumber LIKE 'PIR%'
)
SELECT O.ProjectId, O.OrderNumber, O.Value, SUM(C.Value)
FROM Orders O LEFT JOIN CTE C
ON O.OrderNumber = C.OrderFrom
WHERE O.OrderNumber LIKE 'POR%'
GROUP BY  O.ProjectId, O.OrderNumber, O.Value

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-25 : 01:53:00

Try this once,


SELECT os.projectid,os.ordernumber,os.val,oo.val FROM @orders os
LEFT JOIN (select OrderFrom,sum(val) AS val FROM @orderlookup ol
			INNER JOIN @orders o ON o.OrderNumber = ol.orderto
				GROUP BY OrderFrom ) oo ON oo.OrderFrom = os.Ordernumber
WHERE os.ordernumber like '%por%'

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-25 : 08:13:36

quote:

Originally posted by Nageswar9

Try this once,


SELECT os.projectid,os.ordernumber,os.val,oo.val 
FROM @orders os LEFT JOIN 
(select ol.OrderFrom,sum(o.val) AS val FROM @orderlookup ol
 INNER JOIN @orders o ON o.OrderNumber = ol.orderto
 GROUP BY ol.OrderFrom ) oo ON oo.OrderFrom = os.Ordernumber
WHERE os.ordernumber like '%por%'

dnagahawatte
Starting Member

24 Posts

Posted - 2009-02-26 : 11:05:57

Thanks you very much all of u, i got it working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources