RE: Select By Date Groups – SQLServerCentral

SSC Guru

Points: 442467

May 23, 2018 at 2:01 pm

#1991699

sgmunson - Wednesday, May 23, 2018 1:14 PM
I'm most interested in WHY product1 has a count of 3. Is it simply the maximum number of consecutive days that said product appears in the data? If I merely COUNT the number of rows for product1, it's a lot more than 3.

I think I figured out how the given results were determined:

IF OBJECT_ID('[dbo].[products]','U') IS NOT NULL DROP TABLE [dbo].[products];

CREATE TABLE [dbo].[products] (
[id] int
, [product_name] varchar(25)
, [start_date] date
, [end_date] date
);

INSERT INTO [dbo].[products] (id,product_name,start_date,end_date)
VALUES ( 1, 'product1', '2018-04-20', '2018-04-21'),
       ( 2, 'product1', '2018-04-22', '2018-04-23'),
       ( 3, 'product1', '2018-04-24', '2018-04-25'),
       ( 4, 'product2', '2018-04-20', '2018-04-21'),
       ( 5, 'product2', '2018-04-22', '2018-04-23'),
       ( 6, 'product2', '2018-04-24', '2018-04-25'),
       ( 7, 'product3', '2018-04-20', '2018-04-21'),
       ( 8, 'product3', '2018-04-22', '2018-04-23'),
       ( 9, 'product3', '2018-04-24', '2018-04-25'),
       (10, 'product1', '2018-04-26', '2018-04-27'),
       (11, 'product1', '2018-04-28', '2018-04-29'),
       (12, 'product2', '2018-04-27', '2018-04-28'),
       (13, 'product2', '2018-04-29', '2018-04-30'),
       (14, 'product3', '2018-04-29', '2018-04-30'),
       (15, 'product1', '2017-04-20', '2017-04-21');
GO

WITH base AS (
SELECT
[p].[product_name]
, [grp] = [p].[id] - ROW_NUMBER() OVER (PARTITION BY [p].[product_name] ORDER BY [p].[id], [p].[start_date])
FROM [dbo].[products] AS [p]
)
SELECT
.[product_name]
, [Cnt] = COUNT(DISTINCT .[grp])
FROM
[base] AS
GROUP BY
.[product_name]
ORDER BY
.[product_name];

IF OBJECT_ID('[dbo].[products]','U') IS NOT NULL
DROP TABLE [dbo].[products];