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
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
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER