Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Tweak SQL (Thomasian?) Date greater OR the same

Posted on 2011-02-13
4
Medium Priority
?
239 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Camillia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34884903
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
 
LVL 7

Author Comment

by:Camillia
ID: 34884941
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
 
LVL 22

Accepted Solution

by:
Thomasian earned 2000 total points
ID: 34884972
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
 
LVL 7

Author Comment

by:Camillia
ID: 34884983
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question