0

Possible Duplicate:
How to group time by hour or by 10 minutes

With MSSQL getting this result:

enter image description here

from following query:

SELECT [DATESTAMP],[ZONE],[ALARM] 
FROM [UNIT].[dbo].[LOG] 
WHERE [DATESTAMP] BETWEEN @StartDate AND @EndDate 
    AND [ALARM] ='ERROR' 
    AND [ZONE] IN ('BOX 11','BOX 12') 
ORDER BY DATESTAMP DESC

How would I alter the query to get only rows 2,5,9,13,17,19,20 ?

Meaning I only want the first(oldest) row from within a group spanning approx 1 min. within that group, whereas there will not be a burst of errors more often than 15mins Regards, Freewheelin

Community
  • 1
  • 1
Freewheelin
  • 43
  • 6

2 Answers2

0

If I understand you, you want the earliest entry for each particular minute, right? If so, try this:

SELECT
    DateStamp,
    ZONE,
    Alarm
FROM
    [UNIT].[dbo].[LOG]  
    JOIN 
    (   SELECT 
            DateMinute = DATEADD(second, -DATEPART(second, DateStamp), DateStamp),
            Earliest = MIN(DateStamp)
        FROM
            [UNIT].[dbo].[LOG]  
        GROUP BY
            DATEADD(second, -DATEPART(second, DateStamp), DateStamp)
    ) X ON [UNIT].[dbo].[LOG].DateStamp = X.Earliest
Dirk Horsten
  • 3,753
  • 4
  • 20
  • 37
PaulStock
  • 11,053
  • 9
  • 49
  • 52
  • Paul, OK, I get errors in 'groups' where the group can be 1 to for 4 rows and i'm interested in the first error occurance since possible errors whithin about a minute are 'following errors' and are not to be recognised again, for overview reasons. Thanks guys, real quick response here! – Freewheelin Dec 14 '12 at 21:34
  • Right, that's what I think the code I supplied does - it shows the earliest error within a minute. If this will not work, could you please explain where it is lacking? If it does work, please click on the checkbox to the left of the answer to mark it acceptable. – PaulStock Dec 14 '12 at 21:42
  • Paul, OK, your query says 'YourTable' meaning the results from my query as a table, or a View -stripping order by stuff, or the table [LOG], before my filtering? – Freewheelin Dec 14 '12 at 22:04
  • Sorry, when I typed in the code I forgot to change my tablename "YourTable" to the name of the table you were using "LOG". I have corrected this in my answer. – PaulStock Dec 14 '12 at 22:10
0

Ended up with:

SELECT  MIN([DATESTAMP]) as FirstOccurance FROM ....
WHERE ....
....
GROUP BY DATEPART(minute, [DATESTAMP])
ORDER BY FirstOccurance DESC

-Thanks again guys

Dirk Horsten
  • 3,753
  • 4
  • 20
  • 37
Freewheelin
  • 43
  • 6