Solved

Tweak SQL (Thomasian?) Date greater OR the same

Posted on 2011-02-13
4
237 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 500 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

724 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