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 :-).

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.


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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delphi component that can load a DLL in design time? 8 64
Delphi selector screen 2 80
update joined tables 2 47
Slow Restore if incremental backups using RDiff.exe 4 21
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
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…

831 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