Duration between min and max date

And if you don't care about ProdNumber when statuses change, then it would be (see solution). If you wanted difference between min(StartDate) and max(EndDate) for next status, then do changes accordingly

DROP TABLE IF EXISTS testdata;

CREATE TABLE [dbo].[testdata](
[Prodnumber] [nchar](20) NULL,
[Prodid] [nchar](20) NULL,
[Prodstatusid] [nchar](20) NULL,
[PrdStartDate] [date] NULL,
[PrdEndDate] [date] NULL,
[prodstatus] [nchar](23) NULL
) ON [PRIMARY]
GO


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)


;WITH cte AS (SELECT ProdStatus, MIN(PrdStartDate) AS MinStartDate, 
MAX(t.PrdStartDate) AS MaxStartDate

FROM dbo.testdata t

GROUP BY ProdStatus), cte2 AS (

SELECT 
CONCAT(TRIM(ProdStatus), ' to ' + CAST(LEAD(CAST(ProdStatus as INT)) 
OVER (ORDER BY CAST(ProdStatus as INT)) AS VARCHAR(10))) AS [Status],
cte.MinStartDate AS MinStartDate, LEAD(MaxStartDate) OVER (
ORDER BY CAST(ProdStatus as INT)) AS MaxStartDate
FROM cte)

SELECT *, CASE WHEN cte2.MaxStartDate IS NULL THEN 0 
ELSE DATEDIFF(DAY, MinStartDate, MaxStartDate) end AS Duration
FROM cte2 WHERE [cte2].[Status] LIKE '[1-9]%to%[1-9]%'

Your question is not completly clear. But I quess you want the duration between the max StartDate of the current prodStatus and the min Startdate of the previous prodStatus.

In that case you case use something like this:
DROP TABLE IF EXISTS #TEMP_testdata
DROP TABLE IF EXISTS #TEMP_testdataLag

--CREATE TEMP TABLE FOR EASE OF USE
SELECT prodstatus, 
 MAX(PrdStartDate) as MaxDate, 
 Min(PrdStartDate) as MinDate
INTO #TEMP_testdata
FROM [dbo].[testdata]
GROUP BY prodstatus

SELECT prodstatus,
 MaxDate,
 LAG(prodstatus) OVER (ORDER BY prodstatus) as PreviousStatus,
 LAG(MinDate) OVER (ORDER BY prodstatus) as MinDatePreviousStatus,
 DATEDIFF(SECOND, MaxDate, LAG(MinDate) OVER (ORDER BY prodstatus)) as Duration,
 RTRIM(LAG(prodstatus) OVER (ORDER BY prodstatus)) + '-' + prodstatus as ColumnHeader
INTO #TEMP_testdataLag
FROM #TEMP_testdata


SELECT MinDatePreviousStatus AS [Min Start date],
 MaxDate AS [Max Start date],
 [4-5], 
 [5-6], 
 [6-7]
FROM (
 SELECT *
 FROM #TEMP_testdataLag
 WHERE PreviousStatus IS NOT NULL
 ) AS SourceTable  
PIVOT  
(  
  AVG(Duration)  
  FOR ColumnHeader IN ([4-5], [5-6],[6-7])  
) AS PivotTable

For your information, the expected result will not be the same because the insert script doesnt have all the dates

Suggestion pls

it is giving wrong output and also remaining value should be zero instead of null

also creating view getting error
Msg 111, Level 15, State 1, Line 18
'CREATE VIEW' must be the first statement in a query batch.