Qualify in select statement

Database
Enthusiast

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
10 REPLIES
Enthusiast

Re: Qualify in select statement

Help Please 

Enthusiast

Re: Qualify in select statement

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.

Enthusiast

Re: Qualify in select statement

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.

Enthusiast

Re: Qualify in select statement

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

Enthusiast

Re: Qualify in select statement

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?

Senior Apprentice

Re: Qualify in select statement

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
Enthusiast

Re: Qualify in select statement

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

Enthusiast

Re: Qualify in select statement

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

Enthusiast

Re: Qualify in select statement

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