All Forums Analytics
tsukmana 1 post Joined 01/14
04 Jan 2015
Please help : Query to count number of voucher cards used

Hi All,
I am still new in Teradata.  Is anybody can help  how to write SQL to count number of voucher cards have been used.
I have two tables :
table A : contain Sequences of voucher card sold to market
From_seq_nbr        to_seq_nbr
------------------    ------------------
100                        999
5000                      6000
etc..
table B : contain list of voucher card nbr has been used
seq_nbr
-------
200
300
5500
.. etc
so the report will be like this
From_seq_nbr        to_seq_nbr        nbr_used
-------------------     ----------------    -----------
100                        999                       2
5000                      6000                     1

 

Is there somebody can help how to write the query ? the issue here that I have millions records of table B and thousand records of table A

 

Thanks for your help

Teja

 

dnoeth 4628 posts Joined 11/04
05 Jan 2015

Hi Teja,
did you try a simple LEFT JOIN ON seq_nbr BETWEEN From_seq_nbr AND to_seq_nbr?
Of course this is a product join, but CPU usage might be ok.
 
Otherwise:
Is there a fixed range for between From_seq_nbr and to_seq_nbr?
How often do you need to run this query?
Do you need to code this as a single query?

Dieter

davidchanty 2 posts Joined 02/13
07 Jan 2015

This one can be done by a simple inner join query with a group by. 
 
SELECT
 tableA.From_seq_nbr, tableA.to_seq_nbr, COUNT(0) AS nbr_used
FROM tableA, tableB
WHERE tableB.seq_nbr BETWEEN tableA.From_seq_nbr AND tableA.to_seq_nbr
GROUP BY 1,2
ORDER BY 1,2;
 

You must sign in to leave a comment.