Efficient SQL - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Efficient SQL

Author

Topic

mukhan85
Starting Member

46 Posts

Posted - 2009-07-10 : 16:03:50

Hi, I need to get name, average bonus, maximum and minimum bonus of employees. For one day, one week , and one month.

the table schema:

create table person(name varchar(10), data_1 int, modiry_time date, primary key);

using Java & Spring to retrive data and display on the web page.

I am using the following query:
select name as 'name',
convert(decimal(10,2), AVG(data_1)) as 'avg_duration',
Min(data_1) as 'minimum',
Max(data_1) as 'maximum',
count(*) as 'sample',
1 as 'period'
from person
where country = 'USA' and
convert(char(10), modify_time, 101) = '06/21/2009'
group by name
union all
select name as 'name',
convert(decimal(10,2), AVG(data_1)) as 'avg_duration',
Min(data_1) as 'minimum',
Max(data_1) as 'maximum',
count(*) as 'sample',
7 as 'period'
from person
where country = 'USA' and
convert(char(10), modify_time, 101) > '06/13/2009' and
convert(char(10), modify_time, 101) < '06/22/2009'
group by name
union all
select name as 'name',
convert(decimal(10,2), AVG(data_1)) as 'avg_duration',
Min(data_1) as 'minimum',
Max(data_1) as 'maximum',
count(*) as 'sample',
30 as 'period'
from person
where country = 'USA' and
convert(char(10), modify_time, 101) > '05/21/2009' and
convert(char(10), modify_time, 101) < '06/23/2009'
group by name

Is there a better way to query that info?

Thanks.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-07-11 : 03:20:11

I am not even sure why this works the way you are comparing dates as strings. Also, my suggestions assume you have indexes and your create table is just for reference.

As a rule you should reverse your convert statements to convert the constant not the column. As you have them prevents indexes being used because of the expression on the column.

where country = 'USA' and
modify_
convert(char(10), modify_time, 101) > '05/21/2009'

should be

where country = 'USA' and
modify_time > convert(date, '05/21/2009' )

Also use correctly typed parameters for the dates unless they truly are the constants you have provided.

Finally using a case statement to determine your period column and grouping by that might give you fewer goes through the table. Careful though - it but might end up doing more I/O depending on selectivity of the dates.

Something like:

select name,min(data)1),....,period from
(
select
...
case when modify_time > blah then 1
case when .... then 30
as period
from person
)x
group by name, period

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-11 : 03:48:45

[code]
DECLARE @period TABLE
(
period int,
start_date datetime,
end_date datetime
)

INSERT INTO @period
SELECT 1, '2009-06-21', '2009-06-21' UNION ALL
SELECT 7, '2009-06-13', '2009-06-22' UNION ALL
SELECT 30, '2009-05-21', '2009-06-22'

SELECT name AS 'name',
CONVERT(decimal(10,2), AVG(data_1)) AS 'avg_duration',
MIN(data_1) AS 'minimum',
MAX(data_1) AS 'maximum',
COUNT(*) AS 'sample',
d.period AS 'period'
FROM person p
INNER JOIN @period d ON p.modify_time >= d.start_date
AND p.modify_time < DATEADD(DAY, 1, d.end_date)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-11 : 03:50:48

don't convert your modify_time to string and compare. It will give bad performance.

also since your data_1 is an integer column, you might want to convert to decimal first before average it


CONVERT(decimal(10,2), AVG(convert(decimal(10,2) data_1))) AS 'avg_duration',

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources