troubleshooting Question

Exclude records based on another records value?

Avatar of kenuk110
kenuk110 asked on
Microsoft SQL Server 2008
6 Comments1 Solution357 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros