Link to home
Start Free TrialLog in
Avatar of Camillia
CamilliaFlag for United States of America

asked on

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

Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

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

Avatar of Camillia

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Thomasian
Thomasian
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.