min and max startdate

Question

Hi Expert,

I wanted to calculate max date for prodstatus =5 - min date for prodstatus=4 and max date for prodstatus =4 - min date for prodstatus=2

Create table
CREATE TABLE [dbo].testdata ON [PRIMARY]
GO

Insert
insert into [dbo].[testdata]

values('Prod1000','873','7','2021-06-16', '11-05-2022', '4'),
('Prod1000','873', '18', '2022-05-12',NULL,'7'),
('Prod1000', '873', '19', '2022-05-12', NULL,'5'),
('Prod10000', '1254','4', '2022-03-28', '2022-03-30', '6'),
('Prod10000', '1254','1', '2022-03-3', NULL, '7'),
('Prod10002', '4427', '5', '2022-03-28', '2022-03-31', '4'),
('Prod10002', '4427', '8', '2022-04-01', NULL, NULL),
('Prod10002', '5603', '8', '2022-04-01', NULL, 2)

Expected output:

tried :
--create or alter view vStatusesChange
--as
WITH cte AS (SELECT ProdStatus,prodnumber, MIN(PrdStartDate) AS MinStartDate,
MAX(t.PrdStartDate) AS MaxStartDate

--select *
FROM dbo.testdata t WHERE t.ProdStatus between '4' and '5'
GROUP BY ProdStatus,prodnumber), cte2 AS (

SELECT

cte.MinStartDate AS MinStartDate,prodnumber, max(MaxStartDate) OVER (
ORDER BY CAST(ProdStatus as char)) AS MaxStartDate
FROM cte)

SELECT *, CASE WHEN cte2.MaxStartDate IS NULL THEN 0
ELSE DATEDIFF(DAY, MinStartDate, MaxStartDate) end AS '4_to5'
FROM cte2
GO

am expecting view format

Answer 1

Suggestion pls