Calculation based on none work day (sat sun) ?

[MercJones]

Hi there.

Been scatching my head for a bit with this now, and cant find an easy solution, or pretty much any solution..

Im moving from MSSQL - Postgresql. the query in MSSQL was:

SELECT Now()-([DateOrdered])-1.2 AS Expr1 .....

and

WHERE ((Now()-([DateOrdered])-1.2)>6) ...


so the basic idea is work out if dateordered > 6 days, but take away the 2 non-working days weekends.

So i need to do:

WHERE NOW() - dateordered(APART FROM SAT SUN) > 6.. but dont know how to do it.

I can find the day of a certain date using SELECT extract(dow from dateordered); which will give me 1-6 0 being sunday, 1 being monday for example.

Any ideas how I can put all this together though?

I could use php if it cant be done just in a query.

Cheers

 

[stefanwagner]

I understand, you simply* like to get the workingdays from DateOrdered until now?

*simply told, not simply implemented.

Is Monday to Monday 1 workingday or 0 workingdays?

don't visit my homepage:

http://home.arcor.de/hirnstrom/bewerbung

 

[stefanwagner]

I tried to solve it first in a programming language (Java), and I guess that works ok.
Assumption is, bis >= von.
bis:= to
von:= from
werktage:=working days

 // V4
	public static int werktage (int von, int bis)
	{
		if (bis%7 == 6)
		{
			if (von%7 == 0) 
				return ((bis-von)/7)*5+5;
			++bis;
		}
		if (von%7 == 6)
		{
			++von;
		}
		if (bis%7 < von%7)
		{
			bis+=7;
		}
		return ((bis - von)/7)*5 + (bis%7 - von%7);
	}

Translating that into an SQL-function is hard.
I made at least one mistake - but I may show you how it looks like:

 CREATE FUNCTION dow (date) RETURNS int4 AS 'SELECT int4(extract (dow FROM $1))' LANGUAGE sql;

CREATE FUNCTION workdays(date,date) RETURNS int4 AS 'SELECT
CASE
 WHEN ((dow($2)%7 = 6) AND (dow($1)%7 = 0)) THEN ((dow($2)-dow($1))/7)*5+5
 ELSE (((int4(dow($2)%7 = 6)+(dow($2)+7*int4(dow($2)%7 < dow($1)%7))) - (int4(dow($1)%7 = 6)+dow($1)))/7)*5 + ((int4(dow($2)%7 = 6)+dow($2))%7 - (int4(dow($1)%7 = 6)+dow($1))%7)
END ' LANGUAGE sql;

Looks like perl - doesn't it?

don't visit my homepage:

http://home.arcor.de/hirnstrom/bewerbung

 

[stefanwagner]

If you don't like to implement the function with plsql, python or something, maybe I got something for you.

We need the dow-Function from above.
And a new one:

CREATE FUNCTION workdays(date,date) RETURNS int4 AS '
SELECT ((6+($1-$2))/7)*5-o FROM workdayoffset 
WHERE dow($1)=fromd AND dow($2)=tod' 
LANGUAGE sql;

It will read the offsets from a small 7x7 Table (which, in fact, is a 49-row-table).

CREATE TABLE workdayoffset  (fromd int, tod int, o int, id serial);

The mapping has to be like this:

	0,1,2,3,4,5,5,
	4,0,1,2,3,4,4,
	3,4,0,1,2,3,3,
	2,3,4,0,1,2,2,
	1,2,3,4,0,1,1,
	0,1,2,3,4,0,0,
	0,1,2,3,4,5,0

In Row 0 you see the values for 'fromd' (from-day), the first row has idx=0, 0=Sunday.
In Col 0 you see the values for 'tod' (to-day) - you name it.

Row 2, Col 1 has a value 4 for a calculation from Monday to Sunday.
That's the offset (workdayoffset.o) for the function.

don't visit my homepage:

http://home.arcor.de/hirnstrom/bewerbung