SQL Server Performance

SQL Dates Mystry-Whats Happening

Discussion in 'T-SQL Performance Tuning for Developers' started by maninder, Jun 1, 2005.

  1. maninder New Member

    Hello guys, I was composing a SQL to retrieve the count(Phone),sum(amountbilled)numbers from the Billinghistory Table based on Dates pretaining to Last Month. This is for a Report.
    My Query Goes Like this:

    select count(phone), sum(amountbilled) From billinghistory
    Where phone in
    (
    Select phone from billinghistory
    Where dttimestamp >= dateadd(ms,+3,DATEADD(mm, DATEDIFF(mm,0,getdate() )-2, 0))
    and dttimestamp < =dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )-1, 0))
    and fromcurrentbill=1
    and stroffer = 'PS'

    )

    whats Happening is if i execute the underline marked Code it retrieves 5 Phonenumbers and Total of 25.00 in amount Billed only and if i execute the complete code it retrieves me 15 PhoneNumbers and 220 in billed Amounts.
    To my best Knowledge the 5 Phonenumbers and 25$ is the best possible Answer.
    Maybe i shouldnt do a subquery..hmmmmm
    Theres Something very fishy That i cant seem to figure out. Pl HELP!

    Maninder
  2. Adriaan New Member

    Your query returns the sum of amountbilled for the whole of billinghistory, for all phone numbers that have a billinghistory entry with the given criteria, and effectively the rowcount, repeated for each entry for those phone numbers in billinghistory.

    The summing is not limited to the given criteria: all billingamounts for the selected phone numbers will be summed.

    The result is repeated because you're not grouping on phone.

    (1) Write a simple aggregate query, with simple criteria where you know what kind of results you will get (like phone = '12345').

    (2) Replace the simple criteria with the complex criteria.
  3. FrankKalis Moderator

Share This Page