CODEWITHSUNDEEP
sql-server
300
300

Reputation: 1031

How to do conditional select using SQL Server 2005

I have following query that I want to improve so it displays two additional columns as Variance and Percentage_Variance. 

declare @w0s datetime
declare @w0e datetime
declare @w1s datetime
declare @w1e datetime

set @w0s = dateadd(hh, datediff(hh, 0, getdate()), 0)
set @w0e = getdate()
set @w1s = dateadd(dd, datediff(dd, 0, getdate()) / 7 * 7 - 7, 0)
set @w1e = dateadd(dd, datediff(dd, 0, getdate()) / 7 * 7, 0)

select 
    state,
    sum(case 
           when create_time >= @w0s and create_time < @w0e 
              then 1 else 0 
        end) as Call_Volume_This_hr,
    sum(case 
          when create_time >= @w1s and create_time < @w1e 
             then 1 else 0 
        end) / 7.0 as Avg_Call_Volume_Past_Week1
      --,(case when ((sum(case when create_time>=@w0s and create_time<@w0e then 1 else 0 end)) - (sum(case when create_time>=@w1s and create_time<@w1e then 1 else 0 end)/7.0) > 0)then 1 else 0 end) as Variance
      --,(case when (((case when ((sum(case when create_time>=@w0s and create_time<@w0e then 1 else 0 end)) - (sum(case when create_time>=@w1s and create_time<@w1e then 1 else 0 end)/7.0) > 0)then 1 else 0 end)/(sum(case when create_time>=@w1s and create_time<@w1e then 1 else 0 end)/7.0)*100.0) > 0) then 1 else 0 end) as Percentage_Variance
from 
    ctirpts.dbo.cti_reporting
where 
    create_time >= @w1s
    and datepart(hh, create_time) = datepart(hour, getdate())
    and state is not null
group by 
    state
;

The condition is that 

if (Call_Volume_This_hr > Avg_Call_Volume_Past_Week1)
then 
    Variance = Call_Volume_This_hr - Avg_Call_Volume_Past_Week1 
and 
    Percentage_Variance = (Variance/Avg_Call_Volume_Past_Week1)*100
else
    Variance = 0 and Percentage_Variance = 0

The two commented lines shows my feeble attempt at getting this done but it prints 1 under columns "Variance" and "Percentage_Variance" when "if (Call_Volume_This_hr > Avg_Call_Volume_Past_Week1)" is true. Instead I want the actual computed values as per:

Variance = Call_Volume_This_hr - Avg_Call_Volume_Past_Week1
Percentage_Variance = (Variance/Avg_Call_Volume_Past_Week1)*100

I would appreciate any help or pointers.

Upvotes: 0

Views: 54

Answers (2)

Brian Stork
Brian Stork

Reputation: 980

You could put your initial select into a common table expression, then those calculated SUM columns become much more readily available for conditional computation. You can use an APPLY operator to perform the CASE statement, returning Variance of zero or more (never negatives). Then you can perform the calculation to get the percentage variance.

-- this CTE performs the initial aggregation and makes the recordset available
-- to be used like a table:
;with PreAggregate
as (
    select state
          ,sum(case when create_time>=@w0s and create_time<@w0e then 1 else 0 end) as Call_Volume_This_hr
          ,sum(case when create_time>=@w1s and create_time<@w1e then 1 else 0 end)/7.0 as Avg_Call_Volume_Past_Week1
     from ctirpts.dbo.cti_reporting
      where create_time>=@w1s
       and datepart(hh,create_time)=datepart(hour, getdate())
       and state is not null
     group by state
   )
-- The APPLY operator creates an inline-table-value function without the
-- "black box" query optimizer problems of using an actual UDF
-- The "CROSS APPLY" calculates a 0 or more value for Variance.
-- The Variance value can then be used to compute Percentage_Variance.
select PreAggregate.state
     , PreAggregate.Call_Volume_This_hr
     , PreAggregate.Avg_Call_Volume_Past_Week1
     , InlineFunction.Variance
     , (InlineFunction.Variance/PreAggregate.Avg_Call_Volume_Past_Week1)*100 as 'Percentage_Variance'
  from PreAggregate
cross apply (
             select case
                   when Call_Volume_This_hr < Avg_Call_Volume_Past_Week1 then 0
                   else Call_Volume_This_hr -Avg_Call_Volume_Past_Week1
                   end 'Variance'
            ) InlineFunction;

Upvotes: 1

JohnS
JohnS

Reputation: 2052

I think your two commented lines should be as follows:

  ( ( SUM(CASE WHEN create_time >= @w0s
                    AND create_time < @w0e THEN 1
               ELSE 0
          END) ) - ( SUM(CASE WHEN create_time >= @w1s
                                   AND create_time < @w1e THEN 1
                              ELSE 0
                         END) / 7.0 ) ) AS Variance,
  ( ( CASE WHEN ( ( SUM(CASE WHEN create_time >= @w0s
                                  AND create_time < @w0e THEN 1
                             ELSE 0
                        END) ) - ( SUM(CASE WHEN create_time >= @w1s
                                                 AND create_time < @w1e THEN 1
                                            ELSE 0
                                       END) / 7.0 ) > 0 ) THEN 1
           ELSE 0
      END ) / ( SUM(CASE WHEN create_time >= @w1s
                              AND create_time < @w1e THEN 1
                         ELSE 0
                    END) / 7.0 ) * 100.0 ) AS Percentage_Variance

However without a fiddle or some structure and data I was unable to test it beyond correct syntax. Essentially the outermost CASE statements where the problem. They needed to be removed.

Upvotes: 0

Related Questions