All Forums Database
todil 4 posts Joined 01/14
04 Jan 2014
SQL help

Hi gurus, I am trying to generate a sql based on this scenario. please suggest how. i have a table with credit card number, transaction date and amount and merchant. I want to find a creditcard number which did transations in 3 consecutive months with same merchant with total amount >1000. eg:-a customer would have made transaction in walmart in oct, nov,dec and the total amount for 3 months should be > 1000. 

please suggest. 

Thanks

dnoeth 4628 posts Joined 11/04
05 Jan 2014
SELECT card_number,
   merchant, 
   EXTRACT(YEAR FROM trans_date) AS trans_year,
   EXTRACT(MONTH FROM trans_date) AS trans_month,
   trans_year * 12 + trans_month AS trans_ym,
   SUM(SUM(amount)) 
   OVER (PARTITION BY card_number, merchant
         ORDER BY trans_ym
         ROWS 2 PRECEDING) AS sumamt
FROM trans
GROUP BY 1,2,3
QUALIFY sumamt > 1000
    AND trans_ym - MIN(trans_ym) OVER (PARTITION BY card_number, merchant
                                       ORDER BY trans_ym
                                       ROWS 2 PRECEDING) = 2

This should be what you want: calculate the amount per month/creditcard/merchant and then check for three consecutive months and a sum(amount) > 1000.
Might return multiple rows per credit card, one row for each three months period meeting your condition.

Dieter

todil 4 posts Joined 01/14
05 Jan 2014

Thanks Dieter,
Wil try and post you the update..
 

todil 4 posts Joined 01/14
06 Jan 2014

Dieter, I tried and iam getting this error. Failed 3504 I am getting selected non-aggregrated values must be part of associated group.
 
SELECT acct_id,
MRCH_NM,
EXTRACT(YEAR FROM trxn_post_dt) AS trans_year,
EXTRACT(MONTH FROM trxn_post_dt) AS trans_month,
trans_year * 12 + trans_month AS trans_ym,
SUM(SUM(trxn_amt))
OVER (PARTITION BY acct_id, MRCH_NM
ORDER BY trans_ym
ROWS 2 PRECEDING) AS sumamt
FROM POSTD_TRXN
GROUP BY 1,2,3
QUALIFY sumamt > 1000
AND trans_ym - MIN(trans_ym) OVER (PARTITION BY acct_id, MRCH_NM
ORDER BY trans_ym
ROWS 2 PRECEDING) = 2
 
 

dnoeth 4628 posts Joined 11/04
06 Jan 2014

Strange, this should work as-is. Maybe the parser is a bit confused, try GROUP BY 1,2,3,4
 
Btw, what's your TD release?

Dieter

todil 4 posts Joined 01/14
06 Jan 2014

Dieter, Actually it worked..
I gave GROUP BY 1,2,3,4,5

bvfrompc 3 posts Joined 01/14
09 Jan 2014

Wow, what an amazing site, this is my first visit and have already almost found the answer to my question.  I have a similar problem to code.  In my case I have ID, Date for customer service calls.  I need to flag those in the last year that called four times in a seven day window.
Data looks like this
ID Date
123 2013-02-03
456 2013-02-03
123 2013-02-04
123 2013-02-04
456 2013-02-05
456 2013-02-05
123 2013-02-09
456 2013-02-11
I would like to return the ID and last date of the 7 day window, for this data I would only want:
123 2013-02-06
It is likely that a customer has had this occurance multiple times over the year, not a problem as I could grab the ID and max(date)
Thank you in advance,
Bob

bvfrompc 3 posts Joined 01/14
09 Jan 2014

I apologize in advance, I would be looking for the following to be returned:
123 2013-02-09

dnoeth 4628 posts Joined 11/04
09 Jan 2014

Hi Bob,
no need to apologize :-)
Rephrasing your condition: Find rows where the number of days between the current date and the date three rows before is less than or equal to seven.

SELECT * FROM tab
QUALIFY
   dt - MIN(dt) 
        OVER (PARTITION BY id
              ORDER BY dt
              ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING) <= 7

 

Dieter

bvfrompc 3 posts Joined 01/14
10 Jan 2014

Thank you Dieter!  I'm an analyst who has brute force coded in SQL for years and never had the elegance of the qualify/partition combo.  Thx!

Niesh20us 78 posts Joined 06/13
13 Jan 2014

Hi Dieter,
I was following this thread and trying to understand the actual use of ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING
When i ran

SELECT * FROM tab

QUALIFY

   dt - MIN(dt) 

        OVER (PARTITION BY id

              ORDER BY dt

              ROWS 3 PRECEDING ) <= 7

it gives me below answer set
 
 id dt
123 2/3/2013
123 2/4/2013
123 2/5/2013
123 2/9/2013
456 2/3/2013
456 2/5/2013
456 2/6/2013
 
but when i use between for the same preceding it gives me only one row i.e. 123 2013-02-09
Could you please help me understand this logic
 
Thanks,
Nilesh

Niesh20us 78 posts Joined 06/13
13 Jan 2014

My only question is how between works here

M.Saeed Khurram 544 posts Joined 09/12
14 Jan 2014

Nilesh,
When you use BETWEEN 3 PRECEDING AND 3 PRECEDING it means that a single third row preceding the current row will be selected.
So in your case when you are at 456 2/6/2013, then the third preceding row is 123 2/9/2013.
 
 

Khurram

Niesh20us 78 posts Joined 06/13
14 Jan 2014

Thanks Khurram, ya i got your point so between does not actually means what we normally use in our sql coding.
and also when we dont use between it gives me below result
 
SELECT * FROM tab
QUALIFY dt- MIN(dt) OVER (PARTITION BY id ORDER BY dt ROWS
--BETWEEN
3 PRECEDING
-- AND 3 PRECEDING
) <= 7
 
why do i get all the rows for 123 i should only get which are preceding ( highlighted below)
 
123 2/3/2013
123 2/4/2013
123 2/5/2013
123 2/9/2013

456 2/3/2013
456 2/5/2013
456 2/6/2013
 

Niesh20us 78 posts Joined 06/13
14 Jan 2014

Can anyone please help me answer above question.

dnoeth 4628 posts Joined 11/04
14 Jan 2014

ROWS 3 PRECEDING is equivalent to ROWS BETWEEN 3 PRECEDING AND CURRENT ROW.
Simply move the functions as columns into the SELECT and check the result :-)

Dieter

Niesh20us 78 posts Joined 06/13
14 Jan 2014

Thanks Dieter:)

bhaskey 2 posts Joined 01/14
27 Jan 2014

Hi dnoeth,

Could you please explain the 5th line of your query.
"trans_year * 12 + trans_month AS trans_ym"

Thanks

bhaskey 2 posts Joined 01/14
27 Jan 2014

and also, why did you use SUM function twice
SUM(SUM(amount))
Thanks

dnoeth 4628 posts Joined 11/04
29 Jan 2014

"trans_year * 12 + trans_month AS trans_ym" calculates a sequential month number to deal with months across two years like nov/dec/jan.
"SUM(SUM(amount))": The outer SUM is a Windowed Aggregate Sum and the inner a standard aggregate Sum, calculating the sum of all amounts over a three months window.

Dieter

30 Jan 2014

I am glad join in teradata forum

Vasu

divyagolla 22 posts Joined 02/14
19 Feb 2014

 
Hi,
I had a scneario like log file will show 100 records got processed and when i query the table i can see only 99 records. I have loaded the table using BTEQ.Can anyone help me answer my question.
Thanks.
 

Adeel Chaudhry 773 posts Joined 04/08
19 Feb 2014

Is the table SET or MULTISET? You used INSERT .... SELECT for inserton?
 
Can be due to full row duplicate getting suppressed.

-- If you are stuck at something .... consider it an opportunity to think anew.

divyagolla 22 posts Joined 02/14
19 Feb 2014

Thank you. I will check it.

stringel 1 post Joined 02/14
21 Feb 2014

Export from Teradata SQL ?  How can I export out of native Teradata SQL ? 
How does Sql Assist Expot "( what happens when i click file-export ) ?
I need to write SQL  code that exports to a file -  will run from an aplication not sql assisst
thx

Adeel Chaudhry 773 posts Joined 04/08
22 Feb 2014

You can invoke FastExport script, or you can read data and generate a file in your application.
 
SQL doesnt generate file, its the application above that does it after getting the data form SQL.

-- If you are stuck at something .... consider it an opportunity to think anew.

divyagolla 22 posts Joined 02/14
19 Feb 2015

I had a scenario in which entire data from column A from table A should match with Column A from table B
(Select Offerid , product_id, apc_code from table_A) live_offer
Innerjoin 
(Select Offerid , product_id, apc_code from table_A) plan_offer
on live_offer.product_id = plan_offer.product_id
Where apc_code = apc_code
In the where condition i have check all the apc_codes that are present in table_a(plan offer) should present in table_b(live_offer)
 
say table a is having 2,4 and table b is hvaing 2,4,6 then i will take offrer id from tabke a .
in future table a may get 2,3 as table b doesnt have 3 i have to offer id from table b
Please help us with the query in where clause. Thanks in Advance.

You must sign in to leave a comment.