Calculated Field Question

Hi Everyone,

Question on a calculated field query. I’m attempting to write a query stated if JobHead.JobComplete equals True, Count the number of True returned.

Any ideas?

TIA

This is your calculated field. Then group by the rest of the fields in your query.

sum(
case when JobHead.JobCompete = 1 then 1
else 0
end
)

Thanks Brandon, that worked. You’re the man!
If I wanted to caluclate the number of days between job start date and end date what would be the best query for that?

That’s right here. A bunch of that stuff is in the helper menu. Talk a look through there to familiarize yourself with the basic stuff that they help you with.

1 Like

[quote=“Banderson, post:2, topic:102341”]

sum(
case when JobHead.JobCompete = 1 then 1
else 0
end
)

[/quote]datediff(date/time-day.JobHead.StartDate,JobHead.JobCompletionDate)
datediff(date/time-day.JobHead.StartDate,JobHead.JobCompletionDate). Is there a reason why I’m getting a three argument error?

datediff(date/time-day.JobHead.StartDate,JobHead.JobCompletionDate)

Your parameters need to be separated by commas

dateDiff( parameter1 , parameter2, parameter2)
--or like this
datediff( day, JobHead.StartDate ,JobHead.JobCompletionDate)

Thanks again. Probably need to take a begineers class. lol

Just keep in mind that all of the calculated field stuff is SQL. So if you have questions about something, you can google SQL whateverThingYouAreTryingToDo, and there is almost always some documentation on it. Either Microsoft, or someone on stack overflow has asked a similar question.

1 Like

I guess you will need a calculated field as

datediff(day, JobHead.StartDate, jobHead.DueDate)

I hope this helps.