Here is my sql query
Select t1.[Technician] as 'Technician Name',t1. [Currently Closed] ,t2.[Currently Open],t3.[FCR]
from(select distinct GDetails1."Full Name" AS [Technician],
isnull(cnt, 0) AS [Currently Closed]
from dbo.v_GroupDetails GDetails1
inner join
(select GroupsN."Full Name" AS [Name],
isnull(COUNT(*),0) cnt
from dbo.v_Incident as Incident inner join dbo.v_GroupDetails as GroupsN
on GroupsN."Group ID" = Incident."Closed Group:" and
GroupsN."Full Name" = Incident."Full Name Closed By"
and Incident."Closed Group:" = @pGroup
where Incident."Status ID:" = 'CLOSED'
AND convert(datetime,Incident.[Close Date & Time],101) between '2009-11-01' and '2010-04-30'
Group by GroupsN."Full Name") Tic on Tic.[Name] = GDetails1."Full Name" and GDetails1."Group ID" = @pGroup ) t1
inner join
(select distinct GDetails1."Full Name" AS [Tech],
isnull(cnt, 0) AS [Currently Open]
from dbo.v_GroupDetails GDetails1
inner join
(select GroupsN."Full Name" AS [Name],COUNT(*) cnt
from dbo.v_Incident as Incident inner join dbo.v_GroupDetails as GroupsN
on GroupsN."Group ID" = Incident."Opened Group:" and
GroupsN."Full Name" = Incident."Full Name Opened By"
and Incident."Opened Group:" = @pGroup
where convert(datetime,Incident.[Close Date & Time],101) between '2009-11-01' and '2010-04-30'
Group by GroupsN."Full Name") Tic on Tic.[Name] = GDetails1."Full Name" and GDetails1."Group ID" = @pGroup) t2
on t1.[Technician] = t2.[Tech]
inner join
(select GDetails1."Full Name" AS [Tech],
isnull(cnt, 0) AS [FCR]
from dbo.v_GroupDetails GDetails1
inner join
(select distinct GroupsN."Full Name" AS [Name],
isnull(COUNT(*),0) cnt
from dbo.v_Incident as Incident inner join dbo.v_GroupDetails as GroupsN
on GroupsN."Group ID" = Incident."Closed Group:" and
GroupsN."Full Name" = Incident."Full Name Closed By" and Incident."Closed Group:" = @pGroup
where Incident."First Call Resolution:" = '1' and Incident."Status ID:" = 'CLOSED'
and convert(datetime,Incident.[Close Date & Time],101) between '2009-11-01' and '2010-04-30'
Group by GroupsN."Full Name") Tic on Tic.[Name] = GDetails1."Full Name" and GDetails1."Group ID" = @pGroup) t3
on t1.[Technician] = t3.[Tech]
for this query my tables are dbo.v_Incident and dbo.v_GroupDetails
dbo.v_GroupDetails has some groups and each group has some users.
each user opens and closes some tickets
i need all the three types of ticekts for all the users for a specific group
and if the user doesn't have any tickets for any of the type i need to print 0 for that column.
In the above query i am getting 7 rows for first two selects and third i am only getting 6 rows and when i joining i am getting total only 6 rows.
help urgently needed.