Question :
I have a table with below structure :
FactSatnaEvent(OPRDATE date,OPRCOD int, voucherno int, SATNAPAYATYPE int, CFCIFNO int , AMOUNT int)
based on this table I wrote this query
SELECT
S.OPRDATE ,
S.CFCIFNO ,
SUM(CASE WHEN S.SATNAPAYATYPE=1 THEN S.AMOUNT ELSE 0 END) AS SATNA_DEPOSIT_AMNT,
SUM(CASE WHEN S.SATNAPAYATYPE=2 AND
S.OPRCOD=0 AND
S.voucherno<>-1 THEN S.AMOUNT ELSE 0 END) AS SATNA_WITHDRAW_AMNT,
COUNT(CASE WHEN S.SATNAPAYATYPE=1 THEN 1 ELSE NULL END) AS SATNA_DEPOSIT_COUNT,
COUNT(CASE WHEN S.SATNAPAYATYPE=2 AND
S.OPRCOD=0 AND
S.voucherno<>-1 THEN 1 ELSE NULL END) AS SATNA_WITHDRAW_COUNT
from factsatnaevent s
where s.cfcifno<>-1
GROUP BY S.OPRDATE ,
S.CFCIFNO ;
I Just want to know if there are better way to write this query .
I need to have this output
OPRDATE CFCIFNO SATNA_DEPOSIT_AMNT SATNA_WITHDRAW_AMNT SATNA_DEPOSIT_COUNT SATNA_WITHDRAW_COUNT
Please tell if any further information is required. Thanks in advance
Answer :
- Avoid
<>
in where condition.Instead use>-1
- When you are already filtering anything like
where s.cfcifno<>-1
,then no need of putting same incase condition
because that condition is already filtered.
Your query seem ok to me,
SELECT
S.OPRDATE ,
S.CFCIFNO ,
SUM(CASE WHEN S.SATNAPAYATYPE=1 THEN S.AMOUNT ELSE 0 END) AS SATNA_DEPOSIT_AMNT,
SUM(CASE WHEN S.SATNAPAYATYPE=2 AND
S.OPRCOD=0
THEN S.AMOUNT ELSE 0 END) AS SATNA_WITHDRAW_AMNT,
COUNT(CASE WHEN S.SATNAPAYATYPE=1 THEN 1 ELSE NULL END) AS SATNA_DEPOSIT_COUNT,
COUNT(CASE WHEN S.SATNAPAYATYPE=2 AND
S.OPRCOD=0
THEN 1 ELSE NULL END) AS SATNA_WITHDRAW_COUNT
from factsatnaevent s
where s.cfcifno>-1
GROUP BY S.OPRDATE ,
S.CFCIFNO ;
Are you facing any performance issue ?