Return data only for specific condition SQL Server

I need help on my query to display data only for an specific condition.

Here is the query

select      sv.lookupCode as shipment,
            tv.materialLookupCode,
            tv.vendorLotLookupCode,
            sv.shippedDate

from        datex_footprint.ShipmentsView sv
join        datex_footprint.TasksView tv on sv.id = tv.shipmentId

where       sv.typeId = 2
and         sv.statusId = 8
and         sv.shippedDate Between Dateadd(mm,-30,getdate()) ANd getdate()
and         sv.lookupCode = '58321'
and         tv.operationCodeId = 8
and         tv.projectId = 490


group by    sv.lookupCode,
            tv.materialLookupCode,
            tv.vendorLotLookupCode,
            sv.shippedDate

So in my query I need to add where it should only return those 4 highlighted rows which are 4, so basically it should only return duplicate materialLookupcodes having more than 1 vendorlotlookupcode

Answer

You can do:

with
q as (
  -- your query here
),
s as (
  select
    shipment, 
    materialLookupCode,
    min(vendorLotLookupCode) as min_code,
    max(vendorLotLookupCode) as max_code
  from q
  group by shipment, materialLookupCode
)
select q.*
from q
join s on s.shipment = q.shipment
      and s.materialLookupCode = q.materialLookupCode
where s.min_code <> s.max_code

Alternatively, it could be shorter to use a window function computing DISTINCT row values, but I don’t think this is implemented in SQL Server [yet].