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

Try:

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), cte3 AS (

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

SELECT MinStartDate, MaxStartDate,
ISNULL([2 to 4], 0) AS [2 to 4], 
ISNULL([4 to 5],0) AS [4 to 5], 
ISNULL([5 to 6],0) AS [5 to 6],
ISNULL([6 to 7],0) AS [6 to 7]
FROM cte3 PIVOT (MAX(duration) FOR [Status] IN ([2 to 4], [4 to 5], [5 to 6], [6 to 7])) pvt

This is using static pivot. Dynamic PIVOT is an advanced topic and I don't have time right now to convert this to dynamic pivot.

Answer 3

Suggestion pls

Answer 4

suggestion please

Answer 5

Hi，@Shambhu Rai
Welcome to Microsoft T-SQL Q&A Forum!

Improvements based on naomi's answer, Please try this：

WITH cte1 AS   
(  
    SELECT CONCAT(TRIM(ProdStatus), ' to ' + CAST(LEAD(CAST(ProdStatus as INT))   
         OVER (ORDER BY CAST(ProdStatus as INT)) AS VARCHAR(10))) AS [Status],  
		 case when A.MinStartDate<=lead(A.MinStartDate,1)OVER (ORDER BY CAST(ProdStatus as INT))and A.MinStartDate<=a.MaxStartDate   
		 then A.MinStartDate else lead(A.MinStartDate,1)OVER (ORDER BY CAST(ProdStatus as INT))  
		 end as MinStartDate,  
      case when  A.maxStartDate>=LEAD(A.MaxStartDate,1) OVER (ORDER BY CAST(ProdStatus as INT))and A.MaxStartDate>=A.MinStartDate  
	  then A.MaxStartDate else LEAD(A.MaxStartDate,1)OVER (ORDER BY CAST(ProdStatus as INT))  
	  end as  MaxStartDate  
 FROM   
 (SELECT ProdStatus, MIN(PrdStartDate) AS MinStartDate,MAX(t.PrdStartDate) AS MaxStartDate  
    FROM dbo.testdata t  
    GROUP BY ProdStatus)A  
),cte2 as  
(  
	 SELECT MinStartDate, MaxStartDate, CASE WHEN cte1.MaxStartDate IS NULL THEN 0   
   ELSE DATEDIFF(DAY, MinStartDate, MaxStartDate) end AS Duration, cte1.Status  
   FROM cte1 WHERE [cte1].[Status] LIKE '[1-9]%to%[1-9]%'  
)  
 SELECT MinStartDate, MaxStartDate    ,ISNULL([2 to 4], 0) AS [2 to 4], ISNULL([4 to 5],0) AS [4 to 5],   
        ISNULL([5 to 6],0) AS [5 to 6],ISNULL([6 to 7],0) AS [6 to 7]  
 FROM cte2 PIVOT (MAX(duration) FOR [Status] IN ([2 to 4], [4 to 5], [5 to 6], [6 to 7])) pvt  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.