troubleshooting Question

Adding an if condition to an existing calculated field.

Avatar of bfuchs
bfuchsFlag for United States of America asked on
DatabasesMicrosoft SQL ServerSQL
9 Comments1 Solution93 ViewsLast Modified:
Hi Experts,

I have a column in my view named Duration defined as following.
CASE WHEN Shift_From_Hour IS NULL OR Shift_To_Hour IS NULL THEN 0 WHEN Shift_From_Hour < 0 OR Shift_From_Hour > 24 OR Shift_From_Minute < 0 OR Shift_From_Minute > 59 THEN 0 WHEN Shift_To_Hour < 0 OR Shift_To_Hour > 24 OR Shift_To_Minute < 0 OR Shift_To_Minute > 59 THEN 0 WHEN Shift_From_Hour > Shift_To_Hour THEN ((24 - Shift_From_Hour) + Shift_To_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute) ELSE (Shift_To_Hour - Shift_From_Hour) * 60 + (Shift_To_Minute - Shift_From_Minute) END

Now would like to add a new field to my view named Duration2 having the following logic.
Duration2: CInt(IIf([duration]=0,1440,[duration])/60)
(Code taken from Access query).
Meaning if the duration column is 0 it should return 1440/60, otherwise return the results of Duration column divided by 60.

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros