SQL OVER(Partition) issue - selecting a subset

Question

I'm trying to get a specific subset of data using "over(partition)" syntax. I've created sample data to illustrate. Running the following CTE results in the a small example result set.

I need to calculate 2 date ranges using the following definitions/pseudocode 1: Days to close = CloseDate where stat = 'Closed' minus opendate partitioned by problem 2: Days to solve = "ClosedDate where stat = 'Solved' minus opendate partitioned by problem.

I can get #1 using the over(partition) syntax, but I cannot figure out #2.

with cte as 
(
select 114110712007835 as 'SRNumber', 214110712007835004 as ProblemNumber, 'Open' as 'Stat',    314110712007835004001 as TaskNumber, convert(datetime, '2015-03-02 19:47:43',120) as OpenDate,  convert(datetime, '2015-03-03 19:36:37',120) as CloseDate union 
select 114110712007835 as 'SRNumber',   214110712007835004 as ProblemNumber, 'Investigate' as 'stat',   314110712007835004002 as TaskNumber, convert(datetime, '2015-03-04 00:29:13',120)  as OpenDate, convert(datetime, '2015-03-05 19:36:34',120) as CloseDate  union 
select 114110712007835 as 'SRNumber',   214110712007835004 as ProblemNumber, 'Solve' as 'stat', 314110712007835004003 as TaskNumber, convert(datetime, '2015-03-06 18:17:13',120)  as OpenDate, convert(datetime, '2015-03-07 13:07:31',120) as CloseDate  union
select 114110712007835 as 'SRNumber',   214110712007835004 as ProblemNumber, 'Close' as 'stat', 315032012542588001001 as TaskNumber, convert(datetime, '2015-03-08 15:24:34',120)  as OpenDate, convert(datetime, '2015-03-09 15:15:42',120) as CloseDate  union
select 114110712007835 as 'SRNumber',   215032012542588001 as ProblemNumber, 'Open' as 'stat',  315032012542588001002 as TaskNumber, convert(datetime, '2015-04-20 20:05:48',120)  as OpenDate, convert(datetime, '2015-04-21 03:24:24',120) as CloseDate  union
select 114110712007835 as 'SRNumber',   215032012542588001 as ProblemNumber, 'Investigate' as 'stat',   315032012542588001003 as TaskNumber, convert(datetime, '2015-04-22 18:55:03',120)  as OpenDate, convert(datetime, '2015-04-23 03:24:28',120) as CloseDate  union
select 114110712007835 as 'SRNumber',   215032012542588001 as ProblemNumber, 'Solve' as 'stat', 315032012542588001004 as TaskNumber, convert(datetime, '2015-04-24 13:35:24',120)  as OpenDate, convert(datetime, '2015-04-27 02:24:31',120) as CloseDate union
select 114110712007835 as 'SRNumber',   215032012542588001 as ProblemNumber, 'Close' as 'stat', 315032012542588001004 as TaskNumber, convert(datetime, '2015-04-26 13:35:24',120)  as OpenDate, convert(datetime, '2015-04-29 03:24:31',120) as CloseDate
)

select srnumber, problemnumber, stat, opendate, closedate, --min(opendate) over(partition by problemnumber) as MinDate, max(closedate) over(partition by problemnumber) as MaxDate

case 
when stat = 'Solve' then datediff(mi,min(opendate) over(partition by problemnumber),max(closedate) over(partition by problemnumber)) 
else NULL end as Days_to_Solve

,datediff(mi,min(opendate) over(partition by problemnumber),max(closedate) over(partition by problemnumber))  as Days_to_Close


from cte
order by problemnumber asc, opendate asc
go   

Answer 1

My results very closely resemble user1221684's, but I use windows functions instead of a subquery. So my answer should run a little more efficiently and it's simpler. Check it out:

SELECT  *,
        DATEDIFF(   MI, --this is minutes. For days switch "MI" to "DAY"
                    MIN(CASE WHEN [Stat] = 'Open'  THEN OpenDate END) OVER (PARTITION BY problemNumber),
                    MIN(CASE WHEN [Stat] = 'Solve' THEN CloseDate END) OVER (PARTITION BY problemNumber)
                ) AS Minutes_To_Solve
        ,
        DATEDIFF(   MI, --this is minutes. For days switch "MI" to "DAY"
                    MIN(CASE WHEN [stat] = 'Open' THEN OpenDate  END) OVER (PARTITION BY problemNumber),
                    MIN(CASE WHEN [Stat] = 'Close'  THEN CloseDate END) OVER (PARTITION BY problemNumber)
                ) AS Minutes_To_Close
FROM CTE
ORDER BY OpenDate 

Answer 2

A subquery could get you the result you need.

SELECT  srnumber ,
            problemnumber ,
            stat ,
            opendate ,
            closedate , --min(opendate) over(partition by problemnumber) as MinDate, max(closedate) over(partition by problemnumber) as MaxDate
            ( CASE WHEN stat = 'Solve'
                   THEN ( SELECT TOP 1
                                    DATEDIFF(mi, MIN(opendate) OVER ( ),
                                                              MAX(closedate) OVER ( ))
                          FROM      cte c
                          WHERE     c.problemnumber = cte.problemnumber
                                    AND c.CloseDate <= cte.CloseDate
                        )
                   ELSE NULL
              END ) AS Days_to_Solve ,
            DATEDIFF(mi, MIN(opendate) OVER ( PARTITION BY problemnumber ),
                     MAX(closedate) OVER ( PARTITION BY problemnumber )) AS Days_to_Close
    FROM    cte
    ORDER BY problemnumber ASC ,
            opendate ASC