asked
Count Between three tables
I have three tables Data Service Status Data table Serno | ServiceId | Status | Datetime 1 2 4 12/12/2014 2 1 3 08/12/2014 Service ServiceId | Service Name 1 Deployment 2 Designing Status StatusId | Status 1 Done 2 Pending 3 20%done 4 Canceled I want a Sql code for Count (that is the status count with respect to Services) Designing 0 0 0 1 Deployment 0 0 1 0 I have tried this SELECT COUNT(Service.Status) AS Expr1, Service.ServiceName, Status.Status FROM Data INNER JOIN Service ON Data.ServiceId = Service .ServiceId INNER JOIN Status ON Data.StatusId = Status.Status_Serno GROUP BY Service.ServiceName, Status.Status
Try this create table #Data (Serno int,ServiceId int,StatusId int, DateTm DateTime) Insert into #Data values (1,2,4,'12/12/2014'),(2,1,3,'08/12/2014') Create Table #Service (ServiceId int, ServiceName varchar(20)) insert into #Service values (1,'Deployment'),(2,'Designing') Create table #Status (StatusId int, Status varchar(20)) Insert into #Status Values (1,'Done'), (2,'Pending'), (3,'20%done'), (4,'Canceled') SELECT * FROM(Select S.ServiceName,d.StatusId,st.Status From #Data D INNER JOIN #Service S ON D.ServiceId = S .ServiceId INNER JOIN #Status st ON D.StatusId = st.StatusId) p pivot (Count(StatusID) for Status in ([Done],[Pending],[20%Done],[Canceled]) )as PivotTable
15 People are following this question.
