Solved

Select top 60 where date is a year ago from today

Posted on 2011-10-17
259 Views
I have a table which is holding historical data.  I am working on doing a comparison of recent 30 days versus same 30 days 1 year ago.  There are 2 readings from each day and I do a formula to compute the daily total.

So basically I need to get all records that are 30 days from today from one year ago.
0
Question by:martyuni

LVL 32

Accepted Solution

to get dates from 1 year ago use

select *
from table
where mydate < dateadd(YEAR, -1, getdate())

you can combine to make 1year and 30 days

eg
select *
from table

or if you need between

select *
from table
0

LVL 18

Expert Comment

date here is dob

select * from (select top 30 * from yourtable order by dob desc) x
union
Select * from (select top 30 * from yourtable where dob < dateadd(year,-1,getdate()) order by dob desc)  y
order by dob
0

LVL 2

Expert Comment

declare@ prevdate datetime
declare @prevday datetime

Select  top 30 from  tblnm  where  dtfld =@prevdate

this appeared to work  on some data I had  available to test with

jo
0

Author Comment

So if I do this query:

eg
select TOP 30 *
from mytable
where mydate < dateadd(YEAR, -1, getdate())

That gets me 30 days from a year ago, which is what I want.  The only thing I need to change is that I don't want the data from today's date from one year ago.  Does that make sense?
0

LVL 11

Expert Comment

See if this is what you want:

declare @d date

set @d = '2011-10-17' -- or your desired date in format YYYY-MM-DD

select TOP 30 *
from mytable
where mydate < dateadd(YEAR, -1, @d)
0

LVL 32

Expert Comment

You can start by striping the date from todays date, then subtracting a year

``````declare @datevar datetime

set @datevar = DATEADD(DD, 0, datediff(dd, 0, getdate()))

select TOP 30 *
from mytable
where mydate < dateadd(YEAR, -1, @datevar)
``````

If you want to combine the whole thing in one query

``````select TOP 30 *
from mytable
``````
0

Featured Post

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.