Need filter result set
-
hello!!!
i have a dummy question but i really need the answer
I have several samples for a hour in my table:
DateAndTime Value
01-10-2005 1:52:58 75
01-10-2005 1:56:54 74
02-10-2005 0:03:16 322
02-10-2005 0:10:45 323
02-10-2005 0:15:00 305
02-10-2005 0:18:56 84
02-10-2005 0:23:11 302
02-10-2005 0:27:01 308
I want a resul set with only one sample per hour, for example:
DateAndTime Value
01-10-2005 2:22:18 50
01-10-2005 1:52:58 75
01-10-2005 0:10:45 323
30-09-2005 23:17:08 120
How can i do this??
Thanks,
Cláudia
Regards,Cláudia Rego
www.footballbesttips.com
-
I had to do something similar to this before and accomplished it like this:
SELECT SUBSTRING(CONVERT(VARCHAR(25),DAT,121),1,14)+'00' AS DAT,VALUE
INTO ##TEMP
FROM TEST001
SELECT DAT, SUM(VALUE)
FROM ##TEMP
GROUP BY DAT
Not the best method, but it was only for my reporting needs and it did the trick.
-
>>I want a resul set with only one sample per hour, for example:
These are incomplete requirements that no-one can answer. If there is more than 1 value per hour, which one do you want to keep ? The earliest in the hour ? The latest, the max, the min, the average, a random arbitrary value ?
You added a sample of what you want, but the data values in it don't relate to the sample of raw data.
-
Sorry, you're right.
I have more than 1 value per hour, and I need a random arbitrary value or the earliest.
How i do this??
Thanks
Cláudia
Regards,Cláudia Rego
www.footballbesttips.com
-
hey,
consider formatting the hour value as distinct, and then gruop by that field and sum, avarage or top 1 as the second field
-
>>I have more than 1 value per hour, and I need a random arbitrary value or the earliest.
You need a derived table which returns you the earliest timestamp per hour. Then you join this back to the original table for the required resultset. The derived table groups by the calendar date, and the hour within that date, returning you the earliest hour timestamp in each hour. As long as the timestamps in your main table are unique, this should work.
Select YourTable.*
From YourTable
Inner Join
(
Select Min(DateAndTime) As EarliestPerHour
From YourTable
-- Group by ISO date (YYYYMMDD) and Hour
Group By convert(varchar, DateAndTime, 112), DatePart(hh, DateAndTime)
) dt
On (dt.EarliestPerHour = YourTable.DateAndTime)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply