Compare 2 rows in the same table and output result when conditions are met

Question

Compare 2 rows in the same table and output result when conditions are met

SSC Enthusiast

Points: 175

February 11, 2022 at 6:03 am

I am newbie to SQL, tried searching for answers for similar question but couldn't find any. Sorry, if this type of question was asked before.

I have a temp table as below:

Drop TABLE if EXISTS #temp
Create table #temp(ID int, Country VARCHAR(40), ItemCount int, DeliveryDate Date, Itemtype VARCHAR(40) )
insert #temp(id,Country,itemCount,DeliveryDate,Itemtype)
Select 
             3012111,'Dublin',     100, '01-01-2022', 'Head Gears'
union select 2012111,'Dublin',     200, '01-05-2022', 'Head Gears'
union select 2012112,'Australia',  300, '01-03-2022', 'Knee Pad'
union select 2012110,'Australia',  100, '01-04-2022', 'Head Gears'
union select 2012113,'Singapore',  150, '01-05-2022', 'Head Gears'
union select 2012114,'Singapore',  200, '01-07-2022', 'FootWear'
union select 2012116,'Brazil',     500, '01-08-2022', 'Head Gears'
union select 2012115,'Brazil',     300, '01-06-2022', 'Head Gears'
union select 2012117,'Indonesia',  150, '01-10-2022', 'Foot Wear'

I am trying to write a query to output result from temp table only when the following conditions is met:

Grouped by "Country, compare the Itemcount and deliverydate
Return output only if the deliverydate of lowest itemcount for a given country by itemtype "Head Gears" is before the largest itemcount.
If there is only one delivery for a given country by Itemtype "Head Gears", don't return anything as an output.

Select X.Country, min(id) Id, X.ItemCount
from (
select Country, max(itemCount) itemCount
from #temp
group by Country
) X
inner join #temp T
on X.Country = T.Country
and X.itemCount = T.ItemCount
group by X.Country, X.itemCount

When i run the above query, i get max itemcount by country and by type but unable to achieve what i am looking for. Please help.

Output from the above query:

Screenshot 2022-02-10 215705

OUTPUT NEEDED:

OUTPUT Intended

Thanks for your help!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80338 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Steve Collins SSCommitted Points: 1686 More actions · Answer 2

The explanation seems somewhat open to interpretation. Maybe you could explain why Brazil's ItemCount is 300 and not 500? If 500 is the correct number for Brazil then maybe something like this

with
rn_cte(id, Country, itemCount, DeliveryDate, Itemtype, rn_desc, rn_asc) as (
    select *, 
           row_number() over (partition by Country order by ItemCount desc),
           row_number() over (partition by Country order by ItemCount asc)
    from #temp
    where Itemtype='Head Gears'),
per_country_cte(Country) as (
    select Country
    from rn_cte
    group by Country
    having count(*)>1
           and min(iif(rn_asc=1, DeliveryDate, null))<max(iif(rn_desc=1, DeliveryDate, null)))
select rn.id, rn.Country, rn.itemCount, rn.DeliveryDate, rn.Itemtype
from rn_cte rn
     join per_country_cte pc on rn.Country=pc.Country
where rn.rn_desc=1;

This reply was modified 2 years, 7 months ago by Steve Collins.
This reply was modified 2 years, 7 months ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

RamonS Newbie Points: 5 More actions · Answer 3

-- Hope it helps

with

MultHD as (

select distinct aa.Country from #temp as aa

where (select count(bb.*) from #temp as bb where bb.Country = aa.Country and rtrim(bb.ItemType) = 'Head Gears') > 1

)

,

LowHD as (

select cc.Country

, (select min(dd.itemCount) from #temp as dd where rtrim(dd.Country ) = rtrim(cc.Country) and rtrim(dd.ItemType) = 'Head Gears' and dd.DeliveryDate < (select max(ee.DeliveryDate ) from #temp AS ee where rtrim(ee.Country ) = rtrim(cc.Country) and rtrim(ee.ItemType) = 'Head Gears')) as MinHD

from MultHD as cc

)

select

ff.Country, ff.Id, ff.ItemCount, ff.DeliveryDate, ff.ItemType

from #temp as ff

where exists (select gg.* from LowHD as gg where rtrim(gg.Country) = rtrim(ff.Country) and gg.ItemCount = ff.ItemCount)

-- As seen, ItemCount is considered unique... This query may need improvements with large initial dataset... Besides, I think this is a good startpoint.

-- Best regards.

RamonS Newbie Points: 5 More actions · Answer 4

-- Debugged with MSSQL Management Studio...

Drop TABLE if EXISTS #tempCreate table #temp(ID int, Country VARCHAR(40), ItemCount int, DeliveryDate Date, Itemtype VARCHAR(40) )

insert #temp(id,Country,itemCount,DeliveryDate,Itemtype)

Select

3012111,'Dublin', 100, '01-01-2022', 'Head Gears'

union select 2012111,'Dublin', 200, '01-05-2022', 'Head Gears'

union select 2012112,'Australia', 300, '01-03-2022', 'Knee Pad'

union select 2012110,'Australia', 100, '01-04-2022', 'Head Gears'

union select 2012113,'Singapore', 150, '01-05-2022', 'Head Gears'

union select 2012114,'Singapore', 200, '01-07-2022', 'FootWear'

union select 2012116,'Brazil', 500, '01-08-2022', 'Head Gears'

union select 2012115,'Brazil', 300, '01-06-2022', 'Head Gears'

union select 2012117,'Indonesia', 150, '01-10-2022', 'Foot Wear'

;