×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Calculation based on none work day (sat sun) ?

## Calculation based on none work day (sat sun) ?

(OP)
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

### RE: Calculation based on none work day (sat sun) ?

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

### RE: Calculation based on none work day (sat sun) ?

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

#### CODE

// 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 ### RE: Calculation based on none work day (sat sun) ? 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: #### CODE 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).

#### CODE

CREATE TABLE workdayoffset  (fromd int, tod int, o int, id serial);
The mapping has to be like this:

#### CODE

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!