IF 0.00 then make it .00

Question

Post reply

IF 0.00 then make it .00

krypto69

SSChampion

Points: 13528
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()

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

dlsql SSC Enthusiast Points: 186 More actions · Answer 1

dlsql

SSC Enthusiast

Points: 186

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: 323482 More actions · Answer 2

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: 13528 More actions · Answer 3

krypto69

SSChampion

Points: 13528

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: 323482 More actions · Answer 4

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: 2438 More actions · Answer 5

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.