[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

[loquin]

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

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

[amthomas]

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.

[loquin]

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.

[MercJones]

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

[loquin]

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)

copy tds.workdays (workdate, workday)
from 'c:\\workdays.txt'
delimiter ','
csv header

(Note the double '\' escaping)

Now, try the following query

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.

[MercJones]

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