Please help : Query to count number of voucher cards used

Analytics

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

2 REPLIES
Highlighted
Junior Contributor

Re: Please help : Query to count number of voucher cards used

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?

Teradata Employee

Re: Please help : Query to count number of voucher cards used

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;