Tweak SQL (Thomasian?) Date greater OR the same

Thomasian helped me with this sql.

I know what's causing the issue. This SQL was written based on getting the oldest date (S.RecDate > RecDate ) My manager originally said that we'll have different dates but I came across scenario below where the dates "recdate" are all the same...

 This SQL below works for all scenarios just needs a minor tweak to account for when rows have the same date. In the example below, it should bring back ONE row because onOrder = 1 and qtyNeedRestock = 1. But it brings back all 4 rows...

I think maybe "case" statement, or maybe MAX is needed where we have "S.RecDate > RecDate"  but not sure. I've posted an example and the latest SQL that Thomasian helped me with....
CREATE TABLE #Main
(
      [sku] [varchar](15),
      [qtyneedRestock] [int] ,
      [qtyFenCartNeed] [int],
      [qtyneededFen] [int] ,
      [qtyneededRud] [int] ,
      [qtyRudCardNeed] [int],
      [onOrder] [int] ,
      [Description1] [varchar](50) 
)

CREATE TABLE #SKULocation(
      [sku] [varchar](15) ,
      [loc]  [varchar](10),
      [actualLocQty] [int] ,
      [RecDate] [datetime] ,
      [mu_id] [varchar](20) 
)

insert into #main values ('025343345045',1,0,0,0,0,1,'A1')
insert into #skuLocation values('025343345045','0152030602',18,'2010-10-29 08:00:05.000',0105) 
insert into #skuLocation values('025343345045','0152030603',15,'2010-10-29 08:00:05.000',0407) 
insert into #skuLocation values('025343345045','0152030604',18,'2010-10-29 08:00:05.000',0707) 
insert into #skuLocation values('025343345045','0153060604',15,'2010-10-29 08:00:05.000',5562)

-----------------------------------------
SELECT 
     *  
FROM
 (
  SELECT M.*
         ,S.actualLocQty
         ,S.loc as Location
         ,S.mu_id
         ,(SELECT ISNULL(SUM(actualLocQty),0)
            FROM #SKULocation
            WHERE S.sku=sku AND
                  S.RecDate > RecDate --*** I think this needs to change
                  AND
                  (S.loc / 1000000 = loc / 1000000
                    OR (left(cast(loc as varchar), 1) = '0' AND                         left(cast(S.loc as varchar), 1) = '0'
                       )
                  )

          ) as RunningQty
   FROM #Main M INNER JOIN #SKULocation S ON M.sku=S.sku
) p
WHERE CASE 
	    WHEN left(cast(Location as varchar), 1) = '0'	then qtyneedRestock 	    when Location / 1000000 = 1003 then qtyFenCartNeed
        WHEN Location / 1000000 = 1001 THEN qtyneededFen 
        when Location / 1000000 = 2002 then qtyRudCardNeed 
        when Location / 1000000 = 2003 then qtyRudCardNeed         
ELSE qtyneededRud end  > RunningQty 
ORDER BY sku

Open in new window

LVL 8
CamilliaAsked:
Who is Participating?
 
ThomasianConnect With a Mentor Commented:
I have modified the query such that when the recdate is the same, it starts with the smallest mu_id.
SELECT 
     *  
FROM
 (
  SELECT M.*
         ,S.actualLocQty
         ,S.loc as Location
         ,S.mu_id
         ,(SELECT ISNULL(SUM(actualLocQty),0)
            FROM #SKULocation
            WHERE S.sku=sku AND
                  (S.RecDate > RecDate 
                  OR (S.RecDate = RecDate AND S.mu_id>mu_id)) --*** This was added
                  AND
                  (S.loc / 1000000 = loc / 1000000
                    OR (left(cast(loc as varchar), 1) = '0' AND                         left(cast(S.loc as varchar), 1) = '0'
                       )
                  )

          ) as RunningQty
   FROM #Main M INNER JOIN #SKULocation S ON M.sku=S.sku
) p
WHERE CASE 
	    WHEN left(cast(Location as varchar), 1) = '0'	then qtyneedRestock 	    when Location / 1000000 = 1003 then qtyFenCartNeed
        WHEN Location / 1000000 = 1001 THEN qtyneededFen 
        when Location / 1000000 = 2002 then qtyRudCardNeed 
        when Location / 1000000 = 2003 then qtyRudCardNeed         
ELSE qtyneededRud end  > RunningQty 
ORDER BY sku

Open in new window

0
 
Ephraim WangoyaCommented:
try
SELECT 
     *  
FROM
 (
  SELECT M.*
         ,S.actualLocQty
         ,S.loc as Location
         ,S.mu_id
         ,(SELECT ISNULL(SUM(actualLocQty),0)
            FROM #SKULocation
            WHERE S.sku=sku 
            --AND   S.RecDate = RecDate --*** I think this needs to change
            AND   (S.loc / 1000000 = loc / 1000000 OR (left(cast(loc as varchar), 1) = '0' AND left(cast(S.loc as varchar), 1) = '0'))
            GROUP BY sku, RecDate
            HAVING S.RecDate = MAX(RecDate)
          ) as RunningQty
   FROM #Main M INNER JOIN #SKULocation S ON M.sku=S.sku
) p
WHERE CASE 
	    WHEN left(cast(Location as varchar), 1) = '0'	then qtyneedRestock 	    when Location / 1000000 = 1003 then qtyFenCartNeed
        WHEN Location / 1000000 = 1001 THEN qtyneededFen 
        when Location / 1000000 = 2002 then qtyRudCardNeed 
        when Location / 1000000 = 2003 then qtyRudCardNeed         
ELSE qtyneededRud end  > RunningQty 
ORDER BY sku

Open in new window

0
 
CamilliaAuthor Commented:
No, that gets no rows. I'll see if Thomasian can answer this. This is a long long sql thread that he's been helping me with.
0
 
CamilliaAuthor Commented:
you're the best. I didnt even think about the mu_id and that could be used. I'll close this and test it If I have any questions, i will open a new question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.