Solved

Help with algorithm

Posted on 2006-11-10
5
185 Views
Last Modified: 2010-04-05
      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
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 17920815
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
0
 
LVL 15

Accepted Solution

by:
mikelittlewood earned 400 total points
ID: 17921576
Sorry forgot that DateDiff needs the 2 dates, which is what you need anyway

DATEDIFF( month, StartDate, EndDate)

etc
0
 
LVL 17

Assisted Solution

by:TheRealLoki
TheRealLoki earned 100 total points
ID: 17923918
take a look at the DayOfWeek() function. It returns a number to let you know if a given day is a monday, or tuesday, etc.

I think its Sunday (0) to Saturday (6)
If you are using DB aware components, then the easiest way would be to make several queries to get all the ranges you want
Work out the Start and End date ranges (see below) and put those in as parameters for each relevant query.

If you are just opening the view, running through the list and populating non db aware components (eg. TListView) then there are 2 ways to do it.
Either do it in code in a "while not myview.eof do.." loop, or add a "Calculated field" in delphi (on the component) called "category" for example

To determine the start and end dates for a range (today, last week, last month) you can do :-

find today
StartDate := Trunc(Now)
EndDate := StartDate + EncodeTime(23,59,59,99); // just before midnight

find yesterday
StartDate := Trunc(Now-1);
EndDate := StartDate + EncodeTime(23,59,59,99); // just before midnight

find "this week (mon-sun)"
StartDate := Trunc(Now);
while DayOfWeek(StartDate) <> 1 do StartDate := StartDate - 1 // keep going back until we find "Monday"
EndDate := StartDate + 6 + EncodeTime(23,59,59,99);

find "this working week (mon-fri)"
StartDate := Trunc(Now);
while DayOfWeek(StartDate) <> 1 do StartDate := StartDate - 1 // keep going back until we find "Monday"
EndDate := StartDate + 4 + EncodeTime(23,59,59,99);

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 + 4 + EncodeTime(23,59,59,99);

find "last month"
var
  y, m, d: word;
begin
  DecodeDate(Now, y, m, d);
  EndDate := (EncodeDate(y, m , 1) - 1) + EncodeTime(23,59,59,99); // 1st of the month, -1 day, will be "last day of last month"
  DecodeDate(EndDate, y, m, d);
  Startdate := EncodeDate(y, m, 1); // 1st of whatever month (and whatever year) last month was
end;

You can find the "working week" etc. from any arbitrary date,  the same way as above, my just setting "StartDate" to the date you are currently looking at, and working out monday from there
If you are using mutliple queries, just plus those start and end dates into each query.
eg.
myview_today.sql := 'select * from myview where SomeDate between :startdate and :enddate';
myview_today.ParamByName('StartDate').AsDateTime := Start_Today;
myview_today.ParamByName('EndDate').AsDateTime := End_Today;
.. etc

For the actual display I would use multiple TListViews
place a TScrollbox or TPanel on the form, and put a TListView in it, call this "lvToday"
set up the columns, and set the ViewStyle to vsReport. set the Alignment to alTop;
copy the component, and paste it in the same scrollbox/panel as well, call this one "lvYesterday"
... repeat this until you have all the ranges you wanted
when putting items into the listview, you might want to make certian listviews invisible if there is no data in them
eg. lvLastMonth.Visible := (lvLastMonth.Items.Count > 0);

If you only wish to use 1 query, and just want to decide which "list (eg. listview)" or "group" to put it into, you work out the date ranges 1st, then compare the item's date as you go
eg.
var
  Start_Today, End_Today, Start_Yesterday, End_Yesterday, Start_LastWeek, End_LastWeek, Start_LastMonth, End_LastMonth: TDateTime;
  y, m, d: word;
  newitem: TListItem;
  listview: TListView;
begin
  Start_Today := Trunc(Now);
  End_Today := StartDate + EncodeTime(23,59,59,99); // just before midnight
..
..// rest of dates
// ...last month
  DecodeDate(Now, y, m, d);
  End_LastMonth := (EncodeDate(y, m , 1) - 1) + EncodeTime(23,59,59,99); // 1st of the month, -1 day, will be "last day of last month"
  DecodeDate(End_LastMonth, y, m, d);
  Start_LastMonth := EncodeDate(y, m, 1); // 1st of whatever month (and whatever year) last month was

  mytable.Open;
  mytable.first;
  while not mytable.eof do
  begin
    if ( (mytableSOMEDATE.AsDateTime >= Start_Today) and (mytableSOMEDATE.AsDateTime <= End_Today) ) then
      Listview := lvToday // choose which display to put the item into
    else if ( (mytableSOMEDATE.AsDateTime >= Start_Yesterday) and (mytableSOMEDATE.AsDateTime <= End_Yesterday) ) then
      Listview := lvYesterday // choose which display to put the item into

..      
    else if ( (mytableSOMEDATE.AsDateTime >= Start_LastMonth) and (mytableSOMEDATE.AsDateTime <= End_LastMonth) ) then
      Listview := lvLastMonth // choose which display to put the item into

    newitem := ListView.Items.Add(mytableDESCRIPTION.AsString);
    newitem.SubItems.Add(DateTimeToStr(mytableSOMEDATE.AsDateTime);
  end;
// you could also do this with a list if you have already grabbed the records from the table
end;

If you are using a "calculated field" in delphi, you would set the ."OnCalcFields"
procedure TForm1.MyViewCalcFields(dataset: TDateset);
begin
    if ( (myviewSOMEDATE.AsDateTime >= Start_Today) and (myviewSOMEDATE.AsDateTime <= End_Today) ) then
      myviewCATEGORY.AsString := 'TODAY'
    else if ( (myviewSOMEDATE.AsDateTime >= Start_Yesterday) and (myviewSOMEDATE.AsDateTime <= End_Yesterday) ) then
      myviewCATEGORY.AsString := 'YESTERDAY'
..
    else if ( (myviewSOMEDATE.AsDateTime >= Start_LastMonth) and (myviewSOMEDATE.AsDateTime <= End_LastMonth) ) then
      myviewCATEGORY.AsString := 'LASTMONTH';
end;


hth, Loki
0
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 17923946
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



0
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 17924754
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question