July 9, 2020 at 3:52 pm
Sorry if the title is unclear.
The following query is used in an Excel pivot table.
It is an union of Cie1 and Cie2 companies. The main criteria is to show the back orders (b/o)
I would like that as soon that either one has a b/o, both Cie1 and Cie2 will show,
Currently, only the one with b/o with show and the other side will be null (nothing).
What changes should I do to this query? TIA.
With CTE AS
(SELECT 'Cie1' as Cie, H.SalesOrder, H.OrderStatus, RTRIM(H.Customer) AS Customer1,
RTRIM(D.SalesOrderLine) AS SalesOrderLine1, RTRIM(D.MStockCode) AS StockCode,
RTRIM(D.MStockDes) AS MStockDes,
D.MShipQty + D.QtyReservedShip + D.MBackOrderQty AS Outs,
D.MStockingUom, D.MWarehouse, D.LineType,
RTRIM(I.CountryOfOrigin) AS CountryOfOrigin1, I.WarehouseToUse, D.MLineShipDate,
I.LeadTime, H.CancelledFlag, H.ActiveFlag, H.InterWhSale,
D.MOrderQty, D.MShipQty, D.QtyReservedShip,
D.MBackOrderQty, H.OrderDate, H.Salesperson,
D.MProductClass, W.QtyOnHand, W.QtyOnOrder,
W.QtyAllocated AS QtyAllocatedSales, W.QtyAllocatedWip
FROM Cie1.dbo.SorDetail D INNER JOIN
Cie1.dbo.SorMaster H ON D.SalesOrder = H.SalesOrder LEFT OUTER JOIN
Cie1.dbo.InvWarehouse W ON D.MWarehouse = W.Warehouse AND
D.MStockCode = W.StockCode LEFT OUTER JOIN
Cie1.dbo.InvMaster I ON D.MStockCode = I.StockCode LEFT OUTER JOIN
MuDb.dbo.tblWhCtrl_K C ON D.MWarehouse = C.Warehouse AND D.MStockCode = C.Stockcode
WHERE (H.CancelledFlag <> 'Y') AND (H.ActiveFlag <> 'N') AND (H.InterWhSale <> 'Y') AND
(D.MShipQty + D.QtyReservedShip + D.MBackOrderQty > 0)
UNION
SELECT 'Cie2' as Cie, H.SalesOrder, H.OrderStatus, RTRIM(H.Customer) AS Customer1,
RTRIM(D.SalesOrderLine) AS SalesOrderLine1, RTRIM(D.MStockCode) AS StockCode,
RTRIM(D.MStockDes) AS MStockDes,
D.MShipQty + D.QtyReservedShip + D.MBackOrderQty AS Outs,
D.MStockingUom, D.MWarehouse, D.LineType,
RTRIM(I.CountryOfOrigin) AS CountryOfOrigin1, I.WarehouseToUse, D.MLineShipDate,
I.LeadTime, H.CancelledFlag, H.ActiveFlag, H.InterWhSale,
D.MOrderQty, D.MShipQty, D.QtyReservedShip,
D.MBackOrderQty, H.OrderDate, H.Salesperson,
D.MProductClass, W.QtyOnHand, W.QtyOnOrder,
W.QtyAllocated AS QtyAllocatedSales, W.QtyAllocatedWip
FROM Cie2.dbo.SorDetail D INNER JOIN
Cie2.dbo.SorMaster H ON D.SalesOrder = H.SalesOrder LEFT OUTER JOIN
Cie2.dbo.InvWarehouse W ON D.MWarehouse = W.Warehouse AND
D.MStockCode = W.StockCode LEFT OUTER JOIN
Cie2.dbo.InvMaster I ON D.MStockCode = I.StockCode LEFT OUTER JOIN
MuDb.dbo.tblWhCtrl_N C ON D.MWarehouse = C.Warehouse AND D.MStockCode = C.Stockcode
WHERE (H.CancelledFlag <> 'Y') AND (H.ActiveFlag <> 'N') AND (H.InterWhSale <> 'Y') AND
(D.MShipQty + D.QtyReservedShip + D.MBackOrderQty > 0)
)
Select CTE.* FROM CTE ORDER BY CTE.MLineShipDate
July 9, 2020 at 4:52 pm
UNION does an automatic DISTINCT. Try using UNION ALL instead. If that doesn't answer your question, you'll need to provide sample data and expected results. The data should be provided as a script to create one or more temporary tables and insert data into said tables.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 9, 2020 at 5:37 pm
UNION ALL didn't achieve what I am looking for. Please see attachment. There are two warehouses, S0 and LU. On the first line, we can see that activity was on S0 but I'd like to see also LU (which has no activity). I understand that some of the left columns will need to move in the values.
July 9, 2020 at 5:37 pm
EDIT - I misunderstood the request. My response was wrong. Deleted.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 10, 2020 at 7:19 am
Could you please provide more details as it is a bit unclear what do you mean. Please provide some data examples, what you are getting wrong and what exactly you want to achieve.
July 10, 2020 at 3:19 pm
UNION ALL didn't achieve what I am looking for. Please see attachment. There are two warehouses, S0 and LU. On the first line, we can see that activity was on S0 but I'd like to see also LU (which has no activity). I understand that some of the left columns will need to move in the values.
I specifically said that you would need to provide a SCRIPT. A picture is not a script. We want something that we can just cut and paste into SSMS and then execute. You can't do that with a picture.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 10, 2020 at 11:13 pm
I detailed the query above. I have shown the Excel pivot table. I know what is a script, but I don't understand what is your request here.
July 11, 2020 at 2:30 am
CREATE TABLE and INSERT scripts, so people can run your query against something.... Otherwise, you won't get a tested answer. You like answers that are actually tested, right?
July 13, 2020 at 12:11 pm
The UNION part of this really shouldn't make a difference as the results from the two queries are differentiated by the 'Cie1' and 'Cie2' fixed column values.
This means that whatever is happening that is not giving you the expected results is happening at a lower level and in the queries themselves. Typically investigating such a problem is just a case of drilling into the queries block by block to see where the problem is. Have you tried executing just the 'Cie1' query on it's own? (I'd suggest using SQL Server Management Studio or similar to run these queries). Does this query return any of the back order data that you expect? If not, then you need to drill into them to see why, the quickest method for this usually involves commenting out individual WHERE or JOIN statements.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply