How can I select the whole datetime field when I w

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

How can I select the whole datetime field when I w

Author

Topic

EYAAD
Starting Member

3 Posts

Posted - 2008-11-29 : 04:33:42

Hello,
How can I select the whole datetime field when I want to group by the date only

example:
environment (SQL SERVER 2000).
I have a table (fdate,object_name,param1).
I want to perform a select statement against this table to return the maximum of [size=4](param1) of each day per object
(I used group by object_name,CONVERT(VARCHAR(8),[START TIME],103))
but the problem is that I can not put the whole date field in the select lest

(i.e: I can not do the following statement):
select fdate as datewithtime, object_name, max(param1)
group by object_name,CONVERT(VARCHAR(8),[START TIME],103)

any Help is appreciated
Thankyou in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-29 : 04:39:51

Dont use big font

select CONVERT(VARCHAR(8),[START TIME],103) as datewithtime, object_name, max(param1)
group by object_name,CONVERT(VARCHAR(8),[START TIME],103)

Madhivanan

Failing to plan is Planning to fail

EYAAD
Starting Member

3 Posts

Posted - 2008-11-29 : 05:00:21

quote:
Originally posted by madhivanan

Dont use big font

select CONVERT(VARCHAR(8),[START TIME],103) as datewithtime, object_name, max(param1)
group by object_name,CONVERT(VARCHAR(8),[START TIME],103)

Madhivanan

Failing to plan is Planning to fail

thank you Madhivanan but this is not solve the problem.
the function CONVERT(VARCHAR(8),[START TIME],103) returns the date only not date with time.
what I want is to group by date only and return rows whith time.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-29 : 05:21:26

try


select t1.[START TIME],t2.object_name,t2.maxi from your_table as t1 inner join
(
	select dateadd(day,datediff(day,0,t1.[START TIME]),0) as [START TIME], object_name, max(param1) as maxi 
	from your_table
	group by object_name,dateadd(day,datediff(day,0,t1.[START TIME]),0)
) as t2
on dateadd(day,datediff(day,0,t1.[START TIME]),0)=t2.[START TIME]) and
t1.object_name=t2.object_name

Madhivanan

Failing to plan is Planning to fail

EYAAD
Starting Member

3 Posts

Posted - 2008-11-29 : 07:15:00

doesn't work either

the query you replied returns all rows.
But the good news is tha based on the concept of your reply I have tried this:
====================================

SELECT my_table.*
FROM my_table INNER JOIN
(
SELECT CONVERT(VARCHAR(8),[START TIME], 3) AS [START TIME], [OBJECT_NAME],MAX(param1)AS param1
FROM my_table
GROUP BY CONVERT(VARCHAR(8), [START TIME], 3),[OBJECT_NAME]
) AS temp ON CONVERT(VARCHAR(8), my_table.[START TIME], 3) = temp.[START TIME] AND my_table.[OBJECT NAME] = temp.[OBJECT NAME] AND my_table.param1 = temp.param1

====================================
and it works fine but when there are two records with param1 equal to max(param1)
the retrieved rows will be duplicated (CARTESIAN PRODUCT).

note about table :
the date field ([start time]) contains at least 24 record for every object_name for every day (i.e: one rocord per hour for every object_name). I want to extract all detials of every object_name with maximum param1

quote:

Originally posted by madhivanan

try


select t1.[START TIME],t2.object_name,t2.maxi from your_table as t1 inner join
(
	select dateadd(day,datediff(day,0,t1.[START TIME]),0) as [START TIME], object_name, max(param1) as maxi 
	from your_table
	group by object_name,dateadd(day,datediff(day,0,t1.[START TIME]),0)
) as t2
on dateadd(day,datediff(day,0,t1.[START TIME]),0)=t2.[START TIME]) and
t1.object_name=t2.object_name

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources