Link to home
Start Free TrialLog in
Avatar of kenuk110
kenuk110

asked on

Splitting MSSQL Records in to 750 batches

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 think this 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 Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

you need something like this:

this query only shows what the technique is.
select t.*
   , row_number() over (partition by countryCode, cityCode, storeCode, storeType order by rnum) -1 rn
   , (row_number() over (partition by countryCode, cityCode, storeCode, storeType order by rnum) -1) / 750 bn
  from yourtable

Open in new window

Avatar of kenuk110
kenuk110

ASKER

Hi,

Thanks for the response, you helped me the day before yesterday so thank you again.

I tried to integrate that in to the function I have but I get the following:

Error: 207, Severity: 16, State, Procedure: ServiceViewBatch, Line: 83 [SQL Server] Invalid column name 'rnum'

I'm really no expert, I read your post in your profile and have been trying to figure it out myself but it's just to beyond me, you may say 'well why are you doing it them' ... well, I'm the only one here unfortunately.

This function is run from a few Crystal Reports and they produce instruction to our field staff so I'm racking my head trying to learn SQL and also get help so I really appreciate your help here.

I looked at this error and I'm assuming that it's due to the rnum column being within the bracket? I guess if I take it out then it won't order by rnum?


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, storeType, storeCode, gridCodeX) rnum
, row_number() over (partition by countryCode, cityCode, storeCode, storeType order by rnum) -1 rn
 , (row_number() over (partition by countryCode, cityCode, storeCode, storeType order by rnum) -1) / 750 bn 


  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'
                     )
        )

Open in new window

replace "rnum" by some field of your table that you can use to "order" the items into the batches.
it does not really matter which field, actually, though
Hi,

I changed the end rn and bn to storeCode but I get an error now saying Column name or number of supplied values does not match table definition?

I'm assuming that  the first two parts of this function/script creates two temp tables (although I'm not too sure)...then the last section inserts values in to them and the last section cuts it up in to 750 batches.

I'm really confused by trying to adit this thing, I'm really not sure of what I'm changing, I'm reading like crazy documents pertaining to row_number etc but although it makes sense what the function is for, I still can't put it in to action due to my lack of overall knowledge which is a pain!!!!!

Anyway, I copied what I have changed in to this question but I still get issues.

Any further assistance would be much appreciated.

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, storeType, storeCode, gridCodeX) rnum
, row_number() over (order by countryCode, cityCode, storeCode, storeType) -1 storeCode
, (row_number() over (order by countryCode, cityCode, storeCode, storeType) -1) / 750 storeCode 


  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

Hi again,

I'm copying and pasting and running this, I thin kI'm going round in circles. I have the code at the end of the function like this now but I get the same results as before.

Am I meant to be putting that code you suggested in the last select insert statement of this script, the part which includes SELECT a.* ..... this is the bit that cuts the batches depending on the summed up value for BatchGroup which gets it values from serviceValueTotal.

I'm a little confused....
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, storeType, storeCode, gridCodeX) rnum
, row_number() over (partition by countryCode, cityCode, storeType, storeCode order by countryCode, cityCode, storeCode, storeType, gridCodeX) -1 rnum
,(row_number() over (partition by countryCode, cityCode, storeType, storeCode order by countryCode, cityCode, storeCode, storeType, gridCodeX) -1) / 750 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

Open in new window

you must use the row_number() function in the final select, not in the intermediate insert/select ?!

insert into @ctetemp
  SELECT [ServiceValue]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       --, [itemCode]
       , [itemCodeParsed]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]

  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
,(row_number() over (partition by countryCode, cityCode, storeType, storeCode order by countryCode, cityCode, storeCode, storeType, gridCodeX) -1) / 750 BatchGroup
FROM @ctetemp a

RETURN

Open in new window

Hi,

I've tried that, I now get The multi-part identifier "rt.svSum" could not be bound.

I see that the end of the old script it had the calculation of the 750 brak on the summed value of the serviceValueTotal column which it what it should do as it's not 750 records based on that value, not sure if the script is still doing this or not now or breaking when it hits 750 records?

I have attached the old last part of the script and also the full script as I have it now for reference, I hope I've done what you said correctly?

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

Open in new window

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,
	[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
)
	
insert into @ctetemp
  SELECT [ServiceValue]
       , [ItemName]
       , [countryCode]
       , [storeCode]
       , [cityCode]
       , [itemCode]
       , [itemCodeParsed]
       , [storeType]
       , [vendorCode]
       , [upcCode]
       , [rc]
       , [gridCodeX]
       , [gridCodeY]
       , [facings]
       , [brand]
       , [serviceDay]
       , [serviceType]
       , [ARABIC]
       , [TREF]
       , [serviceValueTotal]

  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
,(row_number() over (partition by countryCode, cityCode, storeType, storeCode order by countryCode, cityCode, storeCode, storeType, gridCodeX) -1) / 750 BatchGroup
FROM @ctetemp a

RETURN
END

Open in new window

what should be the "value" of svSum ?
some running sum?


INSERT INTO @cte
SELECT a.*
, sum(serviceValueTotal) over (partition by countryCode, cityCode, storeType, storeCode order by countryCode, cityCode, storeCode, storeType, gridCodeX) as RunTotal
,(row_number() over (partition by countryCode, cityCode, storeType, storeCode order by countryCode, cityCode, storeCode, storeType, gridCodeX) -1) / 750 as BatchGroup
FROM @ctetemp a

Open in new window

Hi,

The value was I think, looking at the original code I posted, the value of the column serviceValueTotal, I've copied it here so it may make more sense:

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

Open in new window

The original script used to count the values in the serviceValueTotal column after it had been oredered by countrycode, cityCode, storeType, storeCode, gridCodeX, once it reached 750 it should 'break' to a new batch, number two then the next 750 then batch 3. The batches though should be 'grouped' in to individual storeCode 's, so store 006 if the value of serviceValueTotal reached 800 then the first batch would be 750 and the second, 50, then it would go to store 007, this store may have only reach 500 in which case it would only get batch 1 for this store then go on to the next. It's the store, after the ordering that should get each batch starting at 1 then if it includes more than 750 it gets two batches, named 1 and 2 etc.

I tried the code, now I get Incorrect syntax near 'order'

It says the error is on the line with , sum(serviceValueTotal) over (partition ........

Any ideas?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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
Hi,

Well the script ran but it's not working right, the original script worked other than it didn't split the batches per store, the current script adds the run total not relating to the incremental sum of the serviceValueTotal column after it has been ordered by countryCode, cityCode, storeType, storeCode, gridCodeX. I'm not sure really what the Run Total column is doing now, I get huge number and the same number per batch group.

I'll revert back to the origianl and try to get something going with it, it's a shame I'm not more knowlegable to help you help me more, I'll get back to this post though when I get somewhere.

Really appreciate your help on this!
SOLUTION
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
I took all the info and I think by chance figured out the end solution. Really appreciate all the help.