Huge problem with Group by

Question

Huge problem with Group by

rutherfordsmith

SSC Journeyman

Points: 82
More actions
November 8, 2006 at 1:49 am

#115040

Hey Guys,
I have a huge problem. I have a user defined function. When I call the function on more than one field within a group by clause it returns the same result for the calculation on two different fields (the first result).
Basically it's a function to calculate the date from number of seconds. (It's just a weird way our database stores some dates :blink
Create Function OffsetCalc (@Entered_Offset as float,@Offset as float)
Returns datetime
as
Begin
return dateadd(ss,@Entered_offset+@Offset,'1980-03-01')
End
create table tbl_dummy1 (entered_offset float,offset1 float,offset2 float,dcount float)
Insert into tbl_dummy1 values (827163459,367180,413126,1)
Query1
Select dbo.OffsetCalc(entered_offset,offset1) as Date1,
dbo.OffsetCalc(entered_offset,offset2) as date2,
dateadd(ss,Entered_offset+offset1,'1980-03-01') as realDate1,
dateadd(ss,Entered_offset+offset2,'1980-03-01')as RealDate2,
(dcount) as test_count
from tbl_dummy1
Correct results :
Date1 2006-05-21 21:37:19.000
Date2 2006-05-22 10:23:05.000
RealDate1 2006-05-21 21:37:19.000
RealDate2 2006-05-22 10:23:05.000
Count 1.0
With Group by :
Select dbo.OffsetCalc(entered_offset,offset1) as Date1,
dbo.OffsetCalc(entered_offset,offset2) as date2,
dateadd(ss,Entered_offset+offset1,'1980-03-01') as realDate1,
dateadd(ss,Entered_offset+offset2,'1980-03-01')as RealDate2,
Sum(dcount) as test_count
from tbl_dummy1
Group by
dbo.OffsetCalc(entered_offset,offset1) ,
dbo.OffsetCalc(entered_offset,offset2) ,
dateadd(ss,Entered_offset+offset1,'1980-03-01') ,
dateadd(ss,Entered_offset+offset2,'1980-03-01')
Incorrect results:
Date 1: 2006-05-21 21:37:19.000
Date2 : 2006-05-21 21:37:19.000
realdate1: 2006-05-21 21:37:19.000
Realdate2 : 2006-05-22 10:23:05.000
1.0
Any help will be greatly appreciated.
William

Viewing 6 posts - 1 through 5 (of 5 total)

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

ijaz SSCommitted Points: 1912 More actions · Answer 1

Hi,

I you look carefully of your code then you will come to know that there is no problem in the result return by the query.

You are group by on the follwoing column

Date 1, Date2,realdate1,Realdate2

If date2 is a different column and used in the query then how should you say that the data return of the colum is incorrect... always look clearly before sending a bug to be slove.

Hope you will enjoy now and be ready to have a keen look before posting the bugs.

cheers

rutherfordsmith SSC Journeyman Points: 82 More actions · Answer 2

Bottom line :

If you run the query using the "group by" clause it returns different results than if you run it displaying all the results.

Date2 should have a value 2006-05-22 10:23:05.000 as it is calculated off offset2.

Thanks

William

I would not post anything before scrutinizing the code. Do you actually comprehend the problem I am explaining?

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Do it right way:

Select

dbo.OffsetCalc(entered_offset,offset1) as Date1,

dbo.OffsetCalc(entered_offset,offset2) as date2,

dateadd(ss,Entered_offset+offset1,'1980-03-01') as realDate1,

dateadd(ss,Entered_offset+offset2,'1980-03-01')as RealDate2,

Sum(dcount) as test_count

from tbl_dummy1

Group by

entered_offset,offset1,offset2

and don't create problems for yourself.

_____________
Code for TallyGenerator

ijaz SSCommitted Points: 1912 More actions · Answer 4

You there ... I have run the code provided by you and I have get the following results.

Without Group by

2006-03-24 21:37:19.000

2006-03-25 10:23:05.000

2006-03-24 21:37:19.000

2006-03-25 10:23:05.000

1.0

With Group by

2006-03-24 21:37:19.000

2006-03-25 10:23:05.000

1.0

If you change the group by class with this

Group by Entered_offset, offset1,offset2

then we have the same result as we getting without group by

2006-03-24 21:37:19.000

2006-03-25 10:23:05.000

2006-03-24 21:37:19.000

2006-03-25 10:23:05.000

1.0

unfortunately I did not get the date you mentioned 2006-05-22 10:23:05.000

You should change your group by class and I hope this will slove your problem.

Thanks

cheers

rutherfordsmith SSC Journeyman Points: 82 More actions · Answer 5

rutherfordsmith

SSC Journeyman

Points: 82

November 8, 2006 at 3:43 am

#670518

Thanks guys. This really had me stumped.