Exclude records based on another records value?

Hi,

I have a a table which has entries per day for different services we have in our business.

We have a product that will have, on a Monday the following services:

UPC         ServiceDay         ServiceType
12345       Monday               VMF
12345       Monday               SRR
12345       Monday               IDR
12345       Monday               DCS

Now, if a client has our IDR service selected then I need to be able to say on the IDR report, do NOT include the UPC codes that have a ServiceType of SRR next to it. The connection here is the UPC and the ServiceDay I believe. The way our data is retrieved is from a CTE and this creates 'batches' of prints based on a value that is added up, which equals 750.

I have attached the CTE that I run to get the data back in the first place but I need to say for ALL the UPC codes in THIS store bring me back the IDR record ONLY if the SRR record for the same product for the same day is not present and this also needs to be confined to batches so batch 1 will only get batch one UPC codes.

In fact we could make sure it only selects the items from the GridCodeX reference for that batch - if that makes sense.

I'm just looking at the code I already have and maybe it's better just to not retrieve the records for the IDR if the same record exists for the SRR?

Any further clarification or help I can give anybody, please just ask.

Any help will be much appreciated.


Regards,

Ken

ALTER function [dbo].[ServiceViewBatch](@date date)
returns table
as

RETURN WITH cte AS (
  SELECT [ServiceValue]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       , [itemCode]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]
       , ROW_NUMBER() 
            OVER (ORDER BY CountryCode, CityCode, StoreCode, gridcodeX) rnum
  FROM [T1].[dbo].[ServiceSchedule2] 
  WHERE serviceday = DATENAME(WEEKDAY, @date)
  
) 
-- final selection
SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup
FROM cte a
CROSS APPLY(
  SELECT SUM(serviceValueTotal) svSum
  FROM cte b
  WHERE b.rnum <= a.rnum
) rt

Select allOpen in new window