SQL Server Dates with previous month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Server Dates with previous month

Author

Topic

maninder
Posting Yak Master

100 Posts

Posted - 2005-06-01 : 10:42:13

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

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-01 : 11:02:40

You dont need the subquery!
Your results are higher because the totals WILL be across other dates/months other than the range you specified

ie Its totalling ALL the data from those phone numbers in the subquery

Andy

Beauty is in the eyes of the beerholder

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-01 : 11:05:31

Oh, and your subquery is looking at APRIL and not May as you specified

quote:

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.

Run this in QA (this is part of your WHERE clause)
SELECT dateadd(ms,+3,DATEADD(mm, DATEDIFF(mm,0,getdate() )-2, 0))
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )-1, 0))

Andy

Beauty is in the eyes of the beerholder

maninder
Posting Yak Master

100 Posts

Posted - 2005-06-01 : 11:18:02

Yes i have already tried all of the options as i have written the Query

When i restrict the Query qith a Where in clause it Should return me all the Phone Numbers in the Query.
as per your Statement it Should return me all the Phone numbers Which are 1014329 in the database.
What the Query Does is Something Strange with the count(Phone),Sum(amountbilled) is it Returns only 15 Phones and 250$ as the sum of Those restricted with the where in Clause.
It Should return is the 2 Phonenumbers and 15$ based on the Date Range i have specified.

Maninder

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-01 : 11:28:04

It isnt doing something strange, its doing exactly as it should. Have you looked at the 15 phones its returning - i bet its ALL duplicates.

Tip before aggregating make sure the data is EXACTLY what it should be

Try this - how many records are returned & how many distinct phone numbers are there


select phone, amountbilled, dttimestamp 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')

And now try this


select count(phone), sum(amountbilled) 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'

Andy

Beauty is in the eyes of the beerholder

maninder
Posting Yak Master

100 Posts

Posted - 2005-06-01 : 11:33:13

Thanks Man, I got the Picture Crystal Clear. Amazing the Developer have SCRE*** the Database Records Again.

Thanks for you Help.

Maninder

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-01 : 12:24:34

Amazing the lack of data integrity constraints in some databases...

>> Amazing the Developer have SCRE*** the Database Records Again
Raise an error on them

if they try it again !

rockmoose

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-01 : 13:41:24

...............Again!!!!!!

Oh dear oh dear
DROP DEVELOPER

Beauty is in the eyes of the beerholder

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-01 : 13:51:53

/Fires the DB

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
No Rows Returned

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-06-01 : 14:18:51

Yep, a database without proper unique constraints and/or primary keys defined and therefore with duplicate and messy data -- must be the developers fault ! Can't blame a DBA for that one!

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources