Calculating the minutes between dates

Question

I need help converting the following excel logic below to SQL subquery. Your help is very much appreciated.

Column C

• Row 2) 10/1/12 4:24 PM
• Row 3) 10/1/12 4:25 PM
• Row 4) 10/1/12 4:26 PM
• Row 5) 10/1/12 7:27 PM

Accumulated Minutes

• Row 2) 2
• Row 3) 1
• Row 4) 1
• Row 5) 45

Excel Formula

• Row 2) =IF((C3-C2)>45,45,(C3-C2)*1440)
• Row 3) =IF((C4-C3)>45,45,(C4-C3)*1440)
• Row 4) =IF((C5-C4)>45,45,(C5-C4)*1440)
• Row 5) =IF((C6-C5)>45,45,(C6-C5)*1440)

Answer 1

How about this:

WITH CTE(RowNumber, Date) AS(
select 1, '2012-01-10 16:22' union
select 2, '2012-01-10 16:24' union
select 3, '2012-01-10 16:25' union
select 4, '2012-01-10 16:26' union
select 5, '2012-01-10 19:27'
)
select b.Date, IIF(DATEDIFF(minute, a.Date, b.Date) > 45, 45, DATEDIFF(minute, a.Date, b.Date)) as AccumulatedMinutes
from CTE b left outer join CTE a on a.RowNumber = b.RowNumber - 1

It returns:

2012-01-10 16:22    NULL
2012-01-10 16:24    2
2012-01-10 16:25    1
2012-01-10 16:26    1
2012-01-10 19:27    45