Suggestion pls
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
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:
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
6 answers
Sort by: Newest
-
-
-
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
-
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.
-
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