sql-server-2008 - 在 SQL View 中嵌入查询和子查询(以小时和分钟为单位的差异时间)

Question

我想向用户显示此记录是多久前添加的。 (比如“0天5小时6分钟”)所以我需要计算总分钟数。并进行数学计算。我是用 C# 做的，但现在我需要用 sql 语法来做，有没有简单的方法可以做到这一点？

更新:感谢@Andomar在这里的回答，我让它作为一个单独的查询工作，现在我需要将它添加到调用表的大 View 中..

 select case when days > 0 then CAST(Days as varchar(6)) +  ' Days ' else +  
        case when hours > 1 and hours < 24 then cast(hours as varchar(6)) + ' hours'
         when hours > 1 and hours < 24  then '1 hour'
         else '' 
    end + ' ' +
    case when minutes > 1 and minutes < 60 then cast(minutes as varchar(6)) + ' minutes'
         when minutes = 1 then '1 min.'
         else '' 
    end 
    end as TimeOpen
    From   (
    select  datediff(HH, dbo.Calls.CallDate, getdate()) as hours
    ,       datediff(MI, dbo.Calls.CallDate,getdate()) % 60 as minutes
    ,       datediff(D, dbo.Calls.CallDate, getdate()) as Days
    from    calls where Status <> 7 and Status <> 4
    ) as SubQuery

Answer 1

您可以发现小时数的差异，例如:

datediff(hour, startdate, enddate)

剩下的分钟:

datediff(minutes, startdate, enddate) % 60

结合起来，它看起来像:

select  cast(datediff(hour, startdate, enddate) as varchar(20)) + ' hours ' + 
        cast(datediff(minutes, startdate, enddate) % 60 as varchar(20) + ' min.'

要进行条件格式化，您可以使用子查询:

select  case when hours > 1 then cast(hours as varchar(6)) + ' hours'
             when hours > 1 then '1 hour'
             else '' 
        end + ' ' +
        case when minutes > 1 then cast(minutes as varchar(6)) + ' minutes'
             when minutes = 1 then '1 minute'
             else '0 minutes'
        end
from    (
        select  datediff(hour, startdate, enddate) as hours
        ,       datediff(minutes, startdate, enddate) % 60 as minutes
        from    YourTable
        ) as SubQueryAlias

我将添加天数作为读者的练习；)