query all values equal to the nearest payday??

  • Thread starter Thread starter tlyczko
  • Start date Start date
T

tlyczko

Hi, I have a weird query question. :)

I need to pull out a list of rows according to the most recent payday,
and payday is one of the fields the table.

Because I have to include other fields in the query, using MAX doesn't
solve my problem completely, I get multiple rows for the same max
payday (e.g. 3 rows containing 10/6/2006 payday, 3 rows containing
10/20/2006 payday)..

How can I make the query look into another table containing payday
dates and query for the payday closest to today's date??

So that I only get the 3 rows containing 10/20/2006 payday??

Thank you, Tom
 
In the criteria of your Payday field, put
(Select Max(Payday) From TblPaydayDates Where Payday < Date())

Hope that helps!
 
Woudn't that return all the dates?? I will try it though and see what
happens...

I am now looking at datediff type functions because that would enable
me not to use Max, because payday is every Friday and I only need the
most recent payday's data, regardless of the actual payday date...makes
the query more generic and avoids me having to know the actual payday
date...

I am experimenting with datediff < 15 days, datediff < 14 days, and
datediff < 2 weeks, not sure exactly what...

May have to use Julian date calculations...

Finally this query has to be converted/compatible to SQL Server 2005
for an SSIS package.

Thanks, Tom
 
Back
Top