need help filtering returned rows

[Keevo]

Hi, I hope your a smarty pants as I need help filtering the result of the query below. In the image link attached are the records returned. Indicated in red on the image is the records I would like returned. the criteria is has a common field of UPC and the criteria consists of where Type column equals adjustment column and sales, for the same upc. Still want all the columns returned... but jut all records that fit the mentioned criteria , here is what I have to generate the current record set:

select Type, StoreNumber, CostSource, RetailPriceSource, IMD.UPC, IM.Description, IM.Size, IM.Measure, 
cast(round(sum(qtysold),2)as decimal (10,0)) as Qty, IM.MaximumStockLevel, IM.Sweetness as Rotation
from ItemMovementDetail IMD
join ItemMaster IM on
IM.UPC = IMD.UPC
where SaleDate between '2014-06-01' and '2014-07-07' and
IMD.UPC not between '50101' and '59999' and
Indicator != 'V' and 
StoreNumber = 8 and
RetailPriceSource not between '0001' and '0010' and
IM.Discontinued = 0 and
IMD.UPC = IMD.UPC
group by IMD.UPC, Type, RetailPriceSource, CostSource, StoreNumber, IM.Description, IM.Size, IM.Measure, IM.Sweetness, IM.MaximumStockLevel
order by upc, Type

 

[imex]

Hi,

Try something like this:

with
    CTE_Q as
    (
        -- your query without order by
    ),

    CTE_T as
    (
        select UPC
        from CTE_Q
        where Type in ('adjustment', 'sales')
        group by UPC
        having COUNT(distinct Type) = 2
    )

select q.*
from CTE_T as t
inner join CTE_Q as q
    on q.UPC = t.UPC
order by q.upc, q.Type

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[Keevo]

imex your brilliant ! Works like a charm!
Thank you