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


 ServiceId |  Service Name 
   1             Deployment
   2             Designing


 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
more ▼

asked Sep 03, 2014 at 08:49 AM in Default

avatar image

538 17 22 29

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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'),
 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
more ▼

answered Sep 03, 2014 at 07:24 PM

avatar image

5.5k 11 13 20

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 03, 2014 at 08:49 AM

Seen: 384 times

Last Updated: Sep 03, 2014 at 07:24 PM

Copyright 2018 Redgate Software. Privacy Policy