troubleshooting Question

Need help with First Row in Results Set of a SQL Server Query

Avatar of Mike Orther
Mike OrtherFlag for United States of America asked on
Microsoft SQL ServerSQL
17 Comments1 Solution59 ViewsLast Modified:
If the AvailBinQty is >= PickQty then show the first row for the "Item No" "Lot Number" combination.
Else include the 1st and second line
and so on until it finds a "Item No" "Lot Number" combination where AvailBinQty is >= PickQty

SELECT 
  TOP (100) PERCENT TRIM(
    dbo.PK010033.MANUFACTUREORDER_I
  ) AS [MO No], 
  TRIM(dbo.PK010033.ITEMNMBR) AS [Item No], 
  TRIM(dbo.IV00101.ITEMDESC) AS [Desc], 
  CONVERT(
    varchar, dbo.PK010033.REQDATE, 101
  ) AS Required, 
  CAST(
    dbo.PK010033.QTY_ALLOWED_I AS INT
  ) AS PickQty, 
  SUM(
    CAST(dbo.PK010033.QTY_ISSUED_I AS INT)
  ) AS Issued, 
  CAST(
    dbo.PK010033.QTY_ALLOWED_I - dbo.PK010033.QTY_ISSUED_I AS INT
  ) AS [Remaining Qty], 
  TRIM(dbo.IV00300.LOTNUMBR) AS [Lot Number], 
  TRIM(dbo.IV00300.BIN) AS BIN, 
  SUM(
    CAST(
      dbo.IV00300.QTYRECVD - dbo.IV00300.QTYSOLD AS INT
    )
  ) AS AvailBinQty, 
  CONVERT(
    varchar, dbo.IV00300.EXPNDATE, 101
  ) AS ExpireDate, 
  TRIM(dbo.PK010033.UOFM) AS PK_UoM, 
  TRIM(dbo.PK010033.LOCNCODE) AS Site, 
  TRIM(dbo.PK010033.RTSEQNUM_I) AS ROUTE_SEQ, 
  dbo.PK010033.BOMUSERDEF1_I AS [Offset Require Date], 
  dbo.PK010033.BOMUSERDEF2_I AS [Special Instructions], 
  CONVERT(
    varchar, dbo.IV00300.EXPNDATE, 12
  ) AS [Expire No] 
FROM 
  dbo.IV00101 
  RIGHT OUTER JOIN dbo.PK010033 ON dbo.IV00101.ITEMNMBR = dbo.PK010033.ITEMNMBR 
  LEFT OUTER JOIN dbo.IV00112 
  INNER JOIN dbo.IV00300 ON dbo.IV00112.ITEMNMBR = dbo.IV00300.ITEMNMBR 
  AND dbo.IV00112.BIN = dbo.IV00300.BIN ON dbo.PK010033.ITEMNMBR = dbo.IV00300.ITEMNMBR 
  AND dbo.PK010033.LOCNCODE = dbo.IV00300.LOCNCODE 
GROUP BY 
  TRIM(
    dbo.PK010033.MANUFACTUREORDER_I
  ), 
  TRIM(dbo.PK010033.ITEMNMBR), 
  TRIM(dbo.PK010033.UOFM), 
  TRIM(dbo.PK010033.RTSEQNUM_I), 
  TRIM(dbo.PK010033.LOCNCODE), 
  CONVERT(
    varchar, dbo.PK010033.REQDATE, 101
  ), 
  dbo.PK010033.BOMUSERDEF1_I, 
  dbo.PK010033.BOMUSERDEF2_I, 
  dbo.PK010033.MANUFACTUREORDERST_I, 
  TRIM(dbo.IV00101.ITEMDESC), 
  CONVERT(
    varchar, dbo.IV00300.EXPNDATE, 12
  ), 
  CONVERT(
    varchar, dbo.IV00300.EXPNDATE, 101
  ), 
  TRIM(dbo.IV00300.BIN), 
  TRIM(dbo.IV00300.LOTNUMBR), 
  CAST(
    dbo.PK010033.QTY_ALLOWED_I AS INT
  ), 
  CAST(
    dbo.PK010033.QTY_ALLOWED_I - dbo.PK010033.QTY_ISSUED_I AS INT
  ) 
HAVING 
  (
    dbo.PK010033.MANUFACTUREORDERST_I = 3
  ) 
  AND (
    TRIM(dbo.PK010033.LOCNCODE) = 'EF-R'
  ) 
  AND (
    CAST(
      dbo.PK010033.QTY_ALLOWED_I - dbo.PK010033.QTY_ISSUED_I AS INT
    ) > 0
  ) 
  AND (
    SUM(
      CAST(
        dbo.IV00300.QTYRECVD - dbo.IV00300.QTYSOLD AS INT
      )
    ) > 0
  ) 
  AND (
    CONVERT(
      varchar, dbo.IV00300.EXPNDATE, 101
    ) >= GETDATE()
  ) 
ORDER BY 
  [MO No], 
  [Item No], 
  [Expire No]


Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 17 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros