Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Posts
    6

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

    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

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Now() is supported in pg - it is equivalent to the Current_timestamp function. You may want to use current_date instead.

    Also, try changing the 1.2 to interval '1 days 4 hours 48 minutes'


    Now, I BELIEVE the AGE function will do MOST of what you want.
    You could also try

    Code:
    Where Age(dateordered) - interval '1 days 4 hours 48 minutes' > interval '6 days'
    The key thing to remember is that PostgreSQL is, on the whole, more strongly typed than is MSSQL. (It doesn't make many assumptions - it requires you to explicitly perform conversion functions as needed)

    Ref http://www.postgresql.org/docs/8.2/s...-datetime.html
    Last edited by loquin; 11-29-07 at 12:26.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    hehe, I was just looking at that same page

    I am not sure about the ignoring sat/sun part. someone may have a cool way of dealing with that but I would probably script in whatever language.

    Also note that you can play around with interval a lot os loquin noted, and then use extract(field from interval) to get the values out.
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Probably the simplest database approach would be to add a calendar table, containing two fields - a date field, and an integer field. The date field would contain all dates, including weekends and holidays, between the minimum working date and the maximum working date. (+20/-10 years worth, for instance,) indexed by the date. (If you need to hold down the size of the table, limit the date range and periodically flush old days and add new ones)

    The integer field would contain a zero (0) if the date is a weekend or holiday, and a 1 if the date is a working day (weekday, non-holiday)

    To calculate the number of working days between any two two dates from the calendar table, simply sum the integer field between the start and end dates.
    Last edited by loquin; 11-29-07 at 13:00.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Nov 2007
    Posts
    6
    Thanks for the info guys.

    Ive spent a while looking at the stuff today and came up with:

    select (now()::date - dateordered::date) as daysold,orders.dateordered, orders.jobnumber from (staff inner join orders on staff.staffnumber = orders.staffordering) inner join customers on orders.customerid = customers.customerid where extract(dow from dateordered)::int % 6 > 0 AND (dateordered < now() - '5 day'::interval) AND ((orders.requesteddispatchdate < now()) OR (orders.requesteddispatchdate IS NULL)) AND (orders.completed_order = 'f') AND (orders.confirmedorder = 't');

    which works.. but my flaw is that if you have a dateordered over 5 days old, that was ordered on a sat or sun.. it doesnt show up. ARGH.

    so have then tried:

    select (now()::date - dateordered::date) as daysold,orders.dateordered, orders.jobnumber from (staff inner join orders on staff.staffnumber = orders.staffordering) inner join customers on orders.customerid = customers.customerid where Age(dateordered) - interval '1 days 4 hours 48 minutes' > interval '6 days' AND ((orders.requesteddispatchdate < now()) OR (orders.requesteddispatchdate IS NULL)) AND (orders.completed_order = 'f') AND (orders.confirmedorder = 't');

    which runs, but i still cant work out if its missing orders?

    It needs to show all jobs that are overdue, if they are greater than 5 days not including sat/sun (show the job) or if its > say 7 days, show all...

    stuck again lol

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    OK.

    create a table called "workdays" on your database, with date field 'workdate' (pk) and integer field 'workday'

    copy the attached csv file to your server.

    run the script below to import the csv file to your new table. (use your actual pathname where you saved the csv file)

    Code:
    copy tds.workdays (workdate, workday)
    from 'c:\\workdays.txt'
    delimiter ','
    csv header
    (Note the double '\' escaping)

    Now, try the following query
    Code:
    Select Sum (workday) as WorkingDays
    from workdays
    where workdate > '10-17-2007' and workdate <= '10-25-2007'
    Although you could use this as a subquery in a where clause, I would write a function to return the number of working days between Date1 and Date2 - it would result in simpler, more efficient queries.

    Another advantage of this approach is that you only need to update the workday value for a given date in order to set that date as a holiday or other non-work day.

    At my location, we've extended this approach to handle our non-standard work week. I added a column workhours, as we work a 9/80 schedule. In a two week period, we work a total of 9 days, and 80 hours, (Week1: M, T, W, T at 9 hours, F at 8 hours; Week2: M, T, W, T at 9 hours, F off.) This schedule means that we need to search by the number of working hours, rather than days.
    Attached Files Attached Files
    Last edited by loquin; 11-29-07 at 16:45.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Nov 2007
    Posts
    6
    Hi mate, great reply there, still having issues but appreciate the help.

    Ive created that table, and added all the dates etc so if my existing query is:

    select (now()::date - dateordered::date) as daysold,orders.dateordered, orders.jobnumber from (staff inner join orders on staff.staffnumber = orders.staffordering) inner join customers on orders.customerid = customers.customerid where extract(dow from dateordered)::int % 6 > 0 AND (dateordered < now() - '5 day'::interval) AND ((orders.requesteddispatchdate < now()) OR (orders.requesteddispatchdate IS NULL)) AND (orders.completed_order = 'f') AND (orders.confirmedorder = 't');

    i cant see how I can replace

    select (now()::date - dateordered::date) as daysold (i think it would be select sum(workday) as workingdays from workdays where workdate = now() - dateordered) but I think it may need an alias?

    that bit works as it is, but the part that im really confused about is replacing:

    extract(dow from dateordered)::int % 6 > 0 AND (dateordered < now() - '5 day'::interval)


    that works, but not right... i think ive got my logic wrong, but now that workdays table exists it should be tons easier.. but the issue is i need to be able to get results of overdue jobs where if the dateordered > 5 days, not including weekends INCLUDE THAT RESULT, but..(the part im missing *i think*) if its over 5 days old, include it anyway. does that make sense?


    Cheers again for the help, really close now I think

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •