• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 85
  • Last Modified:

Sql query

Hi,
Here are the three table TableA,TableB, and TableC with following columns.
Table A
EmpId  Name
001        abc
002        bbc
Table B
Id  empid , date, statusId  (This is reference of Statusid from table C)
1    001     ,12-2-2015,   1
2    001  , 13-2-2015,     1
3    001  , 14-2-2015,     2
4    002  , 15-2-2015 ,     3
Table C
statusId  statusName
1      Inprogress
2      Completed
3      Not Open


Now what is the count of inprogress and what is the count of completed and what is the count of Not open for each employee.
Please provide the query.
0
praveen1981
Asked:
praveen1981
  • 4
  • 2
  • 2
1 Solution
 
Saurabh Singh TeotiaCommented:
You can use the following query...

select tb1.name,tb3.statusname,count(tb2.id)
from table1 tb1
left join table2 tb2 on tb1.empid=tb2.empid
left join table tb3 on tb2.statusid=tb3.statusid

Open in new window


Saurabh...
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT A.Name C.StatusName, COUNT(B.StatusID)
FROM TableB B
    INNER JOIN TableC C ON B.StatusID = C.StatusID
    INNER JOIN TableA  A ON B.Empid = A.EmpId
GROUP BY A.Name, C.StatusName

Open in new window

0
 
Saurabh Singh TeotiaCommented:
Ahh Good catch vitor..I missed group by in my statement my bad...

select tb1.name,tb3.statusname,count(tb2.id)
from table1 tb1
left join table2 tb2 on tb1.empid=tb2.empid
left join table tb3 on tb2.statusid=tb3.statusid
group by tb1.name,tb3.statusname

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
praveen1981Author Commented:
Hi

I want the result as follows

EmpId  Inprogress Completed NotOpen

001           2                    1                0
002           0                    0                1

could you please suggest.
0
 
Saurabh Singh TeotiaCommented:
You can do something like this to do what you are looking for..

select tb1.empid,
'Inprogress'=sum(case tb3.statusname='Inprogress' then 1 else 0 end),
'Completed'=sum(case tb3.statusname='Completed' then 1 else 0 end),
'Not Open'=sum(case tb3.statusname='Not Open' then 1 else 0 end)
from tablea tb1
left join tableb tb2 on tb1.empid=tb2.empid
left join tablc tb3 on tb2.statusid=tb3.statusid
group by tb1.empid

Open in new window


Saurabh...
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use PIVOT table:
SELECT *
FROM (SELECT A.EmpId, C.StatusName, B.StatusID
		FROM TableB B
			INNER JOIN TableC C ON B.StatusID = C.StatusID
			INNER JOIN TableA  A ON B.Empid = A.EmpId) T
PIVOT
	(COUNT(StatusID) FOR StatusName IN (Inprogress, Completed, [Not Open])
) AS PivotTable

Open in new window

0
 
praveen1981Author Commented:
Thanks
0
 
Saurabh Singh TeotiaCommented:
Any particular reason for the grade b?? Because i'm assuming your problem was solved on this one properly..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now