asked on

Help with algorithm

Hi!

I have a small problem.
What I need to achieve is to organize some date data into: Today, Weekdays, One week ago ... One Month Ago .....
similar to what Outlook is organizing the mail.
Now I have built a database-view which among other things calculates days from Today to a sertain timestamp.
But I need help with the rest of the work.
Any suggestions on how to implement this appreciated.

Regards,
Tomas Helgi

Mike Littlewood

Ok so you need extra columns to show weeks, months from a certain date.
Then you can filter the view based on how many weeks, months back you want to see.

Basically you need to use the DATEDIFF function in SQL

DATEDIFF( month, YourDate)
DATEDIFF( week, YourDate)
DATEDIFF( day, YourDate)

If you can show me the SQL for your view, I could amend it to show weeks etc difference unless you can work it out from this yourself

ASKER CERTIFIED SOLUTION

Mike Littlewood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

TheRealLoki

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TheRealLoki

oops slight mistake

find "last week"
StartDate := Trunc(Now-7); //- 7 days from now will be last week...
while DayOfWeek(StartDate) <> 1 do StartDate := StartDate - 1 // keep going back until we find "Monday"
EndDate := StartDate + 6 + EncodeTime(23,59,59,99);

also, if you want to be sure that find "this week" does not include what is shown in the "today" or "yesterday" lists, then do thid
find "this week (mon-sun) excluding yesterday"
Start_ThisWeek := Trunc(Now);
while DayOfWeek(Start_ThisWeek) <> 1 do Start_ThisWeek := Start_ThisWeek - 1 // keep going back until we find "Monday"
End_ThisWeek := Start_ThisWeek + 6 + EncodeTime(23,59,59,99);
while (End_ThisWeek > Start_Yesterday) do End_ThisWeek := End_ThisWeek - 1;
note it is conceivable that you are on Monday or Tuesday, in which case the results will have the "end date" less than the "start date"
so in that situation, you know that no messages can exist in that range, and you do not need to try to show them
eg.
if End_ThisWeek < Start_ThisWeek then // dont bother checking for this week

Mike Littlewood

If you can show me the SQL query you create for your view, I can adjust it to display all the variations you need instead of having to do it in code.