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 |
|
mauddib
Starting Member
14 Posts |
 Posted - 2005-09-06 : 09:07:32
|
| BElow is a query I designed in Enterprise Manager. Basaically it gets all sales ordres from our system by looking at current orders (After the union) and reconstruction old orders not on the system by adding up all the Shipments which ARE still on the system (before the UNION).My problem before and after the Union is when I select the MAX date that a shipment (if any) was done on an order. That is the last shipment made....If there are 2 items on an order and one of them is shipped, my Query will take the last shipment date for both items. Therefore I want to change the below query to show me the MAX date on the shipment where the QUANTITY field in SALES SHIPMENT LINE is bigger than Zero. How do I make this change both before and after the query:::SELECT TOP 100 PERCENT dbo.[ZCLD$Sales Shipment Line].No_, dbo.[ZCLD$Sales Shipment Line].Description, dbo.[ZCLD$Sales Shipment Line].[Sell-to Customer No_] AS Customer, dbo.[ZCLD$Sales Shipment Header].[Order No_], MAX(dbo.[ZCLD$Sales Shipment Line].[Requested Delivery Date]) AS [Requested Date], MAX(dbo.[ZCLD$Sales Shipment Line].[Shipment Date]) AS [Ship Date], MAX(dbo.[ZCLD$Sales Shipment Line].[Unit Price]) AS Price, SUM(dbo.[ZCLD$Sales Shipment Line].Quantity) AS Quantity, 0 AS [QTY Remaining], 0 AS [Value], MAX(dbo.[ZCLD$Sales Shipment Header].[Posting Date]) AS [Last Shipment]FROM dbo.[ZCLD$Sales Shipment Header] INNER JOIN dbo.[ZCLD$Sales Shipment Line] ON dbo.[ZCLD$Sales Shipment Header].No_ = dbo.[ZCLD$Sales Shipment Line].[Document No_] LEFT OUTER JOIN dbo.[ZCLD$Sales Header] ON dbo.[ZCLD$Sales Shipment Header].[Order No_] = dbo.[ZCLD$Sales Header].No_WHERE (dbo.[ZCLD$Sales Header].No_ IS NULL)GROUP BY dbo.[ZCLD$Sales Shipment Line].No_, dbo.[ZCLD$Sales Shipment Line].Description, dbo.[ZCLD$Sales Shipment Line].[Sell-to Customer No_], dbo.[ZCLD$Sales Shipment Header].[Order No_]HAVING (dbo.[ZCLD$Sales Shipment Line].No_ <> '')UNIONSELECT dbo.[ZCLD$Sales Line].No_ AS Item, dbo.[ZCLD$Sales Line].Description AS Description, dbo.[ZCLD$Sales Header].[Sell-to Customer No_] AS Customer, dbo.[ZCLD$Sales Header].No_ AS [Sales Order], dbo.[ZCLD$Sales Line].[Requested Delivery Date] AS [Requested Date], dbo.[ZCLD$Sales Line].[Shipment Date] AS [Ship Date], dbo.[ZCLD$Sales Line].[Unit Price] AS Price, dbo.[ZCLD$Sales Line].Quantity AS Quantity, dbo.[ZCLD$Sales Line].[Qty_ to Ship] AS [QTY Remaining], dbo.[ZCLD$Sales Line].[Qty_ to Ship] * dbo.[ZCLD$Sales Line].[Unit Price] AS [Value], MAX(dbo.[ZCLD$Sales Shipment Header].[Posting Date]) AS [Last Shipment]FROM dbo.[ZCLD$Sales Shipment Line] INNER JOIN dbo.[ZCLD$Sales Shipment Header] ON dbo.[ZCLD$Sales Shipment Line].[Document No_] = dbo.[ZCLD$Sales Shipment Header].No_ RIGHT OUTER JOIN dbo.[ZCLD$Sales Header] INNER JOIN dbo.[ZCLD$Sales Line] ON dbo.[ZCLD$Sales Header].No_ = dbo.[ZCLD$Sales Line].[Document No_] ON dbo.[ZCLD$Sales Shipment Header].[Order No_] = dbo.[ZCLD$Sales Line].[Document No_]GROUP BY dbo.[ZCLD$Sales Line].No_, dbo.[ZCLD$Sales Line].Description, dbo.[ZCLD$Sales Header].[Sell-to Customer No_], dbo.[ZCLD$Sales Header].No_, dbo.[ZCLD$Sales Line].[Requested Delivery Date], dbo.[ZCLD$Sales Line].[Shipment Date], dbo.[ZCLD$Sales Line].[Unit Price], dbo.[ZCLD$Sales Line].Quantity, dbo.[ZCLD$Sales Line].[Qty_ to Ship], dbo.[ZCLD$Sales Line].[Qty_ to Ship] * dbo.[ZCLD$Sales Line].[Unit Price]HAVING (dbo.[ZCLD$Sales Header].[Sell-to Customer No_] LIKE 'E%') AND (dbo.[ZCLD$Sales Header].No_ LIKE 'SO%') OR (dbo.[ZCLD$Sales Header].[Sell-to Customer No_] LIKE 'U%') |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-09-06 : 13:26:33
|
| How about some DDL, some sample data, and expected results ... you haven't gotten much help yet becusae it is quite difficult to parse your SQL and try to reverse engineer your schema and data and what you are trying to accomplish. Help us help you. You might even find that once you take a small set of sample data and work on stating the problem very clearly that the answer might even jump right out at you before you even get a chance to post the question! |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-09-07 : 00:33:34
|
Without unscrambling that  (do what the Dr. said), you could try taking out all the aggregates and UNION that. Wrap the result into another query (make the UNION a derived table) and aggregate, sort, etc on that. This is just a guess and might be an extremely inefficient way to solve your issue. If you can format the code and put [ c o d e ] and [ / c o d e ] without the spaces around it, and include statements to create some tables and sample data, we could help you a lot easier.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Filter on Union Query