UDF''s in aggregate selects....
-
Hello,
I just found something curious. I wrote simple sql to count applications by day and hour. I used my own UDF to get data easily formatted. What happens is that when I write aggregate sql the second column (group level) data is replaced with that of the first one. Why? (Sql2000)
select dbo.DateToStr(DateCol, 'dd.mm.yyyy', 1) DDMMYYYY, dbo.DateToStr(DateCol, 'hh', 1) HH
from webhak_applicant
29.08.2007 09
29.08.2007 09
29.08.2007 09
.... OK (get the rows - no groupping)
select dbo.DateToStr(DateCol, 'dd.mm.yyyy', 1) DDMMYYYY, dbo.DateToStr(DateCol, 'hh', 1) HH, count(*) kpl
from webhak_applicant
group by dbo.DateToStr(DateCol, 'dd.mm.yyyy', 1), dbo.DateToStr(DateCol, 'hh', 1)
order by 1,2
28.08.2007 28.08.2007 9
28.08.2007 28.08.2007 39
28.08.2007 28.08.2007 26
28.08.2007 28.08.2007 22
.... i.e. HH column shows same values as DDMMYYYY (the first column)???
select dbo.DateToStr(DateCol, 'hh', 1) HH, dbo.DateToStr(DateCol, 'dd.mm.yyyy', 1) DDMMYYYY, count(*) kpl
from webhak_applicant
group by dbo.DateToStr(DateCol, 'hh', 1), dbo.DateToStr(DateCol, 'dd.mm.yyyy', 1)
order by 1, 2
00 00 5
01 01 4
02 02 2
03 03 2
.... i.e. DDMMYYYY column shows same values as HH (the first column)??????
Regards, Ville
-
Hi Ville,
I am having trouble getting the same results as you!
Please coul dyou let me knwo the code behind [dbo].[DateToStr]?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
No idea why the above code does not work, since I don't have access to the functions.
However, this is MUCH more efficient way to do the same thing!
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, DateCol), 0) AS YYYYMMDDHH,
COUNT(*)
FROM WebHak_Applicant
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, DateCol), 0)
ORDER BY 1
And then do the formatting in the front-end application!
N 56°04'39.16"
E 12°55'05.25" -
Hi,
yes, my solution is not fast since UDF's are not fast i've noticed. That is not the point here though, I just used my UDF here to quickly scan the data. But I'd like to know why it acts like it does in the aggregations... It works fine in normal row selects like you saw in my example data
CREATE FUNCTION DateToStr (@DateTime datetime, @DatePicture varchar(200) = 'DD.MM.YYYY HH:MI:SS', @Etunollat bit = 1)
RETURNS varchar(300) AS
BEGIN
declare @Returnvalue varchar(300)
declare @yyyy varchar(4), @yy varchar(2),@qq varchar(2), @mm varchar(2), @dd varchar(2)
declare @hh varchar(2), @mi varchar(2), @ss varchar(2), @ms varchar(3), @wk varchar(2), @dy varchar(3)
Set @yyyy = Cast(DatePart(yyyy, @DateTime) as varchar(4))
Set @yy = Right(Cast(DatePart(yyyy, @DateTime) as varchar(4)), 2)
Set @qq = right(case @Etunollat when 1 then '0' else '' end + Cast(DatePart(qq, @DateTime) as varchar(2)), 2)
Set @mm = right(case @Etunollat when 1 then '0' else '' end + Cast(DatePart(mm, @DateTime) as varchar(2)), 2)
Set @dd = right(case @Etunollat when 1 then '0' else '' end + Cast(DatePart(dd, @DateTime) as varchar(2)), 2)
Set @hh = right(case @Etunollat when 1 then '0' else '' end + Cast(DatePart(hh, @DateTime) as varchar(2)), 2)
Set @mi = right(case @Etunollat when 1 then '0' else '' end + Cast(DatePart(mi, @DateTime) as varchar(2)), 2)
Set @ss = right(case @Etunollat when 1 then '0' else '' end + Cast(DatePart(ss, @DateTime) as varchar(2)), 2)
Set @ms = right(case @Etunollat when 1 then '00' else '' end + Cast(DatePart(ms, @DateTime) as varchar(3)), 3)
Set @wk = right(case @Etunollat when 1 then '0' else '' end + Cast(DatePart(wk, @DateTime) as varchar(2)), 2)
Set @dy = right(case @Etunollat when 1 then '0' else '' end + Cast(DatePart(dy, @DateTime) as varchar(3)), 3)
set @Returnvalue = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@DatePicture, 'yyyy', @yyyy),
'yy', @yy),'qq', @qq), 'mm', @mm), 'dd', @dd), 'hh', @hh), 'mi', @mi), 'ss', @ss), 'ms', @ms), 'wk', @wk), 'dy', @dy)
return @Returnvalue
END
-
Maybe you are experiencing the bug that was described here?
-
Hi,
yes, that was the case. I'm sorry, I didn't look for similar cases in the kbase.
I added 1 sec and 2 secs to the UDF parameters when calling it and... it works...
select dbo.DateToStr(Dateadd(ss, 2, paivitys_aikaleima), 'dd.mm.yyyy', 1) Paiva, dbo.DateToStr(Dateadd(ss, 1, paivitys_aikaleima), 'hh', 1) tunti, count(*) kpl
from webhak_applicant
group by dbo.DateToStr(Dateadd(ss, 2, paivitys_aikaleima), 'dd.mm.yyyy', 1), dbo.DateToStr(Dateadd(ss, 1, paivitys_aikaleima), 'hh', 1)
order by 1,2
Interesting
Maybe I should install sp4 then...
Thanks, Ville
-
If you compare the speed with your code above, and this
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, DateCol), 0) AS YYYYMMDDHH,
COUNT(*)
FROM WebHak_Applicant
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, DateCol), 0)
ORDER BY 1
which is faster and by how many times?
N 56°04'39.16"
E 12°55'05.25" -
Yes you are right (you still need to format it) but like I said earlier that is not the point here... but the bug in sql2000 (sp3) that ruined the outcome of the sql. The code above is just an example of the workaround that was described in MS pages.
I just write simplifying UDF's every now and then to be used by report designers who might not be so skilled with pure sql... i.e. with a udf like my datetostr you can format/embed the date/time in sentences in what ever fashion you like and easily.
Regards, V
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply