Link to home
Start Free TrialLog in
Avatar of kenuk110
kenuk110

asked on

Splitting returned record in to batches of 750 based on criteria

Hi,

I have an MSSQL function that brings back an amount of records and splits them in to 'batches' of 750 then creates a new batch. The function (attached) is bringing them back in batches but i need to amend it to bring back the batches based on the following:

countryCode, cityCode, storeCode, storeType

Just as background info, we merchandise products in supermarkets/stores and we have an amount of work to complete within those stores. A merchandiser can only physically complete 750 transactions. The need for this is to split transactions per store then we assign a resource to work on those.

A batch would be like this then:

If store 006 had 1000 transactions (these are sumed from the ServiceValueTotal column) then 006 would get 2 batches of information, 1 would equal 750 and the second would equal 250.

The next store, say 007 would have 800 transactions, batch 1 would equal 750 and batch 2 would equal 150.

They need to be grouped/split/partitioned (not sure of the correct DB term) based on the store and the others listed above. Is this possible.

To be honest, I thin kthis function did this but them I was only working with one store testing so when it reached 750 it just created another batch, I never tested it with 2 stores and seeing if it grouped by store THEN split in to batches.

Any help on this would be much appreciated.

Best Regards,

Ken
ALTER FUNCTION [dbo].[ServiceViewBatch] (@date datetime)
RETURNS @cte table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL,
	[RunTotal] [decimal](38, 2) NULL,
	[BatchGroup] [int] NULL)


AS
-- body of the function
BEGIN

declare @ctetemp table
(
    -- columns returned by the function
	[ServiceValue] decimal NULL,
	[ItemName] nvarchar (255) NULL,
	[countryCode] [nvarchar](3) NOT NULL,
	[storeCode] [nvarchar](3) NOT NULL,
	[cityCode] [nvarchar](2) NOT NULL,
	--[itemCode] [nvarchar](1000) NOT NULL,
	[itemCodeParsed] [nvarchar] (255) NULL,
	[storeType] [nvarchar](1) NOT NULL,
	[vendorCode] [nvarchar](3) NOT NULL,
	[upcCode] [nvarchar](30) NOT NULL,
	[rc] [nvarchar](3)  NULL,
	[gridCodeX] [nvarchar](4) NOT NULL,
	[gridCodeY] [nvarchar](4) NOT NULL,
	[facings] [nvarchar](3) NULL,
	[brand] [nvarchar](255) NULL,
	[serviceDay] [nvarchar](9) NULL,
	[serviceType] [nvarchar](5) NULL,
	[ARABIC] [nvarchar](255) NULL,
	[TREF] [nvarchar](255) NULL,
	[serviceValueTotal] [decimal](29, 2) NULL,
	[rnum] [bigint] NULL);
	
insert into @ctetemp
  SELECT [ServiceValue]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       --, [itemCode]
       , [itemCodeParsed]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]
       , ROW_NUMBER() 
            OVER (ORDER BY CountryCode, CityCode, StoreCode, storeType,gridcodeX) rnum
  FROM [T1].[dbo].[ServiceSchedule2]  s
  WHERE serviceday = DATENAME(WEEKDAY, @date)
    AND ( ServiceType <> 'IDR' 
        OR NOT EXISTS( select null from [T1].[dbo].[ServiceSchedule2] o
                   where o.UPCcode = s.UPCcode         
                     and o.ServiceDay = s.ServiceDay
                     AND o.ServiceType = 'SRR'
                     )
        )


INSERT INTO @cte
SELECT a.*
     , rt.svSum as RunTotal
     , convert(int, ceiling(rt.svSum/750)) as BatchGroup
FROM @ctetemp a
CROSS APPLY(
  SELECT SUM(serviceValueTotal) svSum
  FROM @ctetemp b
  WHERE b.rnum <= a.rnum
) rt

RETURN
END

Open in new window

Avatar of kenuk110
kenuk110

ASKER

By the way, I got this function from Experts Exchange in the first place so thank you so much to the person who helped the first time around!!!
Oops, one more thing, these batches are split initially in to the day we select to give to the function so we say 'tomorrow' then it batches the results based on the ServiceDay.
ASKER CERTIFIED SOLUTION
Avatar of kenuk110
kenuk110

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial