CODEWITHSUNDEEP
sqlselectcognos-11
Carmine
Carmine

Reputation: 1

How to create a multi level select stmt on the same table with different variable

1/22/24 Part2: Shawn I review your advice it was right on. exactly what I was looking for. I did have to switch the order of the Bulk and the Pickable tables but worked GREAT. THANK YOU !

Here is my SQL

select b.iv_rid, b.tag, b.loc, b.sku, b.dt_recv from iv_f b where b.loc like '%BULK%' and exists ( select 1 from iv_f p where b.sku = p.sku and p.loc not like '%BULK%' and p.dt_recv > b.dt_recv );

1/22/24 example data

tag# SKU Date Loc
T500 ABC123 2022-01-15 31-BULK
T526 ABC123 2021-02-25 GA-21
T678 ABC123 2023-08-17 BG-99
T690 ABC123 2023-08-18 GB-29
T702 ABC123 2024-01-15 31-BULK

In this example tag T500 is older than TAGs T678 & T690. So it needs to be reported so I can get my team to moved the TAG to a NON-BULK location. to our system BULK, is a non-pickable location and we are using FIFO so we need to move the TAG. I dont need to report Tag T500 multiple times (for TAGs T678 & T690) just once. If you can imagine with 100,000 location, my current report shows 2,000 tags but really should show 50. This is due to just 1 Bulk tag being older than 100 Pickable tags

*** added both comments and an example, either thru comments or below

Thank you for responding!

First I'm relatively new to SQL. I am tasked to create a pull data from our inventory table. Note the inventory table has multi records for the same SKU, the same SKU can be stored in multi locations based on how many fit in each location.

So SKU A can be in 5 locations and SKU B can be in 8 locations.

Here is what I am going for: SKU A (in 5 locations) which 2 of the locations are BULK locations. The goal is to report all BULK locations for an SKU where the Received Date is OLDER than what is NOT in a BULK location (a Pickable Location). Think about ITEM Replenishment.

We want to report all Items that we should move from BULK to a Pickable location. I have been playing around but cant get it. Any help would be super

There are my two attemps:

Attempt #1:

select 
    p.iv_rid, p.tag, P.loc, P.sku, P.dt_recv 
from 
    iv_f P 
where 
    loc not like '%BULK%' 
group by 
    P.sku
    and P.SKU exist in (select B.iv_rid, B.tag, b.loc, B.sku, B.dt_recv 
                        from iv_f B 
                        where B.sku = P.sku 
                          and loc like '%BULK%' 
                          and P.dt_rec > B.dt_rec)

Attempt #2:

select 
    P.iv_rid, P.tag, P.loc, P.sku, P.dt_recv,
    B.iv_ri , B.tag, B.loc, B.sku, B.dt_recv
from 
    iv_f P
join 
    iv_f B on P.sku = B.sku 
where 
    P.loc <> '%BULK%' 
    and B.loc = '%BULK%' 
    and P.dt_recv > B.dt_recv

sample data

Upvotes: 0

Views: 74

Answers (1)

shawnt00
shawnt00

Reputation: 17915

I think this is what you were trying to write:

select p.iv_rid, p.tag, p.loc, p.sku, p.dt_recv 
from iv_f p
where p.loc not like '%BULK%' and exists (
    select 1
    from iv_f b
    where b.sku = p.sku
        and b.loc like '%BULK%' and p.dt_rec > b.dt_rec
);

There's also this somewhat uncommon variation. (Note that there's also a matching all option available in SQL.):

select p.iv_rid, p.tag, p.loc, p.sku, p.dt_recv 
from iv_f p
where p.loc not like '%BULK%' and p.dt_rec > any (
    select b.dt_rec
    from iv_f b
    where b.sku = p.sku and b.loc like '%BULK%'
);

A self join would let you grab columns from both tables if you needed them:

select p.iv_rid, p.tag, p.loc, p.sku, p.dt_recv
from iv_f p inner join iv_f b on b.sku = p.sku
where p.loc not like '%BULK%' and b.loc like '%BULK%'
    and p.dt_rec > b.dt_rec;

Upvotes: 0

Related Questions