David Smith

David Smith

  • NA
  • 2k
  • 0

SQL Server 2012: IFF in select clause

Nov 24 2015 5:03 PM
Can someone assist me with robust logic in storedprocedure.
 
I want to to basically implement logic to say  if total hours  is greater than 80, then total hours  minus the (Sum(hours) - 80 as CorrectedHours, else display total hours. 
 
 The max hours is 80, so if users is under 80 hours to display the negative value, but print out there max number,
 
SELECT ColorId,
              ColorItem,
              SUM(hours) Totalhours, 
              SUM(hours) - 80 DifferentHours, 
IIF(SUM(hours) > 80 , SUM(hours) - (SUM(hours) - 80), SUM(hours)) CorrectedHours2, Im not for sure if this is right.
FROM dbo.ColorTable
 
 
ColorId    ColorItem    TotalHours   DifferentsHours  CorrectedHours
 1                     red             90                    10                            80
1                     blue           81.5                 1.5                           80   
  1                    black          80                     0                             80
4                      brown        79                    -1                            79

Answers (3)