Your Explanation is Different and the Output which you have shown is Different...
in Explanation u wanted only those belonging to month 'Jan' but in the output u have shown records for 'Feb' Month too...
Anyway... try this... i think you are looking for this kind of output:
Create Table #CommisionDetails(MachineNumber Nvarchar(40),CommisioningStartDate Date,CommisioningEndDate Date)
Insert into #CommisionDetails
Select '7001','2013-01-01','2013-01-21' Union All
Select '7001','2013-01-01','2013-02-21' Union All
Select '7002','2013-02-02','2013-02-02'
Create Table #MachineTechDetails (MachineNumber NVarchar(40),MachineType Nvarchar(40))
Insert into #MachineTechDetails
Select '7001','ssp' Union
Select '7002','mmt'
Select mt.MachineType,isnull(a.Started,0) as Total,Isnull(b.Completed,0) as Completed,a.Started-isnull(b.Completed,0) as Pending From
(Select MachineNumber,count(MachineNumber) as Started from #CommisionDetails
Where Convert(Varchar(3),DateName(Month,CommisioningStartDate),0) ='Jan'
Group by MachineNumber
)a
LEFT OUTER JOIN
(Select MachineNumber,count(MachineNumber) as Completed from #CommisionDetails
Where Convert(Varchar(3),DateName(Month,CommisioningStartDate),0) ='Jan'
and Convert(Varchar(3),DateName(Month,CommisioningEndDate),0) ='Jan'
Group by MachineNumber
)b
ON a.MachineNumber=b.MachineNumber
Inner join #MachineTechDetails mt on a.MachineNumber=mt.MachineNumber
Drop Table #CommisionDetails
Drop Table #MachineTechDetails
Output:
MachineType Total Completed Pending
------------ -------- ---------- -----------
ssp 2 1 1