Solved

Tweak SQL (Thomasian?) Date greater OR the same

Posted on 2011-02-13
4
226 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
  • 2
4 Comments
 
LVL 32

Expert Comment

by:ewangoya
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Using MS SQL / SQLLite for iPhone  App 7 26
t-sql complement 8 30
combine an MS SQL string in Idera DM 9 28
Azure SQL DB? 3 15
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now