×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here

Calculation based on none work day (sat sun) ?

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:

CODE

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close