Group by date, avg(Value) not working

Question

Post reply

Group by date, avg(Value) not working

GSQL

SSC Enthusiast

Points: 154
More actions
March 11, 2009 at 10:19 pm

#384111

I'm trying to help someone that needs a view that averages an integer value grouping by day. There are several rows per day with different datetime values (up to the millisecond) and I want to convert to a varchar(10) to roll them up.
I tested code against data that this person exported from their server with a query that I gave them, so I know I'm visualizing the data correctly, and the query works on my machine, but when they run the query I give them on their server they get only one row in return - the row that provides the avg(value) for the most recent date (ie: today), where it should be a range of dates.
Here's what I'm doing:
create view MeasuresByDay
as
select Machine, convert(datetime, convert(nvarchar, [DateTime], 101)) MeasureDate, avg(Value) AvgValue
FROM AnalogHistory
WHERE wRowCount = 0 AND wVersion = 'Latest' AND wRetrievalMode = 'Delta'
group by Machine, convert(datetime, convert(nvarchar, [DateTime], 101))
-- Yes, they used [DateTime] as a column name
select *
from MeasuresByDay
where MeasureDate between '20090101' and '20091231'
order by tagname, measuredate
This query produces just 1 row for the user.
To verify the underlying data, I had the user run the above query without aggregating (no group by) and it produces a number of rows on different dates in the past and up to the present, so the rows are there and the user seems to have access to them with his database login.
I've had this user test multiple variations of this query and I'm sure they're getting a little frustrated of trying things that don't work.
Anybody have a clue what could be going on?

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

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

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 1

[font="Verdana"]Rather than convert to varchar and back again, can you try the following?

dateadd(dd, 0, datediff(dd, 0 [DateTime]))

I'm not sure whether that will resolve the problem, but it's a better way of getting just the date part of a date/time field.

Also, I suspect if you made your:

convert(datetime, convert(nvarchar, [DateTime], 101))

to read:

convert(datetime, convert(nvarchar, [DateTime], 101), 101)

... you would get better results. Basically, the outer convert is relying on whatever the system (or user) date format is. So that could vary, and could explain why you're not getting the data you are expecting.

But go with the dateadd() datediff() method. It's faster and doesn't rely on you remembering to specify the date format to work reliably. 🙂

[/font]

GSQL SSC Enthusiast Points: 154 More actions · Answer 2

Thanks for the reply Bruce.

After I posted the question I discovered and researched DateFormat and I found this, which explained the suggestion you gave

http://www.karaszi.com/SQLServer/info_datetime.asp

This page that proves the efficacy of the method you advocate (see: Getting rid of the time portion):

http://www.karaszi.com/SQLServer/info_datetime_rid_sec_perf.asp

This was new to me and will be using this method to strip the time from now on. Since I was convinced, I had the user retry the query

alter view MeasuresByDay

as

select Machine, DATEADD(DAY, 0, DATEDIFF(DAY, '', [DateTime])) MeasureDate, avg(Value) AvgValue, count(*) TotalMeasures

FROM Server.Db.dbo.AnalogHistory

WHERE wRowCount = 0 AND wVersion = 'Latest' AND wRetrievalMode = 'Delta'

group by Machine, DATEADD(DAY, 0, DATEDIFF(DAY, '', [DateTime]))

and then

select top 25 * from MeasuresByDay

But he still gets the same result: 1 row returned.

To check on the DateTime format for his server, which is associated with the default language, I also had him run:

SELECT @@language, @@langid

and he gets

us_english, 0

for which the default DateFormat is MDY. I don't know how he would check if his login DateFormat is different than MDY, but I suspect that it's also MDY

The issue seems to be environmental, but I'm not sure of what else to check. What else could I be missing? Do I need to track down DateFormat some more?

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 3

[font="Verdana"]No, I think that's eliminated it as being a date format issue. Have you had a look at the query plans both for the query and for running the view? That is likely to tell you where they differ.[/font]

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 5

Concur with Gail. Post the table structure with sample data. I am sure you will get good replies.

karthik

GSQL SSC Enthusiast Points: 154 More actions · Answer 6

Thanks, all. I've been asking for the table structure from the user and have not received it yet. Please stay tuned.