Query table

Question

Query table

sharonsql2013

SSCertifiable

Points: 5908
More actions
December 17, 2014 at 2:58 pm

#293588

How do you query the below table so that you get the data from the latest Surveydate to 30days from then.
Say here from 2014/11/19 through 2014/10/19
Create Table #Temp
(Name Varchar(255)
,Surveydate Datetime )
Insert Into #Temp (Name ,Surveydate) Values ( 'Oscar', '2014/11/19' ),('Kevin','2014/10/19' ),('Rick','2014/8/19'),('Jessie','2014/10/29')

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

sharonsql2013 (12/17/2014)
How do you query the below table so that you get the data from the latest Surveydate to 30days from then.
Say here from 2014/11/19 through 2014/10/19
Create Table #Temp
(Name Varchar(255)
,Surveydate Datetime )
Insert Into #Temp (Name ,Surveydate) Values ( 'Oscar', '2014/11/19' ),('Kevin','2014/10/19' ),('Rick','2014/8/19'),('Jessie','2014/10/29')

Here is one way assuming I understand what you are trying to do.

select t1.*

from #Temp t1

cross join

(

select MAX(SurveyDate) as MaxDate

from #Temp

) t

where Surveydate > DATEADD(day, -30, t.MaxDate)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.