How to make group by Revision_ID and when repeated display last check date separ

  • ahmed_elbarbary.2010

    SSCrazy

    Points: 2675

    problem

    How to make group by Revision_ID and when repeated display last check date separated by stick | ?

    I need to group data by Revision_ID that make count to all zpartid

    every revision_ID Have group of parts .

    and when revision id repeated two time then lastcheckdate is firstdate | seconddate

    and if more than two time then display text multi date

    so How to do that please ?

    CREATE TABLE [Parts].[LifeCycleMaster](
    [LifeCycleID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [ZPartID] [bigint] NOT NULL,
    [LastCheckDate] [date] NULL,
    [Revision_ID] [bigint] NULL,
    CONSTRAINT [PK_LifeCycleMaster_LifeCycleID] PRIMARY KEY CLUSTERED
    (
    [LifeCycleID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    CONSTRAINT [UK_PartID] UNIQUE NONCLUSTERED
    (
    [ZPartID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    Revision_ID ZPartID LastCheckDate
    12 10 12/12/2015
    15 120 12/01/2014
    15 130 05/05/2016
    20 170 09/03/2013
    20 200 09/05/2016
    20 300 09/08/2017

    FinalResult
    Revision_ID CountParts LastCheckDate
    12 1 12/12/2015
    15 2 05/05/2016 |12/01/2014
    20 3 Multi date
  • scdecade

    SSChasing Mays

    Points: 654

    with
    LifeCycleMaster_cte(Revision_ID, ZPartID, LastCheckDate) as (
    select 12, 10, '12/12/2015'
    union all
    select 15, 120, '12/01/2014'
    union all
    select 15, 130, '05/05/2016'
    union all
    select 20, 170, '09/03/2013'
    union all
    select 20, 200, '09/05/2016'
    union all
    select 20, 300, '09/08/2017'),
    LifeCycleMaster_counts_cte(Revision_ID, ZPartID, LastCheckDate, CountParts, CountRows) as (
    select
    Revision_ID,
    ZPartID,
    LastCheckDate,
    row_number() over(partition by Revision_ID order by LastCheckDate desc),
    count(*) over(partition by Revision_ID)
    from
    LifeCycleMaster_cte),
    Revision_ID_cte(Revision_ID, CountParts, LastCheckDate) as (
    select
    Revision_ID,
    CountParts,
    case when CountParts=1 then cast(LastCheckDate as nvarchar(10))
    when CountParts=2 then (select
    stuff((select ' | ' + cast(LastCheckDate as nvarchar(10))
    from
    LifeCycleMaster_counts_cte lcc
    where
    lcc.Revision_ID=l.Revision_ID
    order by
    lcc.LastCheckDate desc
    for xml path ('')), 1, 3, ''))
    else 'Multi date' end
    from
    LifeCycleMaster_counts_cte l
    where
    CountParts=CountRows)
    select * from Revision_ID_cte;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • pietlinden

    SSC Guru

    Points: 62674

    One way... You may need to modify the FORMAT()… bit to get the dates formatted the way you want. I did that because I wanted them cast as strings.

    SELECT lcm.Revision_ID
     , COUNT(ZPartID) as CountParts
     , DateList = STUFF(( SELECT ', ' + FORMAT(LastCheckDate,'yyyy-MMM-dd')
        FROM LifeCycleMaster lcm2
        WHERE lcm2.Revision_ID = lcm.Revision_ID
        FOR XML PATH('')), 1, 1, '')
    FROM LifeCycleMaster lcm
    GROUP BY Revision_ID;
  • Jonathan AC Roberts

    SSCoach

    Points: 17161

    ;WITH cte AS 
    (
    SELECT Revision_ID,
    COUNT(*) CountParts
    FROM [Parts].[LifeCycleMaster]
    GROUP BY Revision_ID
    )
    SELECT a.Revision_ID,
    a.CountParts,
    CASE WHEN a.CountParts < 3 THEN b.LastCheckDate
    ELSE 'Multi date'
    END LastCheckDate
    FROM cte a
    CROSS APPLY(SELECT STUFF((SELECT DISTINCT ' | ' + convert(varchar,b.LastCheckDate,103)
    FROM [Parts].[LifeCycleMaster] b
    WHERE b.Revision_ID=a.Revision_ID
    ORDER BY 1
    FOR XML PATH('')),1,3,'')) b(LastCheckDate)
  • scdecade

    SSChasing Mays

    Points: 654

    Well I was close.  Should've summarized from the beginning.  I thought about CROSS APPLY but it seems fussy when there's no TVF because there's still the old ways.  The DISTINCT is a questionable addition imo... the OP didn't mention anything about no dupes.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • scdecade

    SSChasing Mays

    Points: 654

    Final answer, shamelessly borrowing from Jonathan, still without CROSS APPLY.  Also, Jonathan's answer with sample data cte included (copy/paste/run).

    /* borrowed */
    with
    LifeCycleMaster_cte(Revision_ID, ZPartID, LastCheckDate) as (
    select 12, 10, '12/12/2015'
    union all
    select 15, 120, '12/01/2014'
    union all
    select 15, 130, '05/05/2016'
    union all
    select 20, 170, '09/03/2013'
    union all
    select 20, 200, '09/05/2016'
    union all
    select 20, 300, '09/08/2017'),
    counts_cte(Revision_ID, LastCheckDate, CountParts) as (
    select
    Revision_ID, max(LastCheckDate), count(*)
    from
    LifeCycleMaster_cte
    group by
    Revision_ID)
    select
    Revision_ID,
    CountParts,
    case when CountParts=1 then convert(varchar, cc.LastCheckDate,103)
    when CountParts=2 then (select stuff((select ' | ' + convert(varchar, lc.LastCheckDate,103)
    from LifeCycleMaster_cte lc
    where lc.Revision_ID=cc.Revision_ID
    order by lc.LastCheckDate
    for xml path ('')), 1, 3, ''))
    else 'Multi date' end LastCheckDate
    from
    counts_cte cc;

    /* faithful */
    with
    LifeCycleMaster_cte(Revision_ID, ZPartID, LastCheckDate) as (
    select 12, 10, '12/12/2015'
    union all
    select 15, 120, '12/01/2014'
    union all
    select 15, 130, '05/05/2016'
    union all
    select 20, 170, '09/03/2013'
    union all
    select 20, 200, '09/05/2016'
    union all
    select 20, 300, '09/08/2017'),
    cte AS
    (
    SELECT Revision_ID,
    COUNT(*) CountParts
    FROM LifeCycleMaster_cte
    GROUP BY Revision_ID
    )
    SELECT a.Revision_ID,
    a.CountParts,
    CASE WHEN a.CountParts < 3 THEN b.LastCheckDate
    ELSE 'Multi date'
    END LastCheckDate
    FROM cte a
    CROSS APPLY(SELECT STUFF((SELECT DISTINCT ' | ' + convert(varchar,b.LastCheckDate,103)
    FROM LifeCycleMaster_cte b
    WHERE b.Revision_ID=a.Revision_ID
    ORDER BY 1
    FOR XML PATH('')),1,3,'')) b(LastCheckDate);

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply