Board index » delphi » SQL Count

SQL Count


2003-11-06 12:34:05 AM
delphi223
I have 2 tables: Transactions and Payments. Transactions has a DateTime
Stamp and TransactionNumber fields and Payments has TransactionDate and
TransactionNumber fields.
I am trying to get a transaction count from a given time and date range, but
I am not getting the correct results. I tried using Distinct to get the
correct result, which helped, but did not work either.
My code looks something like this:
SQL.Add('Select Count(Distinct P.TransactionNumber) As TransCount
From Payments P, Transactions T');
SQL.Add('Where (P.TransactionDate = Cast(T.Date_Time As Date)) And
(P.TransactionDate < "05/13/2003")');
I will be adding the time constraints to this after I get this part to work.
How can I get the correct Count of Transactions for a given date/time range?
I have tried dozens of ways, but none work when I go to multiple days.
Thanks for any help.
 
 

Re:SQL Count

Gary Rosenberg writes:
Quote
I have 2 tables: Transactions and Payments. Transactions has a
DateTime Stamp and TransactionNumber fields and Payments has
TransactionDate and TransactionNumber fields.

I am trying to get a transaction count from a given time and date
range, but I am not getting the correct results. I tried using
Distinct to get the correct result, which helped, but did not work
either.

My code looks something like this:

SQL.Add('Select Count(Distinct P.TransactionNumber) As
TransCount From Payments P, Transactions T');
SQL.Add('Where (P.TransactionDate = Cast(T.Date_Time As
Date)) And (P.TransactionDate < "05/13/2003")');

I will be adding the time constraints to this after I get this part
to work.

How can I get the correct Count of Transactions for a given date/time
range? I have tried dozens of ways, but none work when I go to
multiple days.
From your sample above it appears you only want a count of transactions that
have one or more payments? If this is correct then you should be counting
the Transaction records, not the Payment records. Also, are you interested
in transactions before a specific date or payments before a specific date?
Perhaps...
select count(T.TransactionNumber)
from Transaction T
inner join Payments P
on T.TransactionNumber = P.TransactionNumber
where T.Date_Time < '05/13/2003'
Small detail: use *single* quotes on literal values like the date strings.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson
 

Re:SQL Count

Thanks Wayne,
That's closer, but still not quite right. Here is an example of what I have
and what I am trying to accomplish:
Transactions Table:
Date_Time TransNum Item
1/2/2003 10:00:25am 1 Item321
1/2/2003 10:00:56am 1 Item089
1/2/2003 10:01:22am 1 Item654
1/2/2003 11:23:32am 2 Item241
1/2/2003 12:45:57pm 3 Item432
1/3/2003 11:21:32am 1 Item322
1/3/2003 3:34:22pm 2 Item 242
Payments Table
Date TransNumber Amt
1/2/2003 1 $128.89
1/2/2003 2 $343.90
1/2/2003 3 $78.00
1/3/2003 1 $34.78
1/3/2003 2 $365.48
With this data I need to get how many transactions (or customers, not how
many items per transaction) occurred per hour for a given fate range. The
date range and time constraints I can get, but when I do a COUNT it always
counts items instead of transactions. I figured if I use the payment table
linked to the Transactions table for times, I can eliminate the items per
transaction, but that still happens.
With the code you gave me, the transaction count for <= 1/3/2003 would be 7
when I want 5. Later on I will go not only by date range, but by time as
well.
If Payments Table had a time stamp, this would be simple, but I can not seem
to combine the tables to get the correct answer.
Any way, thanks for the help and I would greatly appreciate it if you could help
even more.
Gary
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes
Quote
Gary Rosenberg writes:
>I have 2 tables: Transactions and Payments. Transactions has a
>DateTime Stamp and TransactionNumber fields and Payments has
>TransactionDate and TransactionNumber fields.
>
>I am trying to get a transaction count from a given time and date
>range, but I am not getting the correct results. I tried using
>Distinct to get the correct result, which helped, but did not work
>either.
>
>My code looks something like this:
>
>SQL.Add('Select Count(Distinct P.TransactionNumber) As
>TransCount From Payments P, Transactions T');
>SQL.Add('Where (P.TransactionDate = Cast(T.Date_Time As
>Date)) And (P.TransactionDate < "05/13/2003")');
>
>I will be adding the time constraints to this after I get this part
>to work.
>
>How can I get the correct Count of Transactions for a given date/time
>range? I have tried dozens of ways, but none work when I go to
>multiple days.

From your sample above it appears you only want a count of transactions
that
have one or more payments? If this is correct then you should be counting
the Transaction records, not the Payment records. Also, are you interested
in transactions before a specific date or payments before a specific date?

Perhaps...
select count(T.TransactionNumber)
from Transaction T
inner join Payments P
on T.TransactionNumber = P.TransactionNumber
where T.Date_Time < '05/13/2003'

Small detail: use *single* quotes on literal values like the date strings.

--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson


 

Re:SQL Count

Gary Rosenberg writes:
Quote

That's closer, but still not quite right. Here is an example of what
I have and what I am trying to accomplish:

Transactions Table:
Date_Time TransNum Item
1/2/2003 10:00:25am 1 Item321
1/2/2003 10:00:56am 1 Item089
1/2/2003 10:01:22am 1 Item654
1/2/2003 11:23:32am 2 Item241
1/2/2003 12:45:57pm 3 Item432
1/3/2003 11:21:32am 1 Item322
1/3/2003 3:34:22pm 2 Item 242

Payments Table
Date TransNumber Amt
1/2/2003 1 $128.89
1/2/2003 2 $343.90
1/2/2003 3 $78.00
1/3/2003 1 $34.78
1/3/2003 2 $365.48

With this data I need to get how many transactions (or customers, not
how many items per transaction) occurred per hour for a given fate
range. The date range and time constraints I can get, but when I do
a COUNT it always counts items instead of transactions. I figured if
I use the payment table linked to the Transactions table for times, I
can eliminate the items per transaction, but that still happens.
If you do not actually need any data from the payments table, then including
it in the query only complicates things and cannot solve the problem.
How about:
select count(*)
from Transaction T
where T.Date_Time < '05/13/2003'
group by extract(hour from T.Date_Time), T.TransactionNumber
With this, transactions #1 and #2 will be counted once in 2 different hours,
but if you want distinct transactions per hour then that should be correct.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson
 

Re:SQL Count

I've played around with this for awhile, but I can not still get it to work.
When I use the code below, I get capability not supported. it is coming from
the extract in the group by clause. I have tried different variations of te
code to try to get the answer, but I am lost.
I'm using the BDE with Paradox tables.
Any ideas?
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes
Quote
Gary Rosenberg writes:
>
>That's closer, but still not quite right. Here is an example of what
>I have and what I am trying to accomplish:
>
>Transactions Table:
>Date_Time TransNum Item
>1/2/2003 10:00:25am 1 Item321
>1/2/2003 10:00:56am 1 Item089
>1/2/2003 10:01:22am 1 Item654
>1/2/2003 11:23:32am 2 Item241
>1/2/2003 12:45:57pm 3 Item432
>1/3/2003 11:21:32am 1 Item322
>1/3/2003 3:34:22pm 2 Item 242
>
>Payments Table
>Date TransNumber Amt
>1/2/2003 1 $128.89
>1/2/2003 2 $343.90
>1/2/2003 3 $78.00
>1/3/2003 1 $34.78
>1/3/2003 2 $365.48
>
>With this data I need to get how many transactions (or customers, not
>how many items per transaction) occurred per hour for a given fate
>range. The date range and time constraints I can get, but when I do
>a COUNT it always counts items instead of transactions. I figured if
>I use the payment table linked to the Transactions table for times, I
>can eliminate the items per transaction, but that still happens.

If you do not actually need any data from the payments table, then
including
it in the query only complicates things and cannot solve the problem.

How about:

select count(*)
from Transaction T
where T.Date_Time < '05/13/2003'
group by extract(hour from T.Date_Time), T.TransactionNumber

With this, transactions #1 and #2 will be counted once in 2 different
hours,
but if you want distinct transactions per hour then that should be
correct.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson