We help IT Professionals succeed at work.
Get Started

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

396 Views
Last Modified: 2012-05-09
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
CERTIFIED EXPERT
Top Expert 2011
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE