Uncompleted tasks with number of working days OS

[TheToeOfPooh]

Tracking outstanding projects by Manager

I would like to know how\if I can use the following SQL statement -
provided by andrew93 in another thread

SELECT T_Dates.start, Weekday(Now()) AS Today, Int(Now()-[start]) AS Elapsed, ([Elapsed] Mod 7) AS XSDays, IIf([XSDays]=0,0,IIf([Today]=1,[XSDays]-1,IIf([XSDays]+1=[Today],[XSDays]-1,IIf([XSDays]<[Today]-1,[XSDays],[XSDays]-2)))) AS XSWDays, Int((Now()-[start])/7) AS Weeks, ([Weeks]*5)+[XSWDays] AS WorkDays
FROM T_Dates;

Using the above example I have created a table T_Dates and included amongst others the following fields.

Name (Text)
Task (Text)
Completed (Yes/No)
Deadline (Short Format of Date time e.g. 15/03/05)

Using the field called Deadline calculate number of work days between then and today where Completed = No

Through lack of knowledge I am unable to ammend the statement.
Any sugestions please?

Bernard

 

[Norie]

Bernard

I'm not sure about counting work days but the following will tell you how many actual days there are left to the deadline.

SELECT Tasks.Name, Tasks.Task, Tasks.Completed, DateDiff("d",Date(),[Deadline]) AS DaysLeft
FROM Tasks
WHERE (((Tasks.Completed)=No));

 

[Andrew Fergus]

Hi Bernard
That SQL statement was designed to calculate the number of working days between today and a date in the past. I'm not sure how it will work if the date is in the future. If you substitute my table name 'T_Dates' for your actual table name and substitute my field 'start' with your field named 'Deadline', does it give the right result? All of the other variables / fields are calculated within the query. Unfortunately I suspect it won't give the correct result given it was designed to look backwards, but it might be worth a try. That query took me a really long time to work out and I don't fancy trying to re-create it for a future date if I don't have to - I'm not saying I won't work it out, just give me enough time and my curiosity will get the better of me
Andrew

 

[Andrew Fergus]

Hi Bernard

Here you go :

SELECT T_Dates.Deadline, Weekday(Date()) AS Today, Int([Deadline]-Date()) AS Elapsed, ([Elapsed] Mod 7) AS XSDays, IIf([XSDays]=0,0, IIf([Today]=7, [XSDays]-1, IIf([XSDays]=7-[Today], [XSDays]-1, IIf([XSDays]<7-[Today], [XSDays], [XSDays]-2)))) AS XSWDays, Int(([Deadline]-Date())/7) AS Weeks, ([Weeks]*5)+[XSWDays] AS WorkDays
FROM T_Dates;

This SQL will calculate the working days between today and a future date - it does not take account of holidays. It won't work for dates that have passed - see the other logic for that.

Some of the columns are not required, but have been included for simplicity purposes. The Weekday function converts the day of the week into a number and if you map the weekday numbers against the surplus days (i.e. the number of days left over after subtracting the weeks, if any) then you will see how the logic for the excess work days ('XSWDays' field) was worked out.

HTH, Andrew.

 

[TheToeOfPooh]

My apologies guys

It appears I have been unclear as to what I am trying to achieve.

andrew's statement works fine.
I am looking for some guidance as to how I add
fldName
fldTask
fldCompleted (where entry = No)
Using the fldDeadline (short date) to work-out how many days a project has overrun.

I was thinking that by using the Completed field I could identify:

Projects completed and finished on or ahead of deadline = Yes
Projects underway and deadline date not yet exceeded = Blank
Projects which have over run the agreed deadline = No

Does this clarify my objectives?

As I say - my apologies for causing unneccessary work. SQL is a new area to me so please excuse my bumbling.

Bernard

 

[TheToeOfPooh]

I've Tried the following (result was much laughter from my CPU) with no luck.

SELECT T_Dates.start, Weekday(Now()) AS Today, Int(Now()-[start]) AS Elapsed, ([Elapsed] Mod 7) AS XSDays, IIf([XSDays]=0,0,IIf([Today]=1,[XSDays]-1,IIf([XSDays]+1=[Today],[XSDays]-1,IIf([XSDays]<[Today]-1,[XSDays],[XSDays]-2)))) AS XSWDays, Int((Now()-[start])/7) AS Weeks, ([Weeks]*5)+[XSWDays] AS WorkDays
FROM T_Dates
WHERE(((T_Dates.Completed)="No"))
GROUP BY T_Dates.Name;

Any sugestions please?

Bernard

 

[Norie]

Bernard

Was the query I posted any use at all?

 

[Andrew Fergus]

Hi Bernard
Try this :

SELECT T_Dates.Name, T_Dates.start, Int(Date()-[start]) AS Elapsed, (Int(Date()-[start]) Mod 7) AS XSDays, (5*Int((Date()-[start])/7)) + IIf([XSDays]=0,0, IIf(Weekday(Date())=1, [XSDays]-1, IIf([XSDays]+1=Weekday(Date()), [XSDays]-1, IIf([XSDays]<Weekday(Date())-1, [XSDays],[XSDays]-2)))) AS WDays
FROM T_Dates
WHERE (((T_Dates.Completed)="No"))
GROUP BY T_Dates.Name, T_Dates.start;

HTH, Andrew.

 

[TheToeOfPooh]

Norie,

Sorry for not replying quicker.

I used the following based upon your sugestion:SELECT T_Dates.Name, Tasks.Task AS Expr1, Tasks.Completed AS Expr2, DateDiff("d",Date(),[Deadline]) AS DaysLeft
FROM T_Dates
WHERE ((([T_Dates].[Completed])=No));

The result was just a name in the manager field.

There were then Exp1 then Exp2 and days Left as column headings.

Any thoughts?

Bernard

 

[TheToeOfPooh]

Andrew

Your sugestion is giving me enough data to use:

SELECT [T_Dates].[start], Weekday(Now()) AS Today, Int(Now()-[start]) AS Elapsed, ([Elapsed] Mod 7) AS XSDays, IIf([XSDays]=0,0,IIf([Today]=1,[XSDays]-1,IIf([XSDays]+1=[Today],[XSDays]-1,IIf([XSDays]<[Today]-1,[XSDays],[XSDays]-2)))) AS XSWDays, Int((Now()-[start])/7) AS Weeks, ([Weeks]*5)+[XSWDays] AS WorkDays
FROM T_Dates
WHERE ((([T_Dates].[Completed])=No));




How do I include the following -

a) Group by the Name field

b) Include fields Task and Notes

Sorry to be so dense folks.

Bernard