Re: SQL Inspiration needed

On 7/12/2011 12:49 PM, Jon Paris wrote:

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

Yes, that's one of the ways to do it. Birgitta's CTE works, a UDF works. But basic SQL itself isn't very good at this sort of thing where your business logic is determined by flags in the data.

Heck, another way around this is to create a table with every possible combination of two dates (say, in a year) and include the number of days between them.

I guess the point is there is no really nice way to do this in SQL. Most every technique is using a screwdriver to pound in a nail.

Joe