How can I replace the empty values from one column with the unique value from a different column?

user03757

I have Estimated Budget hours (ie 800)  and then sometimes we Update our hours (ie 3,600) to a new "Updated Budget" if needed.  (This "update budget" is set up to include the original estimate so I cannot simply add them)  I need a column to combine using the Updated Budget hours except when ' ' empty or 0, then use the hours in the Estimated Budget.   

I do know the Updated Budget is '  '  (empty)  See attachment   

 

I have tried this:

SELECT processimprovement_dwb. *,
Case WHEN `Updated Budget` like ' ' then (`Estimated Budget`+`Updated Budget`)
else `Updated Budget`
end as 'Estimated and Updated Hours' from processimprovement_dwb

 

And I have tried this:

SELECT processimprovement_dwb. *,
Case WHEN `Updated Budget` like '' then (`Estimated Budget`+`Updated Budget`)
else `Estimated Budget`
end as 'Estimated and Updated Hours' from processimprovement_dwb

 

But neither one gives me the result I need.   

I need to see the value for the Estimated Budget where there was a 0 for Updated Budget.

Please see attachment.

 

 

emendoza

Hello,

 

 

Thank you for providing that screenshot! I see my mistake now. Try changing the 'OR's into 'AND's in the query I provided earlier and see if that works.

 

Enoc Mendoza

**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

user03757

Perfect!  That worked  -  Thank you so much!

Chronos

Is anyone able to help out with this request?

emendoza

Hello,

 

If I understand correctly, essentially you want to check to see if "Updated Budget" is either ' ' empty or NULL, if so, use the current "Estimated Budget", but if is not NULL or empty, then use the "Updated Budget" is that correct? In this case, I'd use something like this:

SELECT
(CASE WHEN (`Updated Budget` IS NOT NULL) OR (`Updated Budget` != '') OR (`Updated Budget` != 0)

THEN `Updated Budget`
ELSE `Estimated Budget`
END) AS 'Estimated and Updated Hours'
FROM 'table_name'

This will cause Update Budget to always be used if it has something in it that isn't empty/NULL/0, and otherwise it will use the Estimated Budget. Does that make sense? Let me know if that works for you!

 

Enoc Mendoza

**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

ST_Superman

Have you tried this:

 

SELECT

ifnull(`Updated Budget`,`Estimated Budget`) as `Estimated and Updated Hours`

ST_Superman

Sorry, it may be too early to help on Dojo for me this morning.  I read your post a little more closely.  I think that I would approach this like this:

 

SELECT *,

CASE(WHEN `Updated Budget` != 0 THEN `Estimated Budget` ELSE `Updated Budget` END AS `Estimated and Updated Hours`,

FROM (SELECT `Estimated Budged`,

ifnull(`Updated Budget`,0) AS `Updated Budget` -- This will enter a 0 any time the updated budget is null

FROM processimprovement_dwb) a 

 

This statement will make a temporary table that replaces any null values from the Updated Budget field with 0's first.  

user03757

Thank you for trying - unfortunately this is still not pulling the estimated with updated value is 0    - See attached

 

 

emendoza

Hello,

 

 

Thank you for providing that screenshot! I see my mistake now. Try changing the 'OR's into 'AND's in the query I provided earlier and see if that works.

 

Enoc Mendoza

**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

user03757

Perfect!  That worked  -  Thank you so much!

user13436

Can you do this without using SQL?