0

@SQLZim helped me on my ? earlier and the syntax works for about 90% of my cases, but the 10%, I can not say the same for. Let's use sample DDL below, and the query produces accurately for emp1 but not for emp2

My Expected output would be emp1 = .4050 and emp2 = .2761 however the query results produce emp1 = .4050 BUT emp2 = .6276

Why the discrepancy in calculations? For further breakdown, this is the calculation I am trying to achieve

emp1 = (1111-(661+(0*.75)))/1111 = .4050
emp2 = (1954.85-(1120.65+(392.7*.75)))/1954.85 = .2761

Which should lead to the results above, however if you look at my DDL below I do not get that outcome, I get

employee    AnnualSales DeptSales   Tax     SomeCalc
emp1        1111.00     661.00      0.00    0.405040
emp2        1954.85     1120.65     392.70  0.627618

DDL

Declare @Calc Table
(
  employee varchar(100)
  ,annualsales decimal(10,2)
  ,dep1sales decimal(10,2)
  ,dep2sales decimal(10,2)
  ,dep3sales decimal(10,2)
  ,dep4sales decimal(10,2)
  ,dep5sales decimal(10,2)
  ,tax decimal(10,2)
)


Insert Into @Calc (employee, annualsales, dep1sales, dep2sales, dep3sales, dep4sales, dep5sales, tax) Values
('emp1', '1111.00', '26.12', '83.00', '16.55', '21.77', '87.08', null)
,('emp1', null, '28.07', '11.00', '101.24', '47.03', '76.19', null)
,('emp1', null, '35.25', '7.00', '33.64', '43.54', '43.52', null)
,('emp2', '1954.85', NULL, '432.45', NULL, NULL, NULL, NULL)
,('emp2', NULL, NULL, '688.20', NULL, NULL, NULL, '392.70')


Select 
employee
,AnnualSales=sum(isnull(annualsales,0)) 
,DeptSales    =sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0)) 
,Tax        =sum(isnull(tax,0)) 
,[SomeCalc] =(sum(isnull(annualsales,0))
         -sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0)) 
         +sum(isnull(tax,0))
        )
        /nullif(sum(isnull(annualsales,0)),0)
from @Calc
group by employee
order by employee asc

How should my query be altered in order for the above expected output to be produced?

1

Instead of posting again, you could have updated your previous question.

The issue with the old query is that it wasn't calculating tax *.75 appropriately.

rextester: http://rextester.com/ZGQ51639

Declare @Calc Table
(
  employee varchar(100)
  ,annualsales decimal(10,2)
  ,dep1sales decimal(10,2)
  ,dep2sales decimal(10,2)
  ,dep3sales decimal(10,2)
  ,dep4sales decimal(10,2)
  ,dep5sales decimal(10,2)
  ,tax decimal(10,2)
)


Insert Into @Calc (employee, annualsales, dep1sales, dep2sales, dep3sales, dep4sales, dep5sales, tax) Values
('emp1', '1111.00', '26.12', '83.00', '16.55', '21.77', '87.08', null)
,('emp1', null, '28.07', '11.00', '101.24', '47.03', '76.19', null)
,('emp1', null, '35.25', '7.00', '33.64', '43.54', '43.52', null)
,('emp2', 1954.85, '1120.65', '0', '0', '0', '0', 392.70)


Select 
employee
,AnnualSales=sum(isnull(annualsales,0)) 
,DeptSales  =sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0)) 
,Tax        =sum(isnull(tax,0)) 
,[SomeCalc] =(sum(isnull(annualsales,0))
             -(
               sum(isnull(dep1sales,0)+isnull(dep2sales,0)+isnull(dep3sales,0)+isnull(dep4sales,0)+isnull(dep5sales,0)) 
             +(sum(isnull(tax,0))*.75)
              )
             )
            /nullif(sum(isnull(annualsales,0)),0)
from @Calc
group by employee
order by employee asc
1
  • Happy to Help, Michael! – SqlZim Dec 16 '16 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.