Only return first matches per Id and calculate difference of dates in minutes

  • SQL_Kills

    Hall of Fame

    Points: 3145

    Hi,

     

    I have a the below table, from the result set I only want to see records where the StatusName has the values Draft and Submitted, and to return the first date based on the Id.

     

    I have included a Select Statement of the final result set I want it to return.

     

    Thanks

     

    CREATE TABLE [dbo].[RequestStatus](
    [Id] [nvarchar](50) NOT NULL,
    [StatusName] [nchar](10) NULL,
    [CreatedOn] [datetime] NULL
    ) ON [PRIMARY]

    GO


    insert [dbo].[RequestStatus] values ('ABC001','Draft','2020-09-28 10:42:20.000')
    insert [dbo].[RequestStatus] values ('ABC001','Draft','2020-09-28 11:42:20.000')
    insert [dbo].[RequestStatus] values ('ABC001','Submitted','2020-09-28 13:42:20.000')
    insert [dbo].[RequestStatus] values ('ABC001','Submitted','2020-09-28 13:43:20.000')
    insert [dbo].[RequestStatus] values ('ABC002','Draft','2020-09-28 10:42:20.000')
    insert [dbo].[RequestStatus] values ('ABC002','Finished','2020-09-28 20:42:20.000')
    insert [dbo].[RequestStatus] values ('ABC003','Unknown','2020-09-28 10:42:20.000')
    insert [dbo].[RequestStatus] values ('ABC004','Submitted','2020-09-28 15:42:20.000')
    insert [dbo].[RequestStatus] values ('ABC005','Draft','2020-09-28 10:42:20.000')
    insert [dbo].[RequestStatus] values ('ABC005','Submitted','2020-09-28 12:42:20.000')


    Select
    1 as Id
    ,'2020-09-28 10:42:20.000' AS Draft
    ,'2020-09-28 13:42:20.000' AS Submitted
    ,DATEDIFF(mi,'2020-09-28 10:42:20.000','2020-09-28 13:42:20.000') AS MinDiff
    UNION
    Select
    5 as Id
    ,'2020-09-28 10:42:20.000' AS Draft
    ,'2020-09-28 12:42:20.000' AS Submitted
    ,DATEDIFF(mi,'2020-09-28 10:42:20.000','2020-09-28 12:42:20.000') AS MinDiff
  • ScottPletcher

    SSC Guru

    Points: 98633

     

    SELECT
    Id,
    MIN(CASE WHEN StatusName = 'Draft' THEN CreatedOn ELSE NULL END) AS Draft,
    MIN(CASE WHEN StatusName = 'Submitted' THEN CreatedOn ELSE NULL END) AS Submitted,
    DATEDIFF(MINUTE,
    MIN(CASE WHEN StatusName = 'Draft' THEN CreatedOn ELSE NULL END),
    MIN(CASE WHEN StatusName = 'Submitted' THEN CreatedOn ELSE NULL END)) AS MinDiff
    FROM (
    SELECT
    *, ROW_NUMBER() OVER(PARTITION BY Id, StatusName ORDER BY CreatedOn) AS row_num
    FROM dbo.RequestStatus
    WHERE StatusName IN ('Draft', 'Submitted')
    ) AS query1
    WHERE row_num = 1
    GROUP BY Id
    HAVING COUNT(*) = 2

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Steve Collins

    Ten Centuries

    Points: 1121

    Here's an alternate way of writing the query.  I've been experimenting with windowing functions and I'm pretty sure, but not 100% certain, this is equivalent to Scott's query.  I would use his query instead of this tho because I'm more confident in that approach.

    ;with rs_cte(Id, StatusName, CreatedOn, Draft, Submitted) as (
    select *, min(case when StatusName='Draft' then CreatedOn else null end) over
    (partition by Id order by CreatedOn),
    min(case when StatusName='Submitted' then CreatedOn else null end) over
    (partition by Id order by CreatedOn)
    from #RequestStatus
    where StatusName in('Draft', 'Submitted'))
    select Id, Draft, Submitted, datediff(mi, Draft, Submitted) MinDiff
    from rs_cte
    where (CreatedOn=Draft
    or CreatedOn=Submitted)
    and Submitted is not null
    and Draft is not null;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply