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.
ALTER function [dbo].[ServiceViewBatch](@date date)
RETURN WITH cte AS (
OVER (ORDER BY CountryCode, CityCode, StoreCode, gridcodeX) rnum
WHERE serviceday = DATENAME(WEEKDAY, @date)
-- final selection
, rt.svSum as RunTotal
, convert(int, ceiling(rt.svSum/750)) as BatchGroup
FROM cte a
SELECT SUM(serviceValueTotal) svSum
FROM cte b
WHERE b.rnum <= a.rnum