SQLTeam.com | Weblogs | Forums

Need data from a single table(Count,Starting Date & Ending Date)


#1

Hi Friend,

I am new to sql please help me

Sample Data

Table 1

Item Date
A 11/06/2014
A 12/06/2014
A 13/06/2014
A 14/06/2014
A 18/06/2014
A 19/06/2014
A 20/06/2014
A 25/06/2014

Expected result

Item Quantity StartDate EndDate
A 4 11/06/2014 14/06/2014
A 3 18/06/2014 20/06/2014
A 1 25/06/2014 25/06/2014

Item - Item Name

Quantity - Number Count in continuous date i.e.,11/06/2014 to 14/06/2014 = 4 ,18 to 20/06/2014 = 3 , 25/06/2014 to 25/06/2014 = 1

StartDate - Continutity Starting Date

EndDate - Continutity Ending Date

Please let me know if you any additional information.
Help me..


#2
DECLARE @tbl TABLE (
    item CHAR(1)
    ,[date] DATETIME
    )

SET DATEFORMAT dmy

INSERT INTO @tbl
SELECT 'A'
    ,'11/06/2014'

INSERT INTO @tbl
SELECT 'A'
    ,'12/06/2014'

INSERT INTO @tbl
SELECT 'A'
    ,'13/06/2014'

INSERT INTO @tbl
SELECT 'A'
    ,'14/06/2014'

INSERT INTO @tbl
SELECT 'A'
    ,'18/06/2014'

INSERT INTO @tbl
SELECT 'A'
    ,'19/06/2014'

INSERT INTO @tbl
SELECT 'A'
    ,'20/06/2014'

INSERT INTO @tbl
SELECT 'A'
    ,'25/06/2014';

WITH cte
AS (
    SELECT *
        ,rn = row_number() OVER (
            PARTITION BY item ORDER BY DATE
            )
    FROM @tbl
    )
    ,rcte
AS (
    SELECT *
        ,grp = 1
    FROM cte
    WHERE rn = 1
    
    UNION ALL
    
    SELECT c.*
        ,grp = CASE 
            WHEN r.DATE = c.DATE - 1
                THEN r.grp
            ELSE r.grp + 1
            END
    FROM rcte r
    INNER JOIN cte c ON r.item = c.item
        AND r.rn = c.rn - 1
    )
SELECT item
    ,count(*)
    ,min(DATE)
    ,max(DATE)
FROM rcte
GROUP BY item
    ,grp

#3

Thank you Khtan Great..
Its Working..