Re: SQL Inspiration needed

On Jul 12, 2011, at 1:26 PM, midrange-l-request@xxxxxxxxxxxx wrote:

The only way to do this simply is to create a different table, populated
with dates and their business day numbers. Then you find the day number
for the source date, add the number of days, and fetch the date for the
resulting day number.

I agree Joe - that would be my approach but I'm just gathering a variety of options here. As you say the benefit is that it works in RPG too. I would use a business day version of a Julian day number (i.e. since the beginning of the table) approach rather than business day in a year to avoid issues with year-end roll-over but same basic idea.

I do have an SQL "solution" that "works" but it is really slow. It uses non-weekend dates rather than business days as such but ... This is the code:

SELECT DC_Date From Date_Conv D1
WHERE D1.dc_Weekend = 'N' and
(Select Count(*) from Date_Conv D2
Where D2.dc_Date >= Current Date
And D2.dc_Date <= D1.dc_Date
And D2.dc_Weekend = 'N') = :n


Jon Paris

www.partner400.com
www.SystemiDeveloper.com