We created a table of working days (excludes sat., sun. and holidays)

The table looks like this:

....

MM/DD/YYYY   RowNumber

08/26/2011   5,620

08/29/2011   5,621

08/30/2011   5,622

...

 

The table has dates from 1990 through 2068.

I want to calculate a date using an offset that comes from a master table, but retrieving only a work day.

example:

08/29/2011 with an offset of 1 should yield 08/26/2011.  I can retrieve the correct value using sub-report by calculating 5622 - 1 = 5621 and linking back to the table.  I prefer NOT to use a sub-report as I have no luck with sharing variables.

I have tried (all unsuccessfully);

SQL Expression

Functions

commands

 

Any ideas?

Thanks for your consideration!

 

Can I pass a report variable to the command?

I created a command,

declare @calcid int
select datevalue
from user_Calendar_By_Workday
where idnumber = @calcid

 

CALCID is a variable that hold the original ROW-ID minus a variable offset.  I do not have the COMMAND joined to anything.  The command returned NULL using the above statements.  If I replace the WHERE with a constant instead of @CALCID, it does return a valid date.

 

FOSTANEK