Duration between min and max date

Question

Hi Expert,

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

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:

Answer 1

Hi ShambhuRai

I hope try to bring an answer with this code. For your information, your screen of expected output hasn't same values as your script to create tables for the test.

select 
    b.min_start_date,
    b.max_start_date,
    DATEDIFF(DAY, max_start_date, min_start_date) as [4-5],
    0 as [5-6]
from
(
    select
        (
            select
                min(testdata.PrdStartDate) as min_start_date
            from
                SKARABEE.dbo.testdata
            where
                testdata.prodstatus = 4
        ) as min_start_date,
        (
            select
                max(testdata.PrdStartDate) as min_start_date
            from
                SKARABEE.dbo.testdata
            where
                testdata.prodstatus = 5
        ) as max_start_date
) as B

union

select 
    b.min_start_date,
    b.max_start_date,
    0 as [4-5],
    DATEDIFF(DAY, max_start_date, min_start_date) as [5-6]
from
(
    select
        (
            select
                min(testdata.PrdStartDate) as min_start_date
            from
                SKARABEE.dbo.testdata
            where
                testdata.prodstatus = 5
        ) as min_start_date,
        (
            select
                max(testdata.PrdStartDate) as min_start_date
            from
                SKARABEE.dbo.testdata
            where
                testdata.prodstatus = 6
        ) as max_start_date
) as B

Best Regards

Answer 2

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

Answer 3

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]%'

Answer 4

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.

Answer 5

Suggestion pls