Getting SQL query value is repeating

Question

I want to get the percentage of acceptable, excellent, notacceptable, using the below query but the answer is repeating

begin
   set nocount on

   declare @acceptable as varchar(10)
   declare @Excellent as varchar(10)
   declare @NotAcceptable as varchar(10)

   declare @total as varchar(10)

   declare @percent1 as varchar(10) = null
   declare @percent2 as varchar(10) = null
   declare @percent3 as varchar(10) = null

   select @acceptable = count(*) 
   from [dbo].[tbl_Apprisal] 
   where ApprisalStatus = 'Acceptable' 

   select @Excellent = count(*) 
   from [dbo].[tbl_Apprisal] 
   where ApprisalStatus = 'Excellent' 

   select @NotAcceptable = count(*) 
   from [dbo].[tbl_Apprisal] 
   where ApprisalStatus = 'Not Acceptable' 

   SET @total = convert(decimal, @acceptable) + 
                convert(decimal, @Excellent) +
                convert(decimal, @NotAcceptable)

   SET @percent1 = convert(int, @acceptable) * convert(int, 100) / convert(int, @total)
   SET @percent2 = convert(int, @Excellent) * convert(int, 100) / convert(int, @total)
   SET @percent3 = convert(int, @NotAcceptable) * convert(int, 100) / convert(int, @total)

   select 
       'Accplable:' + @percent1 + '%' + ',' + 'Excellent:' + @percent2 + '%' + ',' + 'Not Acceptable:' + @percent3 + '%' as persnt,
       Emp.personFname as doneby1, 
       Em.personFname + Em.[personMname] + Em.[personLname] as personFname1, 
       ap.ProcessId, ap.empNumber, 
       ap.fromDate ApprisalStatus, ap.comment, ap.DoneBy, 
       convert(date, ap.DoneByDate, 105) as DoneByDate   
   from 
       [dbo].[tbl_Apprisal] ap
   inner join 
       [dbo].[tbl_EmployeePersonalDetails] Em on Em.empNumber = ap.empNumber  
   inner join 
       [dbo].[tbl_EmployeePersonalDetails] Emp on Emp.empNumber = ap.DoneBy 
   order by 
       convert(date, ap.fromDate, 105) DESC

In this SQL query the value is repeating please help me to solve it

if you use trigger or proc .... add this line SET NOCOUNT Off https://msdn.microsoft.com/en-us/library/ms189837.aspx — Ahmed Galal, Jan 03 '16 at 13:25
Combine the counts into a [single select, using case](http://stackoverflow.com/questions/1400078/is-it-possible-to-specify-condition-in-count) to avoid reading the table three times. — Alex K., Jan 03 '16 at 13:28
Employee id is different. For all the employee id 'acceptable %', 'Excellent% 'and 'NotAcceptable%' getting same same. 'acceptable %', 'Excellent% 'and 'NotAcceptable%' are repeating — Rahul PT, Jan 03 '16 at 13:37
[Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) - you should always use the most appropriate data type - that's what they're there for, after all! If you have `count` and `percentage` - those are **clearly** numerical values - why on earth are you squeezing everything into `varchar(10)` ?? Use the **appropriate data types!** - `int` for a count, `decimal(p,s)` for a percentage..... — marc_s, Jan 03 '16 at 14:32

score 0 · Answer 1 · answered Jan 03 '16 at 20:33

Well, from what I see You have SQL problem not C#.

In cases like this, I tend to have wrong JOINs in my query, and the best way of knowing what is wrong is to write new select to see where rows are duplicated:

select 
    -- AP primary key
    ap.?
    -- Em primary key
    em.?
    -- Emp primary key
    emp.?
from 
    [dbo].[tbl_Apprisal] ap
inner join [dbo].[tbl_EmployeePersonalDetails] Em on Em.empNumber = ap.empNumber  
inner join [dbo].[tbl_EmployeePersonalDetails] Emp on Emp.empNumber = ap.DoneBy

Please check what is pk for each joined table. You should have one em and emp PK per one ap PK. You clearly do not have this situation, so I see two options. Either You can try to write correct joins to ensure that rows will not double itself, or if this is not possible (for example one employee can have many personal details), You can decide which one You want to distpal in subquery and join to it. Something like this:

select 
[..]
from 
    [dbo].[tbl_Apprisal] ap
inner join (
    SELECT 
        empNumber,
        MAX(personFname) as personFname 
    FROM 
        [dbo].[tbl_EmployeePersonalDetails] 
    GROUP BY 
        empNumber
) Em on Em.empNumber = ap.empNumber 
[..]

Hope that will help.

Getting SQL query value is repeating

1 Answers1