question

ShambhuRai-4099 avatar image
0 Votes"
ShambhuRai-4099 asked ShambhuRai-4099 commented

Duration between min and max date

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](
[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
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:

203627-image.png


sql-server-generalsql-server-transact-sqlazure-sql-databasesql-server-analysis-services
image.png (4.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EricMILA-7882 avatar image
1 Vote"
EricMILA-7882 answered ShambhuRai-4099 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry i overlooked your query. it perfectly works!! thanks

0 Votes 0 ·
wilkovdv avatar image
1 Vote"
wilkovdv answered ShambhuRai-4099 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

it is showing wrong output

203725-image.png


0 Votes 0 ·
image.png (156.3 KiB)
ShambhuRai-4099 avatar image
0 Votes"
ShambhuRai-4099 answered ShambhuRai-4099 edited

it is giving wrong output and also remaining value should be zero instead of null

203727-image.png



also creating view getting error
Msg 111, Level 15, State 1, Line 18
'CREATE VIEW' must be the first statement in a query batch.


image.png (156.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ShambhuRai-4099 avatar image
0 Votes"
ShambhuRai-4099 answered

Suggestion pls

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
1 Vote"
NaomiNNN answered NaomiNNN edited

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]%'
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ShambhuRai-4099 avatar image
0 Votes"
ShambhuRai-4099 answered BertZhoumsft-7490 commented

Hi Expert
How can we add view in above query

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,@ShambhuRai-4099
Put cet in view, Like This:

 USE AdventureWorks;
 GO
 CREATE VIEW vwCTE AS
 WITH cte (EmployeeID, ManagerID, Title) as (
   SELECT EmployeeID, ManagerID, Title...

Bert Zhou


0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered ShambhuRai-4099 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Hi Expert,
it is in the rows .... i need it column... 4to 5 and 6to 5 should be column not rows

204053-image.png


0 Votes 0 ·
image.png (43.4 KiB)
ShambhuRai-4099 avatar image
0 Votes"
ShambhuRai-4099 answered NaomiNNN commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Converting rows to columns is possible, but in this case it will be dynamic SQL unless there is limited number of combinations and you know them all in advance. If the number of columns is limited, then you can either use PIVOT or case based pivot to transpose your result. Can you post what exactly would you expect and if the column names are predefined or not?

0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered ShambhuRai-4099 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Again output is wrong

204181-image.png


expected:

204116-image.png


0 Votes 0 ·
image.png (42.1 KiB)
image.png (10.1 KiB)