Solved

Help with algorithm

Posted on 2006-11-10
5
180 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
  • 3
  • 2
5 Comments
 
LVL 15

Expert Comment

by:mikelittlewood
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now