We help IT Professionals succeed at work.

Urgent help needed for sql table joins and I am missing a row

pothireddysunil
pothireddysunil asked
on
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.

Comment
Watch Question

View Solution Only

ishando

Commented:
If any of them can potentially have no records then you'd need to do a query along the lines of:

select t0.[Technician], isnull(t1.[Currently Closed],0) as [Currently Closed], isnull(t2.[Currently Open],0) as [Currently Open],
       isnull(t3.[FCR],0) as [FCR]
from dbo.v_GroupDetails t0
  left outer join (<<query to get closed count>>) t1 on t1.[Technician] = t0.[Technician]
  left outer join (<<query to get open count>>) t2 on t2.[Technician] = t0.[Technician]
  left outer join (<<query to get fcr count>>) t3 on t3.[Technician] = t0.[Technician]
where t0."Group ID" = @pGroup

Open in new window

Top Expert 2011
Commented:
try this


Select coalesce(t1.[name],t2.[name]) as 'Technician Name'
      ,coalesce(t1.cnt,0) as [currently closed]
      ,coalesce(t2.cnt,0) as [Currently Open]
      ,coallesce(t1.[FCR],0) as FCR
   from
              (select  GroupsN."Full Name" AS [Name],
                     COUNT(*) cnt
                    ,sum(case when Incident."First Call Resolution:" = '1' then 1 else 0 end) as fcr  
                 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"  
        ) t1
   full outer 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"  
         ) t2
     on t1.[name] = t2.[name]
 
pothireddysunil

Author

Commented:
good