A community member has associated this post with a similar question:
Duration between min and max date

Only moderators can edit this content.

min and max startdate

Shambhu Rai 1,406 Reputation points
2022-05-20T13:28:47.347+00:00

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:

204162-image.png

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,713 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,799 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,453 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Naomi 7,361 Reputation points
    2022-05-20T14:14:04.757+00:00

    Try: 

    SELECT t.Prodnumber, t.Prodid, t.ProdStatusId AS ProdStatusId, t.PrdStartDate AS MinPrdStartDate, 
MAX(x.PrdStartDate) AS MaxPrdStartDate, 
MAX(CASE WHEN X.ProdStatus = '5' THEN DATEDIFF(DAY, t.PrdStartDate, X.PrdStartDate) ELSE 0 END) AS [4_5],
MAX(CASE WHEN X.ProdStatus = '2' THEN DATEDIFF(DAY, t.PrdStartDate, X.PrdStartDate) ELSE 0 END) AS [4_2]

FROM dbo.testdata t 
OUTER APPLY (SELECT * FROM dbo.testdata x WHERE x.Prodnumber = t.Prodnumber --AND t.Prodid = x.Prodid
and x.prodstatus IN ('5','2')) X
WHERE t.prodstatus = '4'
GROUP BY t.Prodnumber, t.Prodid, t.ProdStatusId, t.PrdStartDate
    1 person found this answer helpful.
  2. Naomi 7,361 Reputation points
    2022-05-20T13:54:42.463+00:00

    Check my last answer in your other thread
    https://learn.microsoft.com/en-us/answers/questions/856244/duration-between-min-and-max-date.html

    Convert to the desired format.

  3. Naomi 7,361 Reputation points
    2022-05-20T15:39:42.73+00:00

    Why did you remove the filter? It was there for the exact reason.

  4. Shambhu Rai 1,406 Reputation points
    2022-05-20T18:53:37.863+00:00

    i do not need a filter , it should take only 1 prodnumber to show for each 4_5 4_2 like leadstatus partition by leadnumber which should get one row

    0 comments
  5. Shambhu Rai 1,406 Reputation points
    2022-05-20T19:37:01.9+00:00

    Any suggestion from any other team

    0 comments