Filtering data - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Filtering data

Author

Topic

Rliss
Starting Member

31 Posts

Posted - 2006-08-11 : 12:32:16

site date-time pct
------------- ------------------------ -----------
omahmwi1 2006-08-03 22:00:00.000 37.80000000
omahmwi1 2006-08-04 06:00:00.000 37.80000000
omahmwi1 2006-08-05 02:30:00.000 37.80000000
omahmwi1 2006-08-06 09:30:00.000 37.78571400
omahmwi1 2006-08-07 00:00:00.000 37.78571400
omahmwi1 2006-08-08 04:00:00.000 37.81428500
omahmwi1 2006-08-08 06:30:00.000 37.81428500
omahmwi1 2006-08-09 15:30:00.000 37.81428500
omahmwi1 2006-08-10 05:45:00.000 37.82857100
omahmwi1 2006-08-11 08:45:00.000 37.74285700

This sample of data is collected in a view displaying the max pct. You will notice that on 8/8, there were 2 time periods with the exact same pct, so it pulled two records for the same day. I need to filter this to get only 1 for each date when I select from the view. Select distinct or select max() is not helping...

Thanks,

RLiss

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-11 : 13:01:02

Select distinct or Max() will help if u select the Date Part only.

Srinika

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-08-11 : 13:08:48

Its a little unclear what youre getting after... ill assume (Site, date-time) is unique, and you want to group by each (day, pct).

Try this:



declare @table table (site varchar(25), dt datetime, pct numeric(12,8))
insert into @table (site, dt, pct)
    select  'omahmwi1', '2006-08-03 22:00:00.000', '37.80000000' union all
    select  'omahmwi1', '2006-08-04 06:00:00.000', '37.80000000' union all
    select  'omahmwi1', '2006-08-05 02:30:00.000', '37.80000000' union all
    select  'omahmwi1', '2006-08-06 09:30:00.000', '37.78571400' union all
    select  'omahmwi1', '2006-08-07 00:00:00.000', '37.78571400' union all
    select  'omahmwi1', '2006-08-08 04:00:00.000', '37.81428500' union all
    select  'omahmwi1', '2006-08-08 06:30:00.000', '37.81428500' union all
    select  'omahmwi1', '2006-08-09 15:30:00.000', '37.81428500' union all
    select  'omahmwi1', '2006-08-10 05:45:00.000', '37.82857100' union all
    select  'omahmwi1', '2006-08-11 08:45:00.000', '37.74285700'

select  t.site, max(t.dt), t.pct
from    @table t
group by t.site, t.pct, convert(varchar, t.dt, 101)
order by 2

Nathan Skerl

Rliss
Starting Member

31 Posts

Posted - 2006-08-11 : 13:11:13

I need to select the date and time as one field as to report the time of day where that pct was acheived.

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-08-11 : 13:14:35

quote:
I need to select the date and time as one field

Which of these two rows do you want to return?

'omahmwi1', '2006-08-08 04:00:00.000', '37.81428500'
'omahmwi1', '2006-08-08 06:30:00.000', '37.81428500'

The code I posted will select the latest entry using max(dt). This date and time is returned in 1 column. Not sure what youre asking.

Nathan Skerl

Rliss
Starting Member

31 Posts

Posted - 2006-08-11 : 13:32:34

It doesn't really matter, I just thought that using max(date-time) would be the only way to differentiate them...I just need only one for the day...

RLiss

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-08-16 : 11:39:58

So, does the script I posted work for you?

What do you mean by "select the date and time as one field?"

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources