question

InigoMontoya-1790 avatar image
0 Votes"
InigoMontoya-1790 asked NaomiNNN answered

Help With SQL Query

@JingyangLi - helped me with almost the same issue, but we found an issue with the query where if an order has not shipped then that order is excluded from the query results. So I post updated DDL to illustrate this, and I should have the two rows for Greentree in the query results bc they meet the Valid requirement, but they are not in the query. How do I change this query so that ship date if exists, is returned?

  Create Table orderInfo
  (
      id int
      ,companyname varchar(250)
      ,orderdate datetime2
      ,ordernumber varchar(100)
      ,ponumber varchar(250)
  )
        
  Insert into orderinfo Values
  (15703, 'Homephotons', '2021-03-25 15:13:38.5900000', 'HP-10061', NULL),
  (18321, 'Greentree', '2021-03-25 15:18:38.590000', 'HP-20001', NULL)
        
  Create Table lineInfo
  (
      id int
      ,orderid int
      ,linenumber int
      ,quantity int
      ,unitprice decimal(16,4)
      ,deliverymethod varchar(250)
      ,accountmanagercode varchar(100)
      ,rc decimal(16,4)
  )
        
  Insert Into lineInfo Values
  (108543, 15703, 1, 1, '0.00', 'FedEx', 'AC203', '12.23')
  ,(108544, 15703, 2, 19, '2.95', 'FedEx', 'AC203', '0.00')
  ,(108545, 15703, 3, 150, '2.22', 'FedEx', 'AC203', '0.00')
  ,(108546, 15703, 4, 500, '0.44', 'FedEx', 'AC203', '0.00')
  ,(108547, 15703, 5, 500, '0.78', 'FedEx', 'AC203', '0.00')
  ,(110321, 18321, 2, 200, '0.10', 'FedEx', 'AC111', '14.00')
  ,(110323, 18321, 14, 201, '1.00', 'FedEx', 'AC111', '0.00')
        
  Create Table linePart 
  (
      lineid int
      ,vendorname varchar(500)    
      ,sku varchar(500)
      ,name varchar(500)
  )
        
  Insert Into linePart Values
  (108543, NULL, 'SKU_1', 'Name_1')
  ,(108544, NULL, 'SKU_2', 'Name_2')
  ,(108545, 'Valid', 'SKU_3', 'Name_3')
  ,(108546, NULL, 'SKU_4', 'Name_4')
  ,(108547, 'Valid', 'SKU_5', 'Name_5')
  ,(110321, 'Valid', 'SKU_6', 'Name_6')
  ,(110323, 'Valid', 'SKU_7', 'Name_7')
        
        
  Create Table shipInfo
  (
      id int
      ,ordersId int
      ,shipdate datetime2
  )
        
  Insert Into shipInfo Values
  (17308, 15703, '2021-06-29 13:17:20.3230000')
  ,(17383, 15703, '2021-04-23 16:29:43.9630000')
  ,(17432, 15703, '2021-04-20 09:20:21.5100000')
  ,(17458, 15703, '2021-04-15 12:55:13.5070000')
        
        
  Create Table shipLines
  (
      shipmentsId int
      ,quantityShipped int
      ,orderlineid varchar(100)
  )
        
  Insert Into shipLines Values
  (17308,150, 'HP-10061-3')
  ,(17383,1,'HP-10061-1')
  ,(17383,500,'HP-10061-5')
  ,(17432,500,'HP-10061-4')
  ,(17458,19, 'HP-10061-2')

And this is query

 Select * from (
       Select
       oi.CompanyName
       ,OrderDate = CAST(oi.OrderDate As Date)
       ,ShipDate = CAST(d.ShipDate As Date)
       ,oi.OrderNumber
       ,oi.PoNumber
       ,li.LineNumber
       ,(select top 1 VendorName from linepart lp where lp.LineId=li.id) VendorName
       ,(select top 1 Name from linepart lp where lp.LineId=li.id) Name
       ,(select top 1 Sku from linepart lp where lp.LineId=li.id) Sku
       ,li.Quantity
       ,(select top 1 QuantityShipped from shipLines sl where sl.ShipmentsId=d.id) QuantityShipped
       ,li.UnitPrice
       ,li.DeliveryMethod
       ,li.accountmanagercode
       ,li.rc
       from [orderInfo] oi
       inner join lineinfo li on li.orderid = oi.id
   cross apply (select top 1 id, CAST(si.ShipDate As Date) ShipDate from shipInfo si 
  where si.ordersId=oi.id order by si.ShipDate  /*DESC */) d
        
  ) selected
   where selected.VendorName ='Valid'
   Order By selected.OrderNumber, selected.LineNumber ASC
sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

NaomiNNN avatar image
0 Votes"
NaomiNNN answered

Instead of CROSS APPLY just use OUTER APPLY. That will work in this exact query with multiple subqueries.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.