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.
| Author |
Topic |
|
Peter2138
Starting Member
2 Posts |
 Posted - 2012-08-15 : 05:28:39
|
| I have two tables, orders and orderlines, with both the ordernumber as link/relation. Further I have a main warehouse and an order status. What do I want?I want my completed orders. An order is complete when:- ALL order item lines with warehouse "SEC" have order status 30An order may have lines from another warehouse but that's not relevant. So I only want orders where ALL orderlines from warehouse SEC have the orderstatus 30.I tried this but it's not working:0rdertabel is SROORSHE: OHORNO=ordernumber, OHSROM=warehouseOrderline tabel is SROORSPL: OLORNO=ordernumber, OLORDS=orderstatusSELECT SROORSHE.OHORNOcount(distinct SROORSPL.OLORDS) as status_countmax (SROORSPL.OLORDS) as orderline_statusFROM SROORSHE, SROORSPLINNER JOIN OLORNO ON SROORSHE.OHORNO = SROORSPL.OLORNO GROUP BY SROORSHE.OHORNO, SROORSHE.OHSROMWHERE SROORSHE.OHSROM = 'SEC' AND status_count = 1 AND orderline_status = 30 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-08-15 : 05:45:13
|
| What if there are no orderlines for SEC but there are for other warehouses - this excludes themSELECT HE.OHORNOfrom SROORSHE HEjoin SROORSPL PLon HE.OHORNO = PL.OLORNO where HE.OHSROM = 'SEC'group by HE.OHORNOhaving max(PL.OLORDS) = 30 and min(PL.OLORDS) = 30==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
Should be easy or? Two tables