SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select problem with dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

smh
Yak Posting Veteran

USA
91 Posts

Posted - 12/27/2012 :  14:41:38  Show Profile  Reply with Quote
a row of a table is selected from a grid based upon a date.

I need a query to select that row and the rows with the 4 dates prior to the selected date. For example the date selected is 1/19/12, the 4 prior dates could be 11/1/11, 11/12/11, 12/3/11, 12/29/11.

I get the id and the date of the selected row from the grid so returning that record is no problem. But I cannot depend upon the identity ID's not having gaps plus the ordering of the table to get the proper dates would preclude using the ID's anyway.

In a way this is like returning the previous 'n' rows before and including row 'r' but in this case it is based upon the date not the id.

Any ideas greatly appreciated.

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/27/2012 :  14:53:03  Show Profile  Reply with Quote
SELECT TOP 4 *
FROM
(select * from yourTable where yourDate <= '20120119'
)
Order By yourDate DESC

Jim


Everyday I learn something that somebody else already knew
Go to Top of Page

smh
Yak Posting Veteran

USA
91 Posts

Posted - 12/27/2012 :  15:26:45  Show Profile  Reply with Quote
Hi:

Thanks, I did not realize one could use top n based upon a selection. I did receive an error with your version so I tried:

select top 4 * from yourtable where yourdate <= '20120119'
order by yourdate desc

This seemed to work ok but maybe only in the sample I used.

------

Here is another question on this question. How can I return only the first ID of the list (the ID of the record with the furthest date from the selected one)?

Edited by - smh on 12/27/2012 15:30:18
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/27/2012 :  15:38:01  Show Profile  Reply with Quote
I think you can just change it to be TOP 1, and instead of ORDER BY yourDate desc just do
ORDER BY yourDate ASC

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

smh
Yak Posting Veteran

USA
91 Posts

Posted - 12/27/2012 :  16:00:10  Show Profile  Reply with Quote
Hi:

Its really like:

select top 1 * from (select top 4 * from tblpayrolldatedetail where payrollDate <= '1/16/2012'
order by payrollDate desc) order by payrolldate

in order to get the 11/1/11 date but this does not work as I get an error.

I could put the 4 records from the initial query into a #temp table and then select the top 1, but would be nice to do it in one query.

Thanks

to get the
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/27/2012 :  17:26:42  Show Profile  Reply with Quote
Nope, what you did is the right logic, just a syntax error. You have to name the set you created with this statement
select top 4 * from tblpayrolldatedetail where payrollDate <= '1/16/2012'
order by payrollDate desc


select top 1 *
from (select top 4 * from tblpayrolldatedetail where payrollDate <= '1/16/2012'
order by payrollDate desc) as NewSetIjustCreated-- you could just name it "a" or anything else you wanted
order by payrolldate


Jim

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

smh
Yak Posting Veteran

USA
91 Posts

Posted - 12/27/2012 :  18:04:51  Show Profile  Reply with Quote
Yes, this works and thanks very much. I did not realize this could be done with "select top num"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000