0

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

  • 1
    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. – Learning_DBAdmin Jul 28 '19 at 11:23
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 ?

  • 1
    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 . – Pantea Tourang Jul 29 '19 at 8:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.