Group together records per file size

• Nomvula

  Hall of Fame

  Points: 3770

  More actions

  April 10, 2019 at 8:11 am

  #3085375

  I've created a script where I need to group all records with max file size of 8mb, Once the size has been reached I need to start with the next set of grouping but my query calculates ok up to 8mb then it's stops counting as return the value as it is and not adding

  WITH CTE_CreateFolders

  --( RowNum, Forename, ForenameLength )

  AS (SELECT dli.CostCentreCode AS FolderName

  ,dli.FileName AS FileNameAndPath

  ,ROW_NUMBER() OVER (ORDER BY dli.CostCentreCode, dli.FileName) AS RowNum

  ,dli.FileSize AS FileSize

  ,dli.CostCentreCode

  FROM report.DocumentLinkImportsOPEX AS dli

  LEFT JOIN (SELECT dlio.CostCentreCode

  ,SUM(dlio.FileSize) / 1024 / 1000 AS FileSize

  FROM report.DocumentLinkImportsOPEX AS dlio

  GROUP BY dlio.CostCentreCode

  ) AS Size ON Size.CostCentreCode = dli.CostCentreCode

  WHERE Size.FileSize >= 8

  )

  SELECT ROW_NUMBER() OVER (PARTITION BY FolderName ORDER BY FolderName) AS GroupRow

  ,FolderName

  ,FileNameAndPath

  ,RowNum

  ,FileSize

  ,CostCentreCode

  ,CASE WHEN ((ISNULL(FileSize + (SELECT ISNULL(SUM(FileSize), 0)

  FROM CTE_CreateFolders

  WHERE RowNum < n.RowNum

  ), 0) > 8000000))

  THEN ISNULL(FileSize + 0, 0)

  ELSE ISNULL(FileSize + (SELECT ISNULL(SUM(FileSize), 0)

  FROM CTE_CreateFolders

  WHERE RowNum < n.RowNum

  ), 0)

  END AS sumfilesize

  FROM CTE_CreateFolders n

  ORDER BY CostCentreCode

  ,GroupRow;