Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Cool Unanswered: Like Statement SQL Suery



    )))


    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




  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You can also use BETWEEN:
    Code:
    ...
       WHERE TBLCallInfo.Date BETWEEN '2005-01-01' 
                                  AND '2005-02-01'
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    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' .

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    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

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Jan 2005
    Posts
    2

    Thumbs up Thank you for the Replies..

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

    Thanks a bunch...dbforums
    Last edited by SillyOne; 01-19-05 at 17:11. Reason: Realizes answer...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •