IF 0.00 then make it .00

• krypto69

  SSChampion

  Points: 13604

  More actions

  January 28, 2015 at 7:10 am

  #302663

  Hi,

  I thought this would be somewhat easy but I'm having trouble with this one. I have a statement that if 'ACTLABCOST' or 'ACTMATCOST' has a value of 0.00 then I need to make it .00.

  Here's the statement:

  Select

  CONVERT(VARCHAR(10), xn_approveddate, 101) + ' ' + convert(VARCHAR(8), xn_approveddate, 108)as "Approved Date",

  WORKTYPE,

  wonum as SERVPRVREFCODE,

  DESCRIPTION,

  LOCATION,

  REPORTEDBY,

  xn_mastersystem as "MASTER SYSTEM",

  STATUS,

  CONVERT(VARCHAR(10), STATUSDATE, 101) + ' ' + convert(VARCHAR(8), STATUSDATE, 108)as STATUSDATE,

  WOPRIORITY,

  CONVERT(VARCHAR(10), ACTSTART, 101) + ' ' + convert(VARCHAR(8), ACTSTART, 108)as ACTSTART,

  CONVERT(VARCHAR(10), ACTFINISH, 101) + ' ' + convert(VARCHAR(8), ACTFINISH, 108)as ACTFINISH,

  NULL AS LEAD,

  ACTLABHRS,

  ACTLABCOST,

  ACTMATCOST,

  ESTDUR,

  assetnum AS ASSET,

  CONVERT(VARCHAR(10), TARGCOMPDATE, 101) + ' ' + convert(VARCHAR(8), TARGCOMPDATE, 108)as TARGCOMPDATE,

  CONVERT(VARCHAR(10), TARGSTARTDATE, 101) + ' ' + convert(VARCHAR(8), TARGSTARTDATE, 108)as TARGSTARTDATE,

  CONVERT(VARCHAR(10), REPORTDATE, 101) + ' ' + convert(VARCHAR(8), REPORTDATE, 108) as REPORTDATE,

  NULL AS SUPERVISOR,

  CONVERT(VARCHAR(10), SCHEDSTART, 101) + ' ' + convert(VARCHAR(8), SCHEDSTART, 108)as SCHEDSTART,

  CONVERT(VARCHAR(10), SCHEDFINISH, 101) + ' ' + convert(VARCHAR(8), SCHEDFINISH, 108)as SCHEDFINISH,

  CONVERT(VARCHAR(10), CHANGEDATE, 101) + ' ' + convert(VARCHAR(8), CHANGEDATE, 108)as CHANGEDATE,

  CHANGEBY,

  CONVERT(VARCHAR(10), PMNEXTDUEDATE, 101) + ' ' + convert(VARCHAR(8), PMNEXTDUEDATE, 108)as PMNEXTDUEDATE,

  ISTASK,

  PARENT,

  NULL AS WONUM,

  WOEQ3 AS "DETAIL LOC",

  'CUB-A0' + WOEQ14 AS CLIN,

  xn_custrefcode as CUSTREFCODE,

  xn_subworktype as SUBWORKTYPE,

  persongroup as WORKCENTER,

  null as GLACCOUNT,

  xn_outagetype as "OUTAGE TYPE",

  xn_outagecause as "OUTAGE CAUSE",

  xn_outagenumofbld as "NUM OF BLD",

  xn_outagestart as "OUTAGE START",

  xn_outageend as "OUTAGE END",

  xn_workordeld as "WORKORDER LD"

  from workorder

  where siteid = 'GTM' and woclass = 'WORKORDER' and istask = 0

  and (( worktype in ('EMERGENCY', 'PROJECT', 'RECURRING', 'ROUTINE', 'PROJECTDEV') and status in ('APPR', 'INPRG', 'COMP', 'COMP-OS', 'CAN'))

  OR (xn_subworktype in ('PROJECT', 'IDIQ') and status = 'WAPPR'))

  --AND ChangeDate > DATEADD(dd,-1,GETDATE())

  --AND Changedate > dateadd(day,datediff(day,1,GETDATE()),0)

  --last 24 hours

  AND changedate BETWEEN DATEADD(day, -1, GetDate()) AND GETDATE()

• dlsql

  SSC Enthusiast

  Points: 186

  More actions

  January 28, 2015 at 7:38 am

  #1773684

  If I've understood correctly you just need to amend the select list for those two columns:

  ACTLABCOST = case when ACTLABCOST = 0.00 then .00 else ACTLABCOST end,

  ACTMATCOST = case when ACTMATCOST = 0.00 then .00 else ACTMATCOST end,

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 28, 2015 at 7:52 am

  #1773692

  you'd have to convert to varchar, are you sure that's what you want?

  ACTLABCOST = case

  when ACTLABCOST = 0

  then '.00'

  else convert(varchar,ACTLABCOST)

  end,

  ACTMATCOST = case

  when ACTMATCOST = 0

  then '.00'

  else convert(varchar,ACTMATCOST)

  end,

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• krypto69

  SSChampion

  Points: 13604

  More actions

  January 28, 2015 at 9:05 am

  #1773724

  Thanks Lowell That worked perfectly.

  Just curious why must that be converted to varchar?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 28, 2015 at 9:16 am

  #1773727

  krypto69 (1/28/2015)

  ---

  Thanks Lowell That worked perfectly.

  Just curious why must that be converted to varchar?

  it's the datatype.

  by definition decimals or floats are represented by at least once digit left of it's decimal point.

  3.14 etc...

  to remove it, you have to change the data type to something that formats/presents it in an expecte dformat.

  excell does the formatting for most folks, for exmaple, wher ethe underlying value is one thing, but it's displayed differently.

  same thing here.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Ville-Pekka Vahteala

  SSCrazy

  Points: 2458

  More actions

  January 28, 2015 at 9:20 am

  #1773730

  Lowell (1/28/2015)

  ---

  you'd have to convert to varchar, are you sure that's what you want?

  ACTLABCOST = case

  when ACTLABCOST = 0

  then '.00'

  else convert(varchar,ACTLABCOST)

  end,

  ACTMATCOST = case

  when ACTMATCOST = 0

  then '.00'

  else convert(varchar,ACTMATCOST)

  end,

  krypto69 (1/28/2015)

  ---

  Thanks Lowell That worked perfectly.

  Just curious why must that be converted to varchar?

  Because you want it to formatted on data layer. Formatting should be done on UI.