Calculating Batch summaries and aggregates

• Tom257

  SSChampion

  Points: 11543

  More actions

  April 15, 2010 at 3:41 am

  #156874

  I've tried to post this several times with the code, but it wont work, so the code is attached as a file, with required results.

  I have an order table where the sequence of like colours should

  be batched, and the sequence of batches preserved.

  So far my efforts have failed to come close to achieving this

  so any suggestions would be welcome.

  (and if anyone knows why the file contents won't please post let me know)

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  April 15, 2010 at 3:56 am

  #1151982

  Just posting the file contents so ppl don't need to download. (no, I don't know why it wouldn't post for you).

  CREATE TABLE dbo.TmpOrder (

  Sequence INT IDENTITY(1,1) PRIMARY KEY,

  ProductID INT,

  Colour varchar(10) );

  INSERT INTO dbo.TmpOrder

  SELECT 1, 'Red' UNION ALL

  SELECT 2, 'Red' UNION ALL

  SELECT 2, 'Blue' UNION ALL

  SELECT 1, 'Yellow' UNION ALL

  SELECT 3, 'Yellow' UNION ALL

  SELECT 4, 'Yellow' UNION ALL

  SELECT 2, 'Red' UNION ALL

  SELECT 2, 'Red' UNION ALL

  SELECT 2, 'Red' UNION ALL

  SELECT 2, 'Yellow' UNION ALL

  SELECT 3, 'Yellow' UNION ALL

  SELECT 4, 'Yellow' UNION ALL

  SELECT 3, 'Yellow' UNION ALL

  SELECT 2, 'Blue' UNION ALL

  SELECT 2, 'Blue';

  SELECT * FROM dbo.TmpOrder

  /* Required Results

  BATCH SUMMARY

  BatchNoStartingSequenceColourBatchSize UniqueProductCount

  --------------------------------------- -------------------

  11Red2 2

  23Blue1 1

  34Yellow3 3

  47Red3 1

  510Yellow4 3

  614Blue2 1

  BATCH AGGREGATES

  TotalOrdersNumberOfBatchesAverageBatchSize

  ------------------------------------------

  1562.5

  */

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Tom257

  SSChampion

  Points: 11543

  More actions

  April 15, 2010 at 4:26 am

  #1151993

  Thanks skcadavre

  I'm currently working on a CTE to identify the colour change condition

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  April 15, 2010 at 4:36 am

  #1151998

  WITH CTE AS (

  SELECT Sequence,ProductID,Colour,

  ROW_NUMBER() OVER(PARTITION BY Colour ORDER BY Sequence) AS rn

  FROM dbo.TmpOrder),

  Results AS (

  SELECT MIN(Sequence) AS StartingSequence,

  Colour,

  COUNT(*) AS BatchSize,

  COUNT(DISTINCT ProductID) AS UniqueProductCount

  FROM CTE

  GROUP BY Colour,rn-Sequence)

  SELECT ROW_NUMBER() OVER(ORDER BY StartingSequence) AS BatchNo,

  StartingSequence,Colour,BatchSize,UniqueProductCount

  FROM Results

  ORDER BY StartingSequence;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Tom257

  SSChampion

  Points: 11543

  More actions

  April 15, 2010 at 4:52 am

  #1152005

  Thanks Mark

  That works great!