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

Question

Post reply

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

Ankita08

SSC Enthusiast

Points: 175

February 11, 2022 at 6:04 am

Go to Answer

#3981822

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!

Johan Bijnens

SSC Guru

Points: 134873

February 11, 2022 at 8:13 am

Answer

#3981844

There is something fishy with the question:

Are you really going to mix all Item types in this comparison?
How could you handle equal itemcount rows for a given country?

WITH cteCalculatedRows
     AS (SELECT *
              , MIN(ItemCount) OVER(PARTITION BY country) AS min_ItemCount
              , MAX(ItemCount) OVER(PARTITION BY country) AS max_ItemCount
              , MIN(DeliveryDate) OVER(PARTITION BY country) AS min_DeliveryDate
              , MAX(DeliveryDate) OVER(PARTITION BY country) AS max_DeliveryDate
              , SUM(CASE itemtype
                        WHEN 'Head Gears'
                        THEN 1
                        ELSE 0
                    END) OVER(PARTITION BY country) AS nRows_HeadGears
              , COUNT(*) OVER(PARTITION BY country) AS nRows_Country
              , RANK() OVER(PARTITION BY country
                ORDER BY Country
                       , DeliveryDate) AS RankIn_Country
         FROM #temp),
     cteMeetConditions
     AS (SELECT CRmin.*
         FROM cteCalculatedRows AS CRmin
         INNER JOIN cteCalculatedRows AS CRmax
              ON CRmax.Country = CRmin.Country
         WHERE CRmin.nRows_HeadGears > 1
               AND CRmin.ItemCount = CRmin.min_ItemCount
               AND CRmax.ItemCount = CRmin.max_ItemCount
               AND CRmax.DeliveryDate > CRmin.DeliveryDate
   )
 -- of all rows meeting the conditions only return 'Head Gears'
     SELECT *
     FROM cteMeetConditions
     WHERE itemtype = 'Head Gears'
     ORDER BY Country
            , RankIn_Country;

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Viewing 3 posts - 1 through 2 (of 2 total)

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

Ankita08 SSC Enthusiast Points: 175 More actions · Answer 1

Johan Bijnens, Thank you so much for your timely help. Your solution worked great for me!

To clarify,

Are you really going to mix all Item types in this comparison? The Source contains many item types but my interest in comparison is only for Head Gears.

How could you handle equal itemcount rows for a given country? If there is an equal item count rows, then I want to simply ignore it and not output anything.