SQL Server Performance Forum – Threads Archive
SQL Dates Mystry-Whats HappeningHello 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 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
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.
See if this helps:http://www.sql-server-performance.com/fk_datetime.asp —
SQL Server MVP