Clever Geek Handbook

Select X Most recent non-sequence weeks

Anyone got an idea on how to choose the number of bad days per day? Dates are the standard date and time. So, for example, I would like to select the 5 most recent data of the day, but there can be many days between the records, so there will not be simply select records from 5 days ago and later.

0


source to share


3 answers


Following Tony Andrews' approach , here's a way to do it in T-SQL:



SELECT
  Value,
  ValueDate
FROM
  Data
WHERE
  ValueDate >= 
  (
    SELECT 
      CONVERT(DATETIME, MIN(TruncatedDate))
    FROM 
      (
         SELECT DISTINCT TOP 5 
           CONVERT(VARCHAR, ValueDate, 102) TruncatedDate
         FROM 
           Event
         ORDER BY 
           TruncatedDate DESC
      ) d
  )
ORDER BY
  ValueDate DESC
+1


source


I don't know the syntax for SQL Server, but you need:

1) Select dates (with shortened time component) in descending order

2) Select top 5

3) Get 5th value



4) Select data where datetime> = 5th value

Something like this "pseudo-SQL":

select *
from data
where datetime >=
( select top 1 date
  from
  ( select top 5 date from
    ( select truncated(datetime) as date
      from data
      order by truncated(datetime) desc
    )
    order by date
  )
)
0


source


It should do it and be good enough in terms of performance. You haven't specified how to handle relationships, so you can add a WITH TIES clause if you need to.

SELECT TOP (@number_to_return)
     *   -- Write out your columns here
FROM
     dbo.MyTable
ORDER BY
     MyDateColumn DESC
0


source






More articles:


All Articles