How to do this? The best way?....

Posted on 1997-12-22
Last Modified: 2010-04-04
Hi all,

I'm letting the user select his data from a treeview. On a few months there will be a lot of data. So I came up with this idea :
 The records in the tables are keyed with date & time. So I thought let me bundle these records in to years. Months, weeks. So the user starts to select the year, then the month, then the week and finally he can easely select his data in this small list.
 No, my question. How can I do this the best? How can I implement this in SQL? Do I have to use several SQL's or is it possible to implement this in one SQL? My idea is several SQL's, but maybe there are better ways....

Question by:ZifNab
  • 3
  • 2
  • 2

Expert Comment

ID: 1354420
If I understand you right, you want to find all the records that correspond to some week (?).

So why not write smth. like this:

Select <.o00o.> where (date > weekbegin) and (date < weekend)

Author Comment

ID: 1354421
Not exactly, that's what I had in mind, but then you also need to make a query wich select all months and all years. Because I only wants the user see which years and months are in the database. Let say, if in march '97 no data is archived this month may not be seen. If in '98 data is collected in march -> March has to been seen.
So for that I need to have something which combines the data to the possible years. Something that combines the data of a year into months and something that combines the data of a month of a specific year into weeks. The last one is possible with what you've wrote. The others??? I really don't know if it's possible.
And is it possible in one query???

So this is what the user does :

   ----- January
   ----- February
   ----- March
           --- day 1..7
           --- day 8..15
                  ------  8
                  ------  9  Got the data!
           --- ...        
  ---- ....


Accepted Solution

juris earned 50 total points
ID: 1354422
I don't know if I'm getting this right, but IMHO the most economic solution would be once SELECT and go throught all the dates and generate all the TTreeNodes (by splitting the date by YYYY, MM and DD and then creating a new node where appropriate).

You see, You could generate the TTreeView on the fly, but You still would have to go once through all the dates to see which years do you have, and then, when a year is ripped open, through all dates of that particular year to select which months does this year have with date, etc, etc, which, I think, would be more hassle then memory economy, but of course, it is an alternative.

Hope I got Your problem right :-).

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.


Author Comment

ID: 1354423
Mmmmm, not such a bad idea,
But, instead of iterating, it would quicker to implement if I could select the dates in a query. But how to do that. How can I split the date in a query?


Expert Comment

ID: 1354424
Aha... In the beginning I thought you wanted to do the oposite thing : you have already put all the data into the treeview and then you want to deal with the data of a week that the user selects.
If I got you right now, you want to create the tree itself. OK, you show the years in the beginning, then when the user click's a year you show the months, then when the user clicks a month you show the weeks, and finally when the user clicks a week you show all the corresponding data (am I correct?). But, what exactly of this process you want to do in one SQL statement?
Every step of this process is obvious... Maybe you want to generate some kind of a database ?

(By the way, I'm not familiar with 'IMHO', so I'll apriciate if you explain me what it is...)

Expert Comment

ID: 1354425
Hey, I think I know what we should do.

You should generate one more database, where you would keep information about the years and year/months which you do have. Updating this whenever new data arrives is a piece of cake. Generating this one once is fairly fast and done only once. And then you let the user select the years and months from this database and then just subselect from the main.

P.S. IMHO = in my humble opinion


Author Comment

ID: 1354426
Matvey, yes you're correct.
Why I want to do it this way? Sometimes I got strange ideas... And this is one. In the earlier version of my program, I've let the user choose a site (first selection!, then select data of that particular site) in a tree which I had build. But, it's so slow and that was only for putting the sites to the tree (with last read dates)! Other Dates were selected with a Popup-Calendar. (Problem: I didn't wanted that the user could select a date which wasn't in the database) I don't have the experience or the time to make my own popup. If find a really good explained pop-up calendar, I can change the code myself but at this moment I couldn't find any.

So I thought, why not trying some DBTreeview or DBExplorer components?
  1. I don't have to write so much code.
  2. They can't be worser in time consuming.
And then I thought, instead of making lots of queries which just do one thing. Namely filtering & sorting in year, month, day. Maybe it's possible to do it all in one!.
By the way, how the heck do you filter on year, month, day if you've saved them not seperately.

that's possible too. But I think that then I've the same problem. I still have to visualise this data in a 'beautiful' way to the user.



Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

791 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