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_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
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