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

Posted on 1997-12-22
Medium Priority
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
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
  • 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 100 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 :-).

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

770 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