Like Statement SQL Suery

SillyOne · #1 (**permalink**) 01-19-05, 12:10

)))

This is a simple Like statement.. I have this table call tblCallInfo with a couple of fields in it.. like Id, Date, Calls, PaidCalls, etc..

I wrote this query where I can add up the total of each field pertaining to a certain date typed in or selected such as (it's actually longer but cut off most of the field so it won't seem so long here:

SELECT TBLCallInfo.Date, Sum(TBLCallInfo.PaidCalls) AS SumOfPaidCalls, Sum(TBLCallInfo.FreeCalls) AS SumOfFreeCalls
FROM TBLCallInfo
WHERE (((TBLCallInfo.Date)=#1/3/2005#))
GROUP BY TBLCallInfo.Date;

For this query I queried the to a certain date, but I will have a couple of records from one Month... Is there anyway I can query this Select statement with the total of each field instead of from one date but from all date in that month...

I know it can be done with a statement like:

SELECT TBLCallInfo.Date, Sum(TBLCallInfo.PaidCalls) AS SumOfPaidCalls, Sum(TBLCallInfo.FreeCalls) AS SumOfFreeCalls
FROM TBLCallInfo
-----------------------------------------------------
// *WHERE (((TBLCallInfo.Date)=#1/3/2005#))

WHERE (((TBLCallInfo.Date) LIKE '01/%')) or "'01/%"

//* 1/3/2005#))
------------------------------------------------------
GROUP BY TBLCallInfo.Date;

Having trouble with the LIKe statement... any help appreciated.. Thank you.

Zoila
sillyone8004@yahoo.com

Pat Phelan · #2 (**permalink**) 01-19-05, 13:15

The most efficient way to do this would be something like:

Code:

SELECT
   TBLCallInfo.Date  -- drop this line to get one total for month
,  Sum(TBLCallInfo.PaidCalls) AS SumOfPaidCalls
,  Sum(TBLCallInfo.FreeCalls) AS SumOfFreeCalls
   FROM TBLCallInfo
   WHERE '2005-01-01' <= TBLCallInfo.Date
      AND TBLCallInfo.Date < '2005-02-01'
   GROUP BY TBLCallInfo.Date  -- drop this line to get one total for month

-PatP

LKBrwn_DBA · #3 (**permalink**) 01-19-05, 13:47

You can also use BETWEEN:

Code:

...
   WHERE TBLCallInfo.Date BETWEEN '2005-01-01' 
                              AND '2005-02-01'

urquel · #4 (**permalink**) 01-19-05, 14:20

Just remember that BETWEEN '2005-01-01' AND '2005-02-01' is equivalent to >= '2005-01-01' AND <= '2005-02-01'. So if you want to exclude 02-01, use BETWEEN '2005-01-01' AND '2005-01-31' .

Pat Phelan · #5 (**permalink**) 01-19-05, 14:35

Quote:

Originally Posted by urquel

Just remember that BETWEEN '2005-01-01' AND '2005-02-01' is equivalent to >= '2005-01-01' AND <= '2005-02-01'. So if you want to exclude 02-01, use BETWEEN '2005-01-01' AND '2005-01-31' .

But that can be problematic if there are times in the dates, since most of the last day gets lost. I think that my answer is easier to read, and harder to get fouled up.

-PatP

LKBrwn_DBA · #6 (**permalink**) 01-19-05, 15:27

Quote:

Originally Posted by Pat Phelan

But that can be problematic if there are times in the dates, since most of the last day gets lost. I think that my answer is easier to read, and harder to get fouled up.
-PatP

If there are times involved, the best answer is:

BETWEEN '2005-01-01' AND '2005-02-01'

Because it implies FROM 2005-01-01 00:00:00 TO 2005-02-01 00:00:00

SillyOne · #7 (**permalink**) 01-19-05, 15:44

I used the Between Function with a Where clause because the Group by didn't work...

Thanks a bunch...dbforums

Internet Services

Web Development

Digital Marketing

Consumer Tech