Query for a report

Question

Query for a report

D. Janjic

Grasshopper

Points: 17
More actions
February 21, 2007 at 12:08 pm

#369607

Hi
I really hope someone can assist me with this. I need a query that'll return data that looks something like:
Card Account Card Number #Transactions March April May June July ...
-------------------- ------------------- -------------------- --------- ------- ------ ------- -------
Card Account comes from Table A
Card Number from Table B
# of transactions from Table C
For Months (march - december) a total number of transactions is needed and this comes from Table C.
What confuses me is that I need to get Card Account Number and the matching Card Number, then I think some sort of a loop needs to be done to get the data broken down by months.
Any suggestions are appreciated.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TeeGee Say Hey Kid Points: 697 More actions · Answer 1

Really need some more information here. What are the specific fields for tables a, b, and c. I assume there are relationships set up between them.

Regards

Terry

Best Regards
Terry

D. Janjic Grasshopper Points: 17 More actions · Answer 2

Thanks for replying.

Table A has a PersonId field which Table B also has. Table B has a CardNumber field which Table C also has.

Those are the fields that I've been using so far for other reports uin order to connect the tables if I needed.

Table C stores all transactions done on cards as well as type of each transaction (ATM or POS), whether it was approved or not and the exact date and time of each transaction.

What this report is asking for is to get a summary for every Card Account Number and Card Number by month the number of transactions that were done.

Thanks.

itzik-408638 SSC Rookie Points: 30 More actions · Answer 3

I believe that what you needed:

select

a.CardAccount,

b.CardNumber,

MarchCount = (select count(*) from TableC c (nolock)

where c.TransactionDate between '03/01/2006' and '03/31/2006 23:59:59.999'

and c.CardNumber = b.CardNumber),

AprilCount,

...

....

DecemberCount

from TableA a (nolock)

inner join TableB b (nolock) on b.PersonID = a.PersonID

Itzhak

D. Janjic Grasshopper Points: 17 More actions · Answer 4

That is exactly it.

Thank you very much Itzhak

Much appreciated.