Emergency 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)

Emergency Query!! :(

Author

Topic

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-04-15 : 09:32:13

Hi all - had a bit of a nightmare at work with order accounts application. Basically - all the orders have been set to uninvoiced and I need to set the ones which have been invoiced back to invoiced. Problem is that orders can be part invoiced.

Tables as follows:

orders
orderid PK int
invoiced int

orderline
orderid int
qty int
productid int

invoices
invoiceid PK int
orderid int

invoiceline
invoiceid int
qty int
productid int

There can be multiple invoices for each order

Question is - how can I write a query which updates the order table - set invoiced to 1 where there are the same amount of items in the order as in the related invoices?

Real emergency this one folks!

Hopefully someone can help me out!

Stephen.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-15 : 09:37:22

does orderline relate to orders and invoiceline relate to invoices? think you should be a little clearer in your example first

Em

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-04-15 : 09:38:52

Sorry - edited for clarity.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-15 : 09:46:43

obviously not tested but something like...


select *
from 
(
select orderid, sum(qty) as orderQty
from orders o join orderline ol on o.orderid = ol.orderid
group by orderid
) orders join
(
select orderid,sum(qty) as invoiceQty
from invoice i join invoiceline il on i.invoiceid = il.invoiceid
group by orderid
) invoices on orders.orderid = invoices.orderid
where orderqty = invoiceqty

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-04-15 : 09:54:06

Thanks for the prompt reply! That does not link on the productid though does it?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-15 : 09:55:35

no, if you just want to check the quantities on the order and the invoice you wouldn't need it eh?

Em

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-04-15 : 09:56:57

But there's more than 1 product to an order?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-15 : 09:59:37

so? if it's just grouped on orderid and totalling the quantity, what difference does it make about the constituent products?

perhaps you should post some sample data for us to test with

Em

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-04-15 : 10:21:50

Of course you're right. Emergency averted - a million thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources