SQL select maximum from two time periods

Question

I have a query, Im trying to select the maximum value from the summer period (nov-april down here) but it only gives me values from nov-dec with this query. Any ideas why?

SELECT TOP 10 Value, DateTime 
  FROM history
 WHERE Tagname = @Tag
   AND 
       ((DateTime >= @StartYear AND DateTime < @StartWinter)
    OR 
       (DateTime >= @FinishWinter AND DateTime < @FinishYear))
ORDER BY Value DESC

Answer 1

(DateTime >= startYear AND datetime < startwinter) gives you all results between jan and april 2009. (Datetime > finishwinter and datetime < finishyear) gives all results in nov dec 09.

So, you're selecting top 10 from Jan Feb March April Nov Dec 2009. If that's what you want to select from, and you're only getting values in Nov Dec 2009, check to see that there should be values in the other months?

If @startwinter isn't year-sensitive you might also get jan-apr 2010.

Answer 2

Shouldn't you use a 'ORDER BY' when using 'TOP 10'?

And what locale do you live in, or, to rephrase it: what are reasonable dates for (@StartYear, @StartWinter, @FinishWinter, @FinishYear)

In Europe I expect:

• StartYear = 2010-01-01
• FinishYear= 2010-12-31
• StartWinter=2010-12-20 (about)
• FinishWinter=2010-03-20 (about)

So the first period would go from 2010-01-01 to 2010-12-20 (about) and the second from March 2010 to End of year.

So this would include the whole year, and most of it, from 03.20 to 12.20 double.

Answer 3

Hey thanks for the help everyone, it seems this is a problem with our historian (a linked db from sql server) so Ill take the issue up with them. I tried the query on a regular mssql db and it seeems fine...