Rando (6/10/2010)

---

Thanks. I'm not able to test this yet since they took our server down today and are migrating it to a different server.

I've never seen SQL like that...

scott.pletcher (6/10/2010)

---

Why couldn't one judge have been there for, say, 60 years, and another judge for only 2 weeks? Wouldn't they have a different number of cases??

Assuming there's only...

scott.pletcher (6/10/2010)

---

But your code is not accurate at all.

The total number of cases a judge has is ABSOLUTELY IRRELEVANT to assigning cases...

scott.pletcher (6/10/2010)

---

Btw, frankly I think a separate table for the judges for rounds is much simpler, and vastly more accurate, than trying to add a lot of switches to the...

#judges says the same but #cases is slightly different:

create table #cases

(

CaseID INT IDENTITY PRIMARY...

scott.pletcher (6/10/2010)

---

That's not true here, because...

Yes it works with the sample data provided, but without specific rule...

Personally I think it would be undesirable to use additional tables that you insert and delete judges from. Everything that you've explained that you want can be obtained from...

Simply handle the TransferCase on your UI side. Your UI will run a query to see if an active judge has had the client in the last x period...

I'm going to take this in a little different direction ...

I've simplified the table structures to make it a bit more straight forward. I've also introduced an 'IsActive' field...

This returns the expected results from your first post:

select prod.dtDate,

comp.SF1,

comp.SF2,

comp.SF3,

comp.SF4,

comp.SF5,

prod.Calls,

prod.TIA,

prod.NPP,

prod.Breaks

from

(

select dtDate,

SUM(iCallsAnswered) as Calls,

SUM(iTIASeconds) as TIA,

SUM(iNPPSeconds) as NPP,

SUM(iBreakSeconds) as Breaks

from pt_ProdData

group by dtDate

) prod

join

(

select DateCompleted,

sum(case when SubFunction_ID = 1 then...

Your Breaks is the sum of your...

In asp.net you should be issuing a connection close for each time you have a connection open. The .net framework manages the connection pooling for you, but I'm pretty...

If I was starting out right now, I would continue to try to find 'real DBA' type jobs, but at the same time I would spend a decent amount of...

I would like to help you but not enough information is included. What is the result set you are looking for? You refer to multiple 'scripts', but I'm...