Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

abc123
Starting Member

47 Posts

Posted - 2009-02-24 : 01:58:30

I have two tables as follow

tbl1 -
JobMappingID JobID ID ExecutionStatus
-------------------- -------------------- -------------------- -------------------
8 3 4 0
9 3 5 1
11 3 6 1
17 17 1 1
18 17 2 1
19 18 3 1
20 18 7 1
21 18 8 0
22 19 9 0
23 19 10 0

tb2 -

JobID JobName CreatedOn ExecutionStatus
-------------------- ----------------- ----------------------- --------------------
3 Job2 2009-02-23 13:50:37.000 1
17 Job3 2009-02-22 12:50:37.000 2
18 Job4 2009-02-23 13:50:37.000 3
19 Job1 2009-02-23 13:50:37.000 4
20 Job2 2009-02-23 13:50:37.000 1
21 Job3 2009-02-22 12:50:37.000 2
22 Job4 2009-02-23 13:50:37.000 3
23 Job1 2009-02-23 13:50:37.000 4

I want the count of all ID with ExecutionStatus = 1. if ExecutionStatus = 0 then NoOfSuccess = 0

My o/p shoud be

JobID JobName CreatedOn NoOfID NoOfSuccess
------ --------- --------------------------------------------------
3 Job2 2009-02-23 13:50:37.000 3 2
17 Job3 2009-02-22 12:50:37.000 2 2
18 Job4 2009-02-23 13:50:37.000 3 2
19 Job1 2009-02-23 13:50:37.000 2 0

plz tell me how I can achive this.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-24 : 02:20:15

select t2.jobID,t2.jobName,CreatedOn,count(ID) as NoOfID,sum(t1.ExecutionStatus) as NoOfSuccess
from @tbl2 t2 join @tbl1 t1 on t1.JobID = t2.JobID
group by t2.jobID,t2.jobName,CreatedOn

Karthik

nirene
Yak Posting Veteran

98 Posts

Posted - 2009-02-24 : 02:27:28

Declare @Temp Table (JobmappingId Int,Jobid Int,Id Int,Executionstatus Int)
Insert Into @Temp
Select 8,3,4,0 Union All Select 9,3,5,1 Union All Select 11,3,6,1 Union All Select 17,17,1,1 Union All Select 18,17,2,1
Union All Select 19,18,3,1 Union All Select 20,18,7,1 Union All Select 21,18,8,0 Union All Select 22,19,9,0 Union All
Select 23,19,10,0

Declare @Temp1 Table (Jobid Int,Jobname Varchar(10),Createdon Datetime,Executionstatus Int)
Insert Into @Temp1
Select 3,'Job2','2009-02-23 13:50:37.000' ,1 Union All Select 17,'Job3','2009-02-22 12:50:37.000' ,2 Union All Select 18,'Job4','2009-02-23 13:50:37.000' ,3
Union All Select 19,'Job1','2009-02-23 13:50:37.000' ,4 Union All Select 20,'Job2','2009-02-23 13:50:37.000' ,1 Union All Select 21,'Job3','2009-02-23 13:50:37.000' ,2
Union All Select 22,'Job4','2009-02-23 13:50:37.000' ,3 Union All Select 23,'Job1','2009-02-23 13:50:37.000' ,4

Select X.*,Y.ES from
(Select A.Jobid,B.Jobname,B.Createdon,Count(A.Jobid) as Cnt from @Temp A,@Temp1 B Where A.jobid=B.jobid Group By A.Jobid,B.Jobname,B.Createdon) X
Left Join
(Select Jobid,Sum(Case When Executionstatus=1 then 1 else 0 end) as ES from @Temp Group by Jobid) Y
On X.Jobid=Y.Jobid

or

Select X.*,Y.ES from
(Select A.Jobid,B.Jobname,B.Createdon,Count(A.Jobid) as Cnt from @Temp A,@Temp1 B Where A.jobid=B.jobid Group By A.Jobid,B.Jobname,B.Createdon) X
Left Join
(Select Jobid,Sum(Executionstatus) as ES from @Temp Group by Jobid) Y
On X.Jobid=Y.Jobid

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-24 : 02:31:56

Try this once
select distinct t.jobid,t1.jobname,t1.CreatedOn,count(ID) as NoOfID,sum(t.ExecutionStatus) as NoOfSuccess
from @tbl1 t
inner join @tbl2 t1 on t.jobid = t1.jobid
group by t.jobid,t1.jobname,t1.CreatedOn

jbp_j
Starting Member

24 Posts

Posted - 2009-02-24 : 03:56:18

hi try this one also,

select a.jobid,jobname,createdon,count(*) as NoOfID,sum(case when a.ExecutionStatus = 1 then 1 else 0 end) as NoOfSuccess
from @tab1 as a
inner join @tab2 as b on a.jobid = b.jobid
group by a.jobid,jobname,createdon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources