MS SQL using CASE WHEN Statements


I have written a very long query ( a payroll / timesheet app) and am having issues with only particular UPDATE Query:
SET pr.M_W1_HRS = (CASE WHEN (pt.StartDate = @M_W1) THEN (8.0000 - pt.AbsenceDuration) ELSE pr.M_W1_HRS END),
	pr.T_W1_HRS = (CASE WHEN (pt.StartDate = @T_W1) THEN (8.0000 - pt.AbsenceDuration) ELSE pr.T_W1_HRS END),
        . (etc, etc for each day of the workweek)
FROM PayrollReport PR
INNER JOIN PTO_Temp pt ON pt.PersonID = pr.EE

Open in new window

I have my main table (PayrollReport) and a temp table (PTO_Temp) which contains durations and dates of time-off occurrences. My date variables contain valid datetime formats, and Im simply trying to subtract 8 from the AbsenceDuration column to arrive at the hours worked for that day.  I get no errors running it, but it is not updating the table.

Is my syntax correct in the CASE WHEN Statement?

Thanks in advance!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

My first question is do you get results from a query like this:

	PayrollReport PR
	INNER JOIN PTO_Temp pt ON pt.PersonID = pr.EE
WHERE pt.StartDate = @M_W1 AND (8.0000 - pt.AbsenceDuration) <> pr.M_W1_HRS 

Open in new window

(YTou can adjust for the appropriate day...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
The syntax is OK.  But I'm pretty sure you'll have a logic error.

Keep in mind that an UPDATE's affect on rows will not "accumulate".  That is, each UPDATE is a complete, separate event, and SQL won't "remember" any prior UPDATEs when another UPDATE is applied.

You'll need to pre-summarize totals from the PTO_Temp by person and week, and then use that pre-summarized result set to UPDATE the main PayrollReport row just once for that person and week.
hhnetworksAuthor Commented:

After declaring and setting the variable, your query returned as expected.  See screenshot
hhnetworksAuthor Commented:
Both of you were correct. My Joins to the temp table were not working the way I needed them to, AND I had some significant logic errors.

Thanks for all your help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.