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