SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Counting Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tarheels4025
Starting Member

2 Posts

Posted - 06/06/2005 :  10:31:48  Show Profile  Reply with Quote
Below is a query I am working on. I am trying to make 3 count cases. The first one just counts all transactions. The second one counts only debit transactions that aren't approved.

For the third count case (one with *) I am trying to find out how many times a customer tries the same card on the same day. Is there any way to count this? A card is id_code_1 and days are by settlement_batch_number. For example if an id_code_1 shows up 3 times on a specific settlement_batch_number then it would show 3 as the count plus all the other id_code_1 that show up more time than once. IS there anyway to show the id_code_1 and how many times? If not just a total. Thanks for any help.


Use Winpayment
GO

SELECT S.card_acceptor_identification STORE,
COUNT (M.message_type) TOTAL_#_TRANS,
COUNT(CASE WHEN id_code_1 = 'DB' and response_code <> '00' THEN 1 END) DEBIT_DECLINED,
*COUNT(CASE WHEN THEN 1 END)
FROM Store S
Left Join financial_message M
On M.card_acceptor_identification = S.card_acceptor_identification
And (settlement_batch_number >= '880')

GROUP BY S.card_acceptor_identification

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 06/06/2005 :  11:18:18  Show Profile  Visit spirit1's Homepage  Reply with Quote
provide table structure with Create table statements
sample data with Insert into statements
and desired results based on your sample data.

that way you'll get the answer quickest.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/06/2005 :  11:33:29  Show Profile  Reply with Quote
Follow the directions in the link below


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

tarheels4025
Starting Member

2 Posts

Posted - 06/06/2005 :  11:38:27  Show Profile  Reply with Quote
My bad sorry the card number in the database is id_number_1 not id_code_1. Would you have any ideas without me posting all of that info. It is a pretty big database. Thank you
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/06/2005 :  11:44:50  Show Profile  Reply with Quote
It just has to be a sample...like I express in the link below.



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000