All Forums Database
Niesh20us 78 posts Joined 06/13
25 Sep 2014
Qualify in select statement

Hi Experts- 
I have a situation where I have to find the TRXN_DT from below table that whenever the TRXn_AMT reaches 300 or more it should return that TRXN_DT. From below table it should be 08/31/2014

ACCT_ID          SOR_ID    TRXN_DT     TRXN_POST_DT  TRXN_AMT
10000000232629865    17    8/11/2014    8/12/2014      0.50
10000000232629865    17    8/12/2014    8/13/2014      1.08
10000000232629865    17    8/12/2014    8/13/2014     35.00
10000000232629865    17    8/15/2014    8/18/2014     89.98
10000000232629865    17    8/28/2014    8/29/2014      2.17
10000000232629865    17    8/31/2014     9/2/2014    555.30 <--
10000000232629865    17    8/31/2014     9/2/2014    555.30
10000000232629865    17    8/31/2014     9/2/2014   1237.42
10000000232629865    17     9/1/2014     9/2/2014      7.99
10000000232629865    17     9/2/2014     9/3/2014     10.89
10000000232629865    17     9/6/2014     9/8/2014     89.98
10000000232629865    17     9/6/2014     9/8/2014     46.50
10000000232629865    17     9/7/2014     9/9/2014     19.07
10000000232629865    17     9/7/2014     9/9/2014     23.93
10000000232629865    17     9/7/2014     9/8/2014     49.80
10000000232629865    17     9/9/2014    9/10/2014      2.16
10000000232629865    17    9/10/2014    9/12/2014     68.66
10000000232629865    17    9/10/2014    9/12/2014     21.00
10000000232629865    17    9/21/2014    9/23/2014    122.16
10000000232629865    17    9/22/2014    9/23/2014      3.92

Can you please tell me why below query is not working or any other good way to write the query

select acct_id , sor_id , TRXN_DT,TRXN_POST_DT ,  QUALIFY  Sum(TRXN_AMT)  over (PARTITION BY ACCT_ID , SOR_ID) >= 300)  from DEBIT_SUM_TRXN
where acct_id =10000000232629865
and sor_id = 17 
QUALIFY ROW_NUMBER() OVER (PARTITION BY ACCT_ID,SOR_ID ORDER BY TRXN_DT ASC) = 1
And   sum(TRXN_AMT) >= 300

 

Niesh20us 78 posts Joined 06/13
25 Sep 2014

Help Please 

Raja_KT 1246 posts Joined 07/09
25 Sep 2014

You can do something like this, see I change only for the trn_post_dt, you can change for the trns_dte .
select acct_id,sor_id,trans_dt, case when max(trn_amt) over(partition by trn_amt order by trn_amt) >=300 then add_months(trn_post_dt - extract(day from trn_post_dt ) +1, 1) END -1 dt, trn_amt from your_table qualify max(trn_amt) over(partition by trn_amt order by trn_amt) >=300
I think you want the last day of the month. Once you get that , then you calculate the difference of date dt and your original post_dt and add to your trans_dt too.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Niesh20us 78 posts Joined 06/13
26 Sep 2014

Thanks Raja. But I am not looking for last day of the month. I want exact tran_dt whenever cumulative sum reaches 300 or more.

Niesh20us 78 posts Joined 06/13
26 Sep 2014

Ok I have got one more way . In the below query it should give one row for combination of Acct_Id and Sor_Id whenever it reaches 300 . But this query is not working can you please tell me what is wrong with below query
 

  SELECT acct_id,sor_id,TRXN_DT,  TRXN_AMT ,  CSUM(TRXN_AMT, acct_id ASC ,sor_id ASC )   

   FROM DEBIT_SUM_TRXN

   QUALIFY CUMSUM  OVER (PARTITION BY ACCT_ID,SOR_ID ORDER BY TRXN_DT ASC) >= 300

Raja_KT 1246 posts Joined 07/09
26 Sep 2014

Because of your long list I could not comprehend the issue :)
You want to sum the trasnsaction amount based on grouping of acct_id and sor_id and when it reaches 300 or more then you want to display it? What is the expected output from the above, is it only one row?
You have two rows and you use sum() and I am confused :):
10000000232629865
17
8/31/2014
9/2/2014
555.30
10000000232629865
17
8/31/2014
9/2/2014
555.30
Can you please tell me what is the final result format and values for the above?

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
26 Sep 2014

I don't fuly understand what you want, but it seems like you need the row where the cumulative sum of the transaction amount reaches 300?
Then you need to two nested QUALIFY:

SELECT ...
FROM 
 ( 
   SELECT ...
      SUM(TRXN_AMT)  
      OVER (PARTITION BY ACCT_ID , SOR_ID
            ORDER BY TRXN_POST_DT
            ROWS UNBOUNDED PRECEDING) AS cumsum
   FROM DEBIT_SUM_TRXN
   QUALIFY  cumsum >= 300
 ) AS dt
QUALIFY
   ROW_NUMBER() 
   OVER (PARTITION BY ACCT_ID,SOR_ID 
         ORDER BY TRXN_DT ASC) = 1

 

Dieter

Niesh20us 78 posts Joined 06/13
26 Sep 2014

Thanks Dieter but I want cumulative sum acct_id . sor_id and Trxn_Dt wise . THis query giving me CSUM across full table

Niesh20us 78 posts Joined 06/13
26 Sep 2014

SELECT ...

FROM

 (

   SELECT ...

      CSUM(TRXN_AMT) 

      OVER (PARTITION BY ACCT_ID , SOR_ID

            ORDER BY TRXN_POST_DT

            ROWS UNBOUNDED PRECEDING) AS cumsum

   FROM DEBIT_SUM_TRXN

   QUALIFY  cumsum >= 300

 ) AS dt

QUALIFY

   ROW_NUMBER()

   OVER (PARTITION BY ACCT_ID,SOR_ID

         ORDER BY TRXN_DT ASC) = 1

 

This is not working

Niesh20us 78 posts Joined 06/13
26 Sep 2014

Yes Raja what you said is correct I want only one row and extremely sorry for confusion

Niesh20us 78 posts Joined 06/13
26 Sep 2014

Dieter I got it Thanks :)

You must sign in to leave a comment.