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

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 ?