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

kenuk110Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kenuk110Author Commented:
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!!!
0
kenuk110Author Commented:
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.
0
kenuk110Author Commented:
Is it possible for anyone to help me with this issue I have?

I have also tried using PARTITION BY before the ORDER BY statement about half way down, this DOES seperate by batch but the counting is wrong then, it doesn't add the ServiceTotalValue up correctly.

Really not sure what to do here??

Regards,

Ken
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.