How do I reload Judges table?

Question

Post reply

How do I reload Judges table?

Viewing 4 posts - 46 through 48 (of 48 total)

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

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 1

I could see you wrestled with it. Some of the requirements weren't exactly clear.

I like to ask a lot of questions so I don't get boxed in later.

Nothing worse than assuming too much and having to scrap it and start over.

Some interesting dialog in this thread.

Have agreat weekend.

Greg E

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 2

Actually, initially, the requirements weren't complete.

Initially, I was told to just worry about whether a client had been assigned to a judge previously for similar or different case(s).

If yes, automatically assign the same case to same judge; otherwise, assign to a new judge.

I thought it was easy and was able to complete this within 2 days.

It was then I was told about reloading the wheel with same 12 judges and starting all over.

Well, I am extremely fortunate for this forum.

My record here so far is perfect.

Just when I think a problem is too complex to handle and would take a long time, someone comes along with a solution.

This time, it is Scott Pletcher. This forum is lucky to have him.

He is talented.

I hope to be able to contribute one day.

Speaking of problem, Scott, I am having understand which fieldname, from which table they are related to:

newJudgeID

newClientID

newCaseID

Are these params from Case table?

Also, this is the code I used to initially load the judges dropdown from which judges are assigned.

Please ignore the html and just concentrate on the sql:

SELECT j.JudgeCode, j.JudgeName

FROM Judges j

LEFT JOIN Cases c ON c.JudgeCode = j.JudgeCode

WHERE c.JudgeCode is null

ORDER BY j.JudgeName

Which part of your code will do same thing?

Is the code the loads it, also the code that reloads it?

So far, I am able to get the Round table to be populated but nothing else seems to work for me.

For instance, we insert JudgeID into Round table by selecting JudgeIde from Judges table with this WHERE Clause:

WHERE status = 1 AND isTakingNewCases = 1

but I don't see where we are updating the Judges to set isTakingNewCases = 0 after the insert.

I know it is more me who is confused than the code.

Thanks a lot

It is 11:08PM Saturday and I am still trying to figure them out.

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 3

but I don't see where we are updating the Judges to set isTakingNewCases = 0 after the insert.

No, sorry, it is confusing, but I didn't have time to go into any detail earlier.

I added that just-in-case as a global flag. It is not used for round selection, only if the judge is not taking any cases for some period of time for whatever reason (but without actually retiring, etc.). In case a judge is ill, temporarily needs a reduced work load, or whatever.

The @ sign are local T-SQL variables. That is, they are just variables for holding values, not table columns. Since you are using another language, you just DIM them or whatever.

To reload the round table, you select from the Judge table -- and, if applicable, any other table(s) you need to determine judge activity -- making sure the judge's (permanent) status is active and their (temporary) status is that they are still available to be assigned new cases.

The INSERT into the round table handles all the logic for determining judge eligibility. After that, the next random judge is just SELECTed from the round table. If a judge becomes unavailable after the round table is judge, just delete the judge from the round table.

Scott Pletcher, SQL Server MVP 2008-2010

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 4

I *think* I got it working Scott.

I am having difficulty putting your ideas together to work for me.

I did, however, "borrow" an idea that I have kind of used differently.

Since I am still confused about the Round table and stuff, I decided to add that isTakingNewCases fieldname into the Judges table.

Anytime a case is assigned a judge, if the case is new and isTransfer <> 1 (it is not a transfer case), isTakingNewCases is set to 0.

Once all the isTakingNewCases (all 12 judges) have each been assigned a case, then isTakingNewCases is reset to 1 (update judges set istakingNewCases = 1)

and the query is called again to reload the judges table:

SELECT JudgeCode, JudgeName FROM Judges

WHERE IsTakingNewCases = 1

ORDER BY JudgeName

I am sure there is a more efficient way but unless/until we find a bug during testing, I will stick with this.

Thanks to you and EVERYONE who contributed your expert asssistance to this post.

I am really grateful.

P.S. I will revive this post (with your permission) if this doesn't work after all but testing is continuing.