T-SQL Query to calculate Settele and withdraw amount in MSSQL- SERVER

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

• Learning_DBAdmin: Can you provide some sample data in the table and the desired output. Alias name for table is not required as you are having only one table.

Answer 1

1. Avoid <> in where condition.Instead use >-1
2. When you are already filtering anything like where s.cfcifno<>-1 ,then no need of putting same in case 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 ?

• Pantea: Thanks for your answer . I'm not facing any performance issues right now . I just wanted to know whether there is better ways than using case statement .