SQL Anti Logic: Usage of CASE Statement

Hello all... Reviewing queries while development and fixing performance issues in SQLs are part of my job... I get lot of queries where people have written a really bad query and thank god we have this process in place to tune such queries... You can take this as a sequel to my Post on "Anti SQL Logic"

So someone had a requirement of getting Account Statistics from a table storing statistics for every account (I have tweaked the requirement and actual query, a little for abstraction and a little to highlight the significance of improvements done in query) ... Stats are stored in periodical fashion and I can configure a period code to get statistics for a specific period say monthly or say yearly... Additionally I want stats for my current period as well as for my previous period...

Probably a naive approach that come first in mind i below:

1) I get a union of all the possible combinations for previous period and current period. Below is basic query for that...


SELECT AST.* FROM {?schemaName}.ACCSTATSTABLE AST, {?schemaName}.ACCOUNTTABLE ACC
WHERE ACC.ACCOUNTNUMBER = AST.ACCOUNTNUMBER
AND ACC.ACCOUNTNUMBER = '{?inputACCOUNTNUMBER}'
AND 'Q' = (SELECT VALUE FROM CONFIGTABLE WHERE CONFIGNAME='StatsPeriod' )
AND {?FORMONTH} > (AST.STATSPERIOD - 1) * 3 +1 //for current period
--AND {?FORMONTH} <= (AST.STATSPERIOD-1) * 3 //for previous period AND AST.FORYEAR = '{?FORYEAR}'

UNION

SELECT AST.* FROM {?schemaName}.ACCSTATSTABLE AST, {?schemaName}.ACCOUNTTABLE ACC
WHERE ACC.ACCOUNTNUMBER = AST.ACCOUNTNUMBER
AND ACC.ACCOUNTNUMBER = '{?inputACCOUNTNUMBER}'
AND 'H' = (SELECT VALUE FROM CONFIGTABLE WHERE CONFIGNAME='StatsPeriod' )
AND {?FORMONTH} > (AST.STATSPERIOD - 1) * 6 +1 //for current period
AND {?FORMONTH} <= AST.STATSPERIOD * 6 // for previous period AND AST.FORYEAR = {?FORYEAR}

UNION

SELECT AST.* FROM {?schemaName}.ACCSTATSTABLE AST, {?schemaName}.ACCOUNTTABLE ACC
WHERE ACC.ACCOUNTNUMBER = AST.ACCOUNTNUMBER
AND ACC.ACCOUNTNUMBER = '{?inputACCOUNTNUMBER}'
AND 'Y' = (SELECT VALUE FROM CONFIGTABLE WHERE CONFIGNAME='StatsPeriod' )
AND AST.FORYEAR ={?FORYEAR} //for curr period
-- AND AST.FORYEST={?FORYEAR}-1 //for prev period

2) I also get a result of last month/quarter and 2nd-half of previous year result sets (so that if I pass 1st month as my input then I shall get previous years last month ;-o )

3) I join these results sets

4) Use an inner query to get what is the configured period code and then I get stats only for that period code...

Well this is quite procedural and algorithmic... SQLs are not procedural (and anyways I am personally very much against usage of PLs codes with RDBMS especially the cursor based things, well that is debated topic)...

Well I wrote a Structured Query to get what was required making the best I can make of CASE:

1) Select ALL the columns for statitics twice
i) Once actual value if it your period value (e.g. 4th quarter) else select it as 0
ii) Select stats column as 0 if it matches your period value else select actual value
This will help you categorize stats in column wise fashion where current period columns will be holding actual value and 2nd set of columns will be holding data for other periods.

2) now you need to filter the current period and previous data
i) Filtering current period data is pretty easy as you know the period number
ii) For filtering the period number for previous period I used below case logic

(CASE 'M'
WHEN 'M' THEN 2
WHEN 'Q' THEN (2-1/3)+1
WHEN 'H' THEN (2-1/6)+1
WHEN 'Y' THEN AST.STATSPERIOD
END) in
( {PeriodNumber}, //Current Period number
(CASE {?FORMONTH} WHEN 1 THEN //Logic to arrive at previous period
case {PERIODCODE} when 'M' THEN 12 //if monthly then prev month is 12
WHEN 'Q' THEN 4 //if quarterly then prev quarter is 3
WHEN 'H' THEN 2 //if halfyearly then prev period is 2
ELSE {PeriodNumber} //if yearly, prev period number does not matter
END
ELSE {PeriodNumber}-1 end) //finally it should be input period number-1
)
AND AST.FORYEAR IN( {?FORYEAR},
(CASE {?PERIODCODE} WHEN 'Y' THEN {?FORYEAR}-1
else
(case when {PeriodNumber}=1 then {?FORYEAR}-1
//If my input number is 1 then I need to consider prev year stats as well
ELSE {?FORYEAR} END)
END)

3) Now I have got 0 as prev period stats in rows which belong to current period and 0 as current period stats for rows which belong to prev year. All I need to do is sum
SELECT
SUM(temp.CURR_MAXIMUM_BALANCE),
SUM(temp.CURR_MINIMUM_BALANCE),
SUM(temp.CURR_AVG_BALANCE),
SUM(temp.CURR_CREDIT_COUNTER ),
SUM(temp.CURR_INTERESTPAID),
SUM(temp.CURR_CHARGES),
SUM(temp.PREV_MAXIMUM_BALANCE),
SUM(temp.PREV_MINIMUM_BALANCE),
SUM(temp.PREV_AVG_BALANCE),
SUM(temp.PREV_CREDIT_COUNTER ),
SUM(temp.PREV_INTERESTPAID),
SUM(temp.PREV_CHARGES
from
(select
case when AST.STATSPERIOD=2 then MAXIMUM_BALANCE else 0 END as CURR_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then MINIMUM_BALANCE else 0 END as CURR_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then AVG_BALANCE else 0 END as CURR_AVG_BALANCE,
case when AST.STATSPERIOD=2 then CREDIT_COUNTER else 0 END as CURR_CREDIT_COUNTER ,
case when AST.STATSPERIOD=2 then INTERESTPAID else 0 END as CURR_INTERESTPAID,
case when AST.STATSPERIOD=2 then CURR_CHARGES else 0 END as CURR_CHARGES,
case when AST.STATSPERIOD=2 then 0 ELSE MAXIMUM_BALANCE END as PREV_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then 0 ELSE MINIMUM_BALANCE END as PREV_MAXIMUM_BALANCE,
case when AST.STATSPERIOD=2 then 0 ELSE AVG_BALANCE END as PREV_AVG_BALANCE,
case when AST.STATSPERIOD=2 then 0 ELSE CREDIT_COUNTER END as PREV_CREDIT_COUNTER ,
case when AST.STATSPERIOD=2 then 0 ELSE INTERESTPAID END as PREV_INTERESTPAID,
case when AST.STATSPERIOD=2 then 0 ELSE PREV_CHARGES END as PREV_CHARGES,
FROM {?schemaName}.ACCOUNTSTATISTICS AST
[where statement discussed above]
group by ACCOUNTID


I guess I have made it very boring and complex to understand... But I hope it will help people understand how flexible can be usage of CASE statements...

I am looking at putting a content on improving INSERT performance... (this again has come from processing of rows from temp tables, where data is populated in bulk and deleted in bulk)...