How to make group by Revision_ID and when repeated display l

ahmed elbarbary

6y1k

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

ahmed elbarbary6y ago
but until now problem not solved on multi date
because according to last thread if i have 3 date as following
if revision repeated 3 time with same date
25/08/2020 25/08/2020 25/08/2020

in case above it show multidate
but i need it 25/08/2020
because it is repeated but if data as below
25/08/2020 25/08/2019 25/08/2018
it must show multidate on lastcheckdate because date is not similar
ahmed elbarbary6y ago
thank you for reply
it also must done on case of multidate
Jignesh Kumar6y ago
Hello,

Try this one and still facing an issue then Please more data with your different case,
select revision_id,count(revision_id) as CountParts ,
CASE WHEN count(revision_id)>2 then 'Multi Date'
ELSE STUFF((SELECT  N', ' + CAST(convert(varchar,lc.LastCheckDate,101) AS VARCHAR)
  FROM dbo.[LifeCycleMaster] AS lc
  where lc.revision_id= t.revision_id
  group BY lc.LastCheckDate
  ORDER BY lc.LastCheckDate  -- only change
  FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
END  as LastCheckDate
from [LifeCycleMaster] t group by revision_id
ahmed elbarbary6y ago
thank you for reply
I do that
select revision_id,count(revision_id) as CountParts ,
CASE WHEN count(revision_id)>2 then 'Multi Date'
ELSE (SELECT SUBSTRING(
(SELECT ' | ' + CAST(convert(varchar,LastCheckDate,101) AS VARCHAR) FROM [LifeCycleMaster] where revision_id= t.revision_id order by LastCheckDate desc
FOR XML PATH('')), 3,50) AS LastCheckDate)
END  as LastCheckDate
from [LifeCycleMaster] t group by revision_id
but proplem if i have two date similar it make as following
25/08/2020 | 25/08/2020 this is according to my case is wrong
I need distinct date it must be as following
25/08/2020 | 25/07/2020 correct
in case i have two dates to revisionid
i will get second not repeated
Jignesh Kumar6y ago
Hey,

Please try blow query,
select revision_id,count(revision_id) as CountParts ,
CASE WHEN count(revision_id)>2 then 'Multi Date'
ELSE (SELECT SUBSTRING(
(SELECT ' | ' + CAST(convert(varchar,LastCheckDate,101) AS VARCHAR) FROM [LifeCycleMaster] where revision_id= t.revision_id order by LastCheckDate desc
FOR XML PATH('')), 3,50) AS LastCheckDate)
END  as LastCheckDate
from [LifeCycleMaster] t group by revision_id
Veerendra Annigere6y ago
SELECT Revision_ID , Count(Revision_ID) as CountParts ,LastCheckDate = STUFF ((SELECT ' | ' + LastCheckDate

FROM [LifeCycleMaster] b

WHERE b.Revision_ID = a.Revision_ID

FOR XML PATH('')), 1, 2, ' ')

FROM [LifeCycleMaster] a

GROUP BY [Revision_ID]