January 18, 2020 at 12:50 am
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_IDZPartIDLastCheckDate
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 112/12/2015
15 205/05/2016 |12/01/2014
20 3Multi date
January 18, 2020 at 3:21 am
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
January 18, 2020 at 3:27 am
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;
January 18, 2020 at 3:34 am
;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)
January 18, 2020 at 1:19 pm
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
January 18, 2020 at 2:26 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy