CODEWITHSUNDEEP
sqlsql-server
Darshak Kumbhani
Darshak Kumbhani

Reputation: 9

Generate group of data based on sum of values

How to calculate the sum and total files.

here is my table

**FileId**          **FileSize(MB)**
    1                      5
    2                      4
    3                      1
    4                      6
    5                      8
    6                      1
    7                      7
    8                      2

Expected result

BatchNo         StartId          EndId        BatchSize
   1               1               3             10
   2               4               4              6
   3               5               6              9
   4               7               8              9

If File Size >= 10 then start new batch

also file count per batch is >= 10 then start new batch

StartId and EndId based on FileId

and BatchNo Is AutoIncrement

Upvotes: 0

Views: 191

Answers (2)

Sandeep Kumar
Sandeep Kumar

Reputation: 315

Here is another solution, with different approach:

INSERT INTO #batchdetails (FileID, FileSizeTotal, GroupID)
SELECT FileID, (
            SELECT SUM(filesize) FROM #filedetails f2
            WHERE f1.fileid >= f2.fileid ) AS FileSizeTotal,
    1+CONVERT(INT,(
            SELECT SUM(filesize) FROM #filedetails f2
            WHERE f1.fileid >= f2.fileid
    )/(@filesizepergroup+0.1)) AS GroupID
FROM #filedetails f1

SELECT DISTINCT
    BatchDetails.GroupID AS BatchNo,
    MIN(BatchDetails.FileID) OVER (PARTITION BY BatchDetails.GroupID ORDER BY BatchDetails.GroupID) AS StartID,
    MAX(BatchDetails.FileID) OVER (PARTITION BY BatchDetails.GroupID ORDER BY BatchDetails.GroupID) AS EndID,
    BatchSizeGroup.BatchSize
FROM #batchdetails BatchDetails
INNER JOIN (
    SELECT GroupID, (GroupFileSizeTotal - LAG(GroupFileSizeTotal,1,0) OVER (ORDER BY GroupID)) AS BatchSize
    FROM (
        SELECT DISTINCT 
            GroupID, 
            MAX(FileSizeTotal) OVER (PARTITION BY GroupID ORDER BY GroupID) AS GroupFileSizeTotal
        FROM #batchdetails
        GROUP BY GroupID, FileSizeTotal
    )A
)BatchSizeGroup ON BatchDetails.GroupID = BatchSizeGroup.GroupID
GROUP BY BatchDetails.GroupID, BatchDetails.FileID, BatchSizeGroup.BatchSize

Demo is Here : dbfiddle

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10701

You can use recursive query like this

with rdata as
(
      select row_number() over (order by fileId) rn, * from data
), rcte as
(
  select 1 no, 1 gr, fileSize fileSizeSum , * 
  from rdata where fileid = 1
  union all
  select case when fileSizeSum + d.fileSize > 10 or r.no = 10 then 1 else r.no + 1 end gr,
         case when fileSizeSum + d.fileSize > 10 or r.no = 10 then r.gr + 1 else r.gr end gr,
         case when fileSizeSum + d.fileSize > 10 or r.no = 10 then d.fileSize else d.fileSize + fileSizeSum  end fileSizeSum, 
         d.*
  from rcte r
  join rdata d on r.rn + 1 = d.rn
)
select r.gr, 
       min(fileId), 
       max(fileId), 
       max(fileSizeSum)
from rcte r
group by r.gr

dbfiddle

Upvotes: 5

Related Questions