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
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) A socialist is someone who will give you the shirt off *someone else's* back.
September 29, 2020 at 1:44 pm
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply