Field in SSRS report should be shown as (Days, Hours)

• VSSGeorge

  SSCrazy Eights

  Points: 8146

  More actions

  January 11, 2012 at 3:30 am

  #391285

  Hi

  I have a requirement where a field in SSRS report should be shown as (Days, Hours)

  Ie. If it is 50hours it should be shown as 2days 2hours.

  Any help is appreciated.

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  January 11, 2012 at 5:31 am

  #1431726

  In SQL, something like this -

  BEGIN TRAN

  --Sample data

  CREATE TABLE timing (timing INT)

  INSERT INTO timing

  SELECT *

  FROM (VALUES(50),(24),(20),(1),(16),(360),(14000))a(timing)

  SELECT timing,

  CASE WHEN (timing / 24) = 1

  THEN CONVERT(VARCHAR(6),(timing / 24)) + 'day '

  WHEN (timing / 24) > 1

  THEN CONVERT(VARCHAR(6),(timing / 24)) + 'days '

  ELSE '' END +

  CASE WHEN (timing - ((timing / 24) * 24)) = 1

  THEN CONVERT(VARCHAR(2),timing - ((timing / 24) * 24)) + 'hour'

  WHEN (timing - ((timing / 24) * 24)) > 1

  THEN CONVERT(VARCHAR(2),timing - ((timing / 24) * 24)) + 'hours'

  ELSE '' END

  FROM timing

  ROLLBACK

  The syntax for SSRS is a little different, but the principle is the same.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---