What seems like an easy task is kickin my butt. :(

Question

Post reply

What seems like an easy task is kickin my butt. :(

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

john-902052

I entered all the data (original and additional) and ran your code from the forum entry of 2:12 PM

declare @StartDate datetime

declare @EndDate datetime

set @StartDate = '2/1/2010'

set @EndDate = '2/20/2010'

select convert(datetime, convert(char(10), TransDate, 101)) as tDate, sum(CR) as dailyTotal, paytype,

case

when PayType = 1 then 'Cash'

when PayType = 2 then 'Credit'

when PayType = 3 then 'Check'

end as paytypedesc

from ledger

where CR > 0 and TransDate between @StartDate and @EndDate

group by convert(datetime, convert(char(10), TransDate, 101)), paytype, TransDate

order by transDate

And these are the results OF MY running on SQL 2005 Express:

tDate dailyTotalpaytypepaytypedesc

2010-02-10 00:00:00.000180.001Cash

2010-02-10 00:00:00.0001800.002Credit

2010-02-10 00:00:00.00016.003Check

2010-02-11 00:00:00.000180.001Cash

2010-02-11 00:00:00.0001800.002Credit

2010-02-11 00:00:00.00016.003Check

And still do NOT get your unacceptable answer ? ? ?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

john.delahunt Right there with Babe Points: 714 More actions · Answer 2

bitbucket: You've haven't quite got the same data as the original post; the original post has dates and times in the TransDate column (and it makes a world of difference when trying to group on a datetime column!)

john-902052 SSCommitted Points: 1578 More actions · Answer 3

Outrageous? I created a new DB from my DDL and used the inserts and still get the wrong answer on SQL EXpress 2005. I am really lost now and need to finish this thing up. :w00t:

john.delahunt Right there with Babe Points: 714 More actions · Answer 4

Outrageous? I've always considered consistency to be a good thing. The same bad SQL run against the same data should always give you the same bad result.

Catalin Enescu SSC Veteran Points: 276 More actions · Answer 5

truncate the time from your date field.

try this:

select cast(cast(TransDate as int) as smalldatetime) as TransDate, sum(CR) as dailyTotal, paytype,
case
when PayType = 1 then 'Cash'
when PayType = 2 then 'Credit'
when PayType = 3 then 'Check'
end as paytypedesc
from ledger
where CR > 0 and TransDate between @StartDate and @EndDate
group by cast(cast(TransDate as int) as smalldatetime) , paytype
order by cast(cast(TransDate as int) as smalldatetime)