Embed a query and sub Query within a sql view (diff time in hours and min.)

I want to display for t开发者_开发技巧he user how long ago this record was added. (Like "0 Days 5 Hours 6 Min.") so i need to take the total number of min. and make the math. I did it in c# but now I need to do it in sql syntax, is there a easy way to do that?

UPDATE: Thanks to @Andomar answer here I got it to work as a separate query, Now I need to add it to a big view of the calls table..

 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

---

You can find the differences in hours like:

datediff(hour, startdate, enddate)

And the remainder of minutes:

datediff(minutes, startdate, enddate) % 60

Combined, it would look like:

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

To do conditional formatting, you could use a subquery:

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

I'll leave adding days as an exercise for the reader ;)

---

Put your date calculations in a user defined function. Something like this.

create function GetDateDiffStr(@FromDate datetime, @ToDate datetime) returns varchar(50)
as
begin
  declare @Ret varchar(50)
  select @Ret = 
         cast(DayDiff.Value as varchar(10))+case DayDiff.Value when 1 then ' day ' else ' days ' end+
         cast(HourDiff.Value as varchar(10))+case HourDiff.Value when 1 then ' hour ' else ' hours ' end+
         cast(MinutDiff.Value as varchar(10))+case MinutDiff.Value when 1 then ' minute ' else ' minutes ' end
  from        (select datediff(mi, @FromDate, @ToDate)) as TotalMinutes(Value)
  cross apply (select TotalMinutes.Value / (24*60)) as DayDiff(Value)
  cross apply (select (TotalMinutes.Value - DayDiff.Value*24*60)/60) as HourDiff(Value)
  cross apply (select TotalMinutes.Value - DayDiff.Value*24*60 - HourDiff.Value*60) as MinutDiff(Value)
  return @Ret
end

And use the function in the field list in your view.

select dbo.GetDateDiffStr(YourDateColumn, getdate()) as DateDiffStr
from YourTable

---

You'll need to specify what database system you are working on, but in sql server (and I assume many other database systems) you can create a function that has the same logic as what you have in your code and then you can call that in the sql query

---

CREATE VIEW your_view_name
AS
WITH
  ViewWithMinutes AS (
    SELECT
      TotalMinutes = DATEDIFF(mi, start_date_column, GETDATE()),
      other_columns
    FROM your_tables_and_joins
  ),
  ViewWithTimeParts AS (
    SELECT
      Minutes = TotalMinutes % 60,
      Hours   = TotalMinutes / 60 % 24,
      Days    = TotalMinutes / 60 / 24,
      the_other_columns
    FROM
  )
SELECT
  TimeOpen = CAST(Days    AS varchar) + CASE Days    WHEN 1 THEN ' day '    ELSE ' days '    END
           + CAST(Hours   AS varchar) + CASE Hours   WHEN 1 THEN ' hour '   ELSE ' hours '   END
           + CAST(Minutes AS varchar) + CASE Minutes WHEN 1 THEN ' minute ' ELSE ' minutes ' END
  the_other_columns
FROM ViewWithTimeParts