if / else statement?
-
Hello friends!
My department *still* hasn't gotten a real SQL programmer yet!
Anyway, here's a query I've been working on all week. I have this query, that grabs data from various tables to count the number of items(called totReferrals) for 2 different departments.
It links the department by schoolworker. But there is one schoolworker who works in 2 different department, so it throws off the count.
This schoolworker has 2 entries in the user table(1 for each department he works in), one is james1, and the other is james2.
Problem is both James1 and James2 are linked to the same department(UadminofficeID) because that is his primary office.
But in the query, I want James2 to count in the other office he works in.
I was thinking, would it be possible to use an if/else statement in a problem like this?
something like if uname = james2, then....
select AOofficeName as Office, count(ARid) as totReferrals
from AdminResource, ProfRequest, Users, AdminOffice
where ARenteredDate BETWEEN '01/01/2005' and '10/18/2005'
and ARcurrentStatus >= 0
and ARprofrequestID = PRid
and PRschoolworker = Uname //linking the school worker to their username in the user table
and UadminofficeID = AOid //linking the user to his admin office
thank you!
-
Where is your group by clause?
This query does not appear to be complete.
Try adding the username to the group by
-
Also you still have to use the CASE statement I showed you a few weeks ago. That won't ever change.
-
OOps...I can't cut and paste very well it seems! Here is the full query.
I only have 2 options...james1 & james2...I'd still have to use case?
To be honest, I don't even know what I want to do here. I just know the fact that james having 2 usernames in the users table is causing the numbers to be off.
select AOofficeName as Office, count(ARid) as totReferrals
from AdminResource, ProfRequest, Users, AdminOffice
where ARenteredDate BETWEEN '01/01/2005' and '10/18/2005'
and ARcurrentStatus >= 0
and ARprofrequestID = PRid
and PRschoolworker = Uname
and UadminofficeID = AOid
group by AOofficeName
order by AOofficeName
-
select AOofficeName as Office, count(ARid) as totReferrals ,UserName
from AdminResource, ProfRequest, Users, AdminOffice
where ARenteredDate BETWEEN '01/01/2005' and '10/18/2005'
and ARcurrentStatus >= 0
and ARprofrequestID = PRid
and PRschoolworker = Uname
and UadminofficeID = AOid
group by AOofficeName, USERNAME
order by AOofficeName
-
I was reffering to a sum(case) solution but that should do it... unless we don't have all the info.
-
RayM, I don't quite understand how adding username to the query will fix the count issue I am having.
Is that where the sum(case) comes in RGR'us?
Thank you gentleman!
-
You can do something like this, but I'm not totally sure it solves the problem in this case.
select AOofficeName as Office, count(ARid) as totReferrals ,case when UserName IN ('james1', 'james2') THEN 'James' ELSE UserName END AS UserName
from AdminResource, ProfRequest, Users, AdminOffice
where ARenteredDate BETWEEN '01/01/2005' and '10/18/2005'
and ARcurrentStatus >= 0
and ARprofrequestID = PRid
and PRschoolworker = Uname
and UadminofficeID = AOid
group by AOofficeName, case when UserName IN ('james1', 'james2') THEN 'James' ELSE UserName ENDorder by AOofficeName
-
RGR'us, I see what you are doing. You are combining the two usernames as one. But I think I failed to explain(as usual) what I'm actually stuck on.
James works for the Astronomy department(James1), but he also does a bit of work for the CIS department...so there's another username he uses for that called James2.
My original query counts the total amount of student referrals for each department(totReferrals).
So the results look like this:
Office TotReferrals
Astronomy 192
CIS 310
The problem is, my current query does this:
and UadminofficeID = AOid <---UadminofficeID is in the users table and is the key for the adminoffice table. This is how it matches each user to their respective office.
But James1 and James2 have the SAME UadminofficeID. So both James1 and James2 counts are going into the CIS department, even though James2 is the username he uses when he works in the Astronomy dept.
So that's why I originally asked about the if/then statement.
If username = James2, then count it in the Astronomy department else count it in the CIS department
or as you say, use case:
CASE WHEN James2 ELSE <---not sure what to put in the blanks.....
I am sure I just made it worse!
Thank you!
-
Sounds almost impossible... you want to show the data for one thing, but not for the other... but for the other too???
-
well, let me type it out to help me think.
I'm doing a query that counts the total amount of referrals. (count(ARid) as totReferrals)
each entry in the AdminResource table is linked to the ProfRequest table (and ARprofrequestID = PRid )
each entry in the ProfRequest table is linked to the user table (and PRschoolworker = Uname)
and finally, each entry in the user table is linked to the AdminOffice table(and UadminofficeID = AOid)
and THATS how I determine the department.
And it works great if James would just get fired!
But seriously, my problem is, I am linking the user to the department(UadminofficeID = AOid), and since James works for two departments, his referrals(from both James1 and James2) are both going to his main department(via UadminofficeID = AOid).
Now you may ask, why not just change the James2 UadminofficeID entry in the user table to point to the correct department office?
Well, if I did that, it would break about a million other queries.
So my only option is, to figure out how to get the the referrals from James2 to count for the correct department and NOT the department that is linked to his UadminofficeID.
So my guess is, I'll need to come up with a totally different way to link the referrals to the department. Right now, they are linked via the user...which is why I have this problem.
mmmm....
-
just move the case the to departement id depending on the james of the row and that'll work.
-
really? that'll work? I will work on it tonight.
-
I am not sure, since my orientation in the old syntax is not good, but translated into JOINs it should be something like this:
select AOofficeName as Office, count(ARid) as totReferrals
from AdminResource AR
join ProfRequest PR on PR.prID = AR.ARprofrequestID
join Users U on u.uname = PR.schoolworker
join AdminOffice AO on AO.aoID = U.uadminofficeID
where ARenteredDate BETWEEN '01/01/2005' and '10/18/2005'
and ARcurrentStatus >= 0
group by AOofficeName
order by AOofficeName
If that's correct, you can simply join a fake table (called "derived table" in BOL) - instead of joining table USERS, you join to a select in which you replace the department for james2 with the value you want to use. It works precisely like joining to the original table, if the value for James2 could be corrected there. Example - if James2 should count to the department with ID 999, it would look like this:
select AOofficeName as Office, count(ARid) as totReferrals
from AdminResource AR
join ProfRequest PR on PR.prID = AR.ARprofrequestID
join (SELECT uname,
CASE WHEN uname = 'james2' THEN 999 ELSE uadminofficeID END as trueofficeID
FROM Users) U on u.uname = PR.schoolworker
join AdminOffice AO on AO.aoID = U.trueofficeID
where ARenteredDate BETWEEN '01/01/2005' and '10/18/2005'
and ARcurrentStatus >= 0
group by AOofficeName
order by AOofficeName
However, all this is untested, since I don't have DDL of your tables.
-
Wow...so do I need to use both queries with a GO between them?
Is this any better or worse than using a CASE statement as RGR suggested?
Thank yoU!
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply