Problem creating query

[djburnheim]

I know a very limited amount of SQL and need some help writing a query that is probably pretty simple. I have written two seprate queries and am trying to join them..I'm not quite sure how to explain it..this is what I have

***Query 1***
SELECT Count(*) AS 'Cases Closed' from SW_HD_CASE
WHERE swDateResolved between '02 Dec 2002' AND '03 Dec 2002'

***Query 2***
SELECT swSpecialistID, dtGroupID FROM SW_SPECIALIST
WHERE SW_SPECIALIST.dtGroupID = 67
OR SW_SPECIALIST.dtGroupID = 5

There is a column in SW_HD_CASE called swReportedBy which contains swSpecialistID values. The joined query would be something like

SELECT Count(*) AS 'Cases Closed' from SW_HD_CASE SW_SPECIALIST
WHERE SW_HD_CASE.swDateResolved between '02 Dec 2002' AND '03 Dec 2002'
AND SW_SPECIALIST.dtGroupID = 5
OR SW_SPECIALIST.dtGroupID = 5
AND SW_HD_CASE.swReportedBy = SW_SPECIALIST.swSpecialistID

any suggestions?

 

[nikhilparchure]

hi hope this solves what u want
SELECT Count(*) AS 'Cases Closed'
from SW_HD_CASE , SW_SPECIALIST
WHERE SW_HD_CASE.swReportedBy = SW_SPECIALIST.swSpecialistID
and SW_HD_CASE.swDateResolved between '02 Dec 2002'
AND '03 Dec 2002'
and ( SW_SPECIALIST.dtGroupID = 67 OR
SW_SPECIALIST.dtGroupID = 5 )



Regards
Nikhil

 

[djburnheim]

Thanks for the response NikHil, I tried your query but it only returns 1 record. I don't know if I've explained the query properly.....

SW_HD_CASE
----------------
swHDCaseID swResolvedBy swDateResolved
1 2 02 Dec 2002
2 1 02 Dec 2002
3 4 02 Dec 2002
4 2 02 Dec 2002
5 3 02 Dec 2002

SW_SPECIALIST
-----------------
swSpecialistID dtGroupID
1 1
2 2
3 2
4 3
5 1

I want to count all the records in SW_HD_CASE that where resolved on the 2nd Dec by a specialist who is a member of a the group with id 1 or 2. The result would be 4

 

[sunila7]

Hi,

Try this query...

select count(*) from
SW_HD_CASE C
inner join #SW_SPECIALIST S ON S.SWSPECIALISTId=C.SW_SPECIALIST
where (dtgroupId=1 or dtgroupId=2) and swDateResolved ='02 Dec 2002'

Sunil