September 15, 2005 at 7:33 am
I need to compare a record with a date to the last two dates (two previous dates) in a Dates Table. I want to use my Dates Table because it takes Holidays and Weekends into consideration. Below, I am using the DATEADD Function with a hardcoded date. How can I replace the DATEADD Function with the last two dates in the Date Table. Do I need to read (Select) the Dates Table twice and store the two dates into two local variables? I know I can get the last date by using the Max(Date) Function. How would I get the next to last date from the Dates Table? (Would the Top Function work?)
DECLARE @BusinessDate AS DateTime
SET @BusinessDate = '07/29/05'
SELECT
*
FROM Table A
--Note I need to pull data from the last 2 previous business days
WHERE A.AsOfDate > DATEADD (dd, -1, @BusinessDate)
Thanks in advance, Kevin.
September 15, 2005 at 7:51 am
Something like this??
Select top 2 Dates from dbo.Calendar where Dates < @BusinessDate order by Dates Desc
September 15, 2005 at 8:06 am
"How would I get the next to last date from the Dates Table? (Would the Top Function work?) "
select max(date) from dates where date < (select max(d1.date) from dates d1)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 15, 2005 at 10:27 am
select max(Latest2.[Date]) as MostRecent
,min(Latest2.[Date]) as PriorDate
from (select top 2 [Date] from Dates
where [Date] <= @BusinessDate
order by [Date] DESC
) Latest2
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply