none
Adding a Dollar $ to calculation from a CASE statement

    Question

  • --To compute the cost for fiscal year
    --Cost =
    case
    when (tblbookin.bookindt between '2013-10-01 00:00:00.000' AND '2014-09-30 23:59:59.999') 
    then ((DATEDIFF(DAY,tblbookin.bookindt,tblrelease.releaseddt)) * 62.46)
    when (tblbookin.bookindt between '2014-10-01 00:00:00.000' AND '2015-09-30 23:59:59.999') 
    then ((DATEDIFF(DAY,tblbookin.bookindt,tblrelease.releaseddt)) * 63.11)
    end as [Cost]

    The above works fine, I get the results. But I would like to add a dollar sign to my [Cost]. I have used the following as stand alone for a field:

    '$'+CAST(CONVERT(DECIMAL(30,2),yourcolumnname) AS varchar(20)) as [column name],

    How can I code the '$' within my CASE statement?

    Thanks,

    jr7138

    Thursday, August 28, 2014 7:59 PM

Answers

  • You must convert your value to a char data type.

    HOWEVER, you should be doing this in your presentation layer, not in your query.

    '$' + CONVERT(VARCHAR,case
    when (tblbookin.bookindt between '2013-10-01 00:00:00.000' AND '2014-09-30 23:59:59.999') 
    then ((DATEDIFF(DAY,tblbookin.bookindt,tblrelease.releaseddt)) * 62.46)
    when (tblbookin.bookindt between '2014-10-01 00:00:00.000' AND '2015-09-30 23:59:59.999') 
    then ((DATEDIFF(DAY,tblbookin.bookindt,tblrelease.releaseddt)) * 63.11)
    end)

    Thursday, August 28, 2014 8:01 PM
  • You can also use the FORMAT command starting with SQL Server 2012:

    SELECT Dollar=FORMAT(123456.78,'c2','en-US'); -- $123,456.78


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Thursday, August 28, 2014 8:16 PM
    Moderator
  •  
    case
    when (tblbookin.bookindt between '2013-10-01 00:00:00.000' AND '2014-09-30 23:59:59.999') 
    then '$'+Cast((DATEDIFF(DAY,tblbookin.bookindt,tblrelease.releaseddt)) * 62.46 as varchar(20))
    when (tblbookin.bookindt between '2014-10-01 00:00:00.000' AND '2015-09-30 23:59:59.999') 
    then '$'+Cast((DATEDIFF(DAY,tblbookin.bookindt,tblrelease.releaseddt)) * 63.11  as varchar(20))
    end as [Cost]

    Thursday, August 28, 2014 8:14 PM
    Moderator

All replies

  • You must convert your value to a char data type.

    HOWEVER, you should be doing this in your presentation layer, not in your query.

    '$' + CONVERT(VARCHAR,case
    when (tblbookin.bookindt between '2013-10-01 00:00:00.000' AND '2014-09-30 23:59:59.999') 
    then ((DATEDIFF(DAY,tblbookin.bookindt,tblrelease.releaseddt)) * 62.46)
    when (tblbookin.bookindt between '2014-10-01 00:00:00.000' AND '2015-09-30 23:59:59.999') 
    then ((DATEDIFF(DAY,tblbookin.bookindt,tblrelease.releaseddt)) * 63.11)
    end)

    Thursday, August 28, 2014 8:01 PM
  •  
    case
    when (tblbookin.bookindt between '2013-10-01 00:00:00.000' AND '2014-09-30 23:59:59.999') 
    then '$'+Cast((DATEDIFF(DAY,tblbookin.bookindt,tblrelease.releaseddt)) * 62.46 as varchar(20))
    when (tblbookin.bookindt between '2014-10-01 00:00:00.000' AND '2015-09-30 23:59:59.999') 
    then '$'+Cast((DATEDIFF(DAY,tblbookin.bookindt,tblrelease.releaseddt)) * 63.11  as varchar(20))
    end as [Cost]

    Thursday, August 28, 2014 8:14 PM
    Moderator
  • You can also use the FORMAT command starting with SQL Server 2012:

    SELECT Dollar=FORMAT(123456.78,'c2','en-US'); -- $123,456.78


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Thursday, August 28, 2014 8:16 PM
    Moderator
  • Thank you, that worked.

    jer

    Friday, August 29, 2014 11:56 AM
  • Thank you, this also works.

    jer

    Friday, August 29, 2014 11:56 AM
  • Thank you, works.


    jer

    Friday, August 29, 2014 11:57 AM
    1. The first principle of any tiered architecture is that all display formatting is done in the presentation layer. This is not just SQL!  
    2. There is no CASE statement in SQL; we have a CASE expression. 
    3. Putting "tbl-" is a design flaw called tibbling and we laugh at you for doing it. Read Phil Factor's piece on it
    4. We have a DATE data type that you failed to use: (bookin_date BETWEEN  '2013-10-01'' AND '2014-09-30')
     

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, August 29, 2014 4:31 PM
  • Celko:

    Thank you for your rude reply. 

    I much prefer, Patrick Hurst's helpfulness. Patrick's knowledge is way above most, but he doesn't come off as a know it all like you do!

    Regards,

    jr7138


    jer

    Friday, August 29, 2014 5:56 PM
  • Hi,

    It could have been rude a bit, but Celko is right that we don't want to do formatting in the database. The formatting needs to be in the presentation layer.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, August 29, 2014 6:33 PM
    Moderator
  • I much prefer, Patrick Hurst's helpfulness. Patrick's knowledge is way above most

    I'm not sure I'd go that far, but I appreciate the kind words.
    Friday, August 29, 2014 7:10 PM
  • Celko:

    Thank you for your rude reply. 

    I much prefer, Patrick Hurst's helpfulness. Patrick's knowledge is way above most, but he doesn't come off as a know it all like you do!

    It was not rude. It was complete and covered several error you made. Do you want to actually get the help you need to be professional or just a quick kludge to get over hump? 

    And, yes, I am a know-it-all in SQL; look at my credentials :) 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, August 29, 2014 7:29 PM
  • Naomi,

    Thanks for the message. I will work on formatting within the presentation layer.

    Regards,

    jr7138


    jer

    Friday, August 29, 2014 7:31 PM
  • Patrick:

    You answered a question, an end-user had without blow-back or preaching. That takes a high amount of skill.

    Regards,

    jr7138


    jer

    Friday, August 29, 2014 7:34 PM
  • Celko:

    It was rude ("is a design flaw called tibbling and we laugh at you for doing it").

    But I appreciate your bullet points and have made changes, and of course I want the assistance (and also gain knowledge). Otherwise; why post a question.

    Your post was also helpful, thank you.

    Regards,

    jr7138


    jer

    Friday, August 29, 2014 7:55 PM
  • Celko:

    One additional "statement/expression":

    The "tibbling" was designed/used well before I came on board. Nothing I can do about the prefixes, but we have them all covered; sl, tbl, trd, sp.

    Cheers,

    jr7138


    jer

    Friday, August 29, 2014 8:15 PM