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

  • Question

  • 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
    

    Saturday, January 18, 2020 12:38 AM

Answers

  • Hi engahmedbarbary,

    Please check following script.

    ;with cte as (
    select distinct Revision_ID,
    count(*)over(partition by [Revision_ID] )CountParts , 
    case when count(*)over(partition by [Revision_ID])>=3 then 'Multi date' else convert(varchar(20),LastCheckDate,101) end LastCheckDate
    from [LifeCycleMaster])
    SELECT B.Revision_ID,B.CountParts,LEFT(LastCheckDate,LEN(LastCheckDate)-1) as LastCheckDate 
    FROM (
    	SELECT Revision_ID,CountParts,
    		(SELECT LastCheckDate+' |' FROM cte 
    		  WHERE Revision_ID=A.Revision_ID and CountParts=A.CountParts
    		  FOR XML PATH('')) AS LastCheckDate
    	FROM cte A 
    	GROUP BY  Revision_ID,CountParts) B 
    /*
    Revision_ID          CountParts  LastCheckDate
    -------------------- ----------- ---------------------------------------
    12                   1           12/12/2015 
    15                   2           05/05/2016 |12/01/2014 
    20                   3           Multi date 
    */
    
     --------starting with SQL Server  2017
    ;with cte as (
    select distinct Revision_ID,
    count(*)over(partition by [Revision_ID] )CountParts , 
    case when count(*)over(partition by [Revision_ID])>=3 then 'Multi date' else convert(varchar(20),LastCheckDate,101) end LastCheckDate
    from [LifeCycleMaster])
    select Revision_ID,CountParts,STRING_AGG (LastCheckDate,' |') as LastCheckDate
    from cte
    group by  Revision_ID,CountParts
    /*
    Revision_ID          CountParts  LastCheckDate
    -------------------- ----------- ---------------------------------------
    12                   1           12/12/2015 
    15                   2           05/05/2016 |12/01/2014 
    20                   3           Multi date 
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 20, 2020 3:31 AM

All replies

  • select Revision_ID,count(*) CountParts ,
    Case 
    when count(*)=1 then Format(min(LastCheckDate),'MM/dd/yyyy')
    when count(*)=2 then concat(Format(min(LastCheckDate),'MM/dd/yyyy'),' | ',Format(min(LastCheckDate),'MM/dd/yyyy'))
    when count(*)>2 then 'Multi dates'
    else ''
    end LastCheckDate
    from LifeCycleMaster
    group by Revision_ID

    Saturday, January 18, 2020 1:26 AM
    Moderator
  • Hi engahmedbarbary,

    Please check following script.

    ;with cte as (
    select distinct Revision_ID,
    count(*)over(partition by [Revision_ID] )CountParts , 
    case when count(*)over(partition by [Revision_ID])>=3 then 'Multi date' else convert(varchar(20),LastCheckDate,101) end LastCheckDate
    from [LifeCycleMaster])
    SELECT B.Revision_ID,B.CountParts,LEFT(LastCheckDate,LEN(LastCheckDate)-1) as LastCheckDate 
    FROM (
    	SELECT Revision_ID,CountParts,
    		(SELECT LastCheckDate+' |' FROM cte 
    		  WHERE Revision_ID=A.Revision_ID and CountParts=A.CountParts
    		  FOR XML PATH('')) AS LastCheckDate
    	FROM cte A 
    	GROUP BY  Revision_ID,CountParts) B 
    /*
    Revision_ID          CountParts  LastCheckDate
    -------------------- ----------- ---------------------------------------
    12                   1           12/12/2015 
    15                   2           05/05/2016 |12/01/2014 
    20                   3           Multi date 
    */
    
     --------starting with SQL Server  2017
    ;with cte as (
    select distinct Revision_ID,
    count(*)over(partition by [Revision_ID] )CountParts , 
    case when count(*)over(partition by [Revision_ID])>=3 then 'Multi date' else convert(varchar(20),LastCheckDate,101) end LastCheckDate
    from [LifeCycleMaster])
    select Revision_ID,CountParts,STRING_AGG (LastCheckDate,' |') as LastCheckDate
    from cte
    group by  Revision_ID,CountParts
    /*
    Revision_ID          CountParts  LastCheckDate
    -------------------- ----------- ---------------------------------------
    12                   1           12/12/2015 
    15                   2           05/05/2016 |12/01/2014 
    20                   3           Multi date 
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 20, 2020 3:31 AM