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 (although it would not match output exactly as your dataset doesn't match expected result):

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 ProdId, ProdStatusId, MIN(PrdStartDate) AS MinStartDate, 
MAX(t.PrdStartDate) AS MaxStartDate

FROM dbo.testdata t

GROUP BY ProdId, ProdStatusId), cte2 AS (

SELECT cte.Prodid, 
CONCAT(TRIM(ProdStatusId), ' to ' + CAST(LEAD(CAST(ProdStatusId as INT)) 
OVER (PARTITION BY cte.Prodid ORDER BY CAST(ProdStatusId as INT)) AS VARCHAR(10))) AS [Status],
cte.MinStartDate AS MinStartDate, LEAD(MaxStartDate) OVER (PARTITION BY ProdId
ORDER BY CAST(ProdStatusId 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

Answer 3

Hi Expert
How can we add view in above query

Answer 4

create or alter view vStatusesChange
as
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]%'

GO

Answer 5

Hi Expert,

it is improper to use lead function it just requires min and max date based on prodstatus and columns required not rows as expected results