select problem with dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

select problem with dates

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2012-12-27 : 14:41:38

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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-27 : 14:53:03

SELECT TOP 4 *
FROM
(select * from yourTable where yourDate <= '20120119'
)
Order By yourDate DESC

Jim

Everyday I learn something that somebody else already knew

smh
Yak Posting Veteran

94 Posts

Posted - 2012-12-27 : 15:26:45

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)?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-27 : 15:38:01

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

smh
Yak Posting Veteran

94 Posts

Posted - 2012-12-27 : 16:00:10

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-27 : 17:26:42

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

smh
Yak Posting Veteran

94 Posts

Posted - 2012-12-27 : 18:04:51

Yes, this works and thanks very much. I did not realize this could be done with "select top num"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources