I have a query that uses a union to get the results that I need, but I am having problems understanding how I need to change my query to be able to format the same results in a more readable manner. I am ending with a larger number of columns that I actually need.
The query is simple, I think the complexity comes in how it needs to be structured. May be I need to use CASE statements? I am not sure but any help would be appreciated.
, sl.[Document No_] [SalesOrderNumber]
, sl.[Location Code] [LocationCode]
, sl.[Shipment Date] [ShipmentDate]
, sl.[Sell-to Customer No_] [CustomerNumber]
, sl.Quantity [OrderQty]
, cus.Name [CustomerName]
, 0 [FirmedPlannedQty]
, '' [FirmedPlannedDueDate]
, '' [FirmedPlannedNumber]
, '' [FirmedPlannedLocation]
dbo.[CQC$Sales Line] sl INNER JOIN
dbo.[CQC$Customer] cus ON cus.No_ = sl.[Sell-to Customer No_]
sl.No_ = '10568'
sl.[Document Type] IN ('1')
sl.Quantity = sl.[Outstanding Quantity]
, '' [Description]
, '' [SalesOrderNumber]
, '' [LocationCode]
, '' [ShipmentDate]
, '' [CustomerNumber]
, 0 [OrderQty]
, '' [CustomerName]
, pol.Quantity [FirmedPlannedQty]
, pol.[Due Date] [FirmedPlannedDueDate]
, pol.[Prod_ Order No_] [FirmedPlannedNumber]
, pol.[Location Code] [FirmedPlannedLocation]
dbo.[CQC$Prod_ Order Line] pol
[Item No_] = '10568'
Status = 2
[Variant Code] <> 'R'
, FirmedPlannedDueDate asc
how I need it to be (mock up with excel):
Thank you very much for your help.
If more details need to be added, please let me know.