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

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

Thanks,
Zif.
LVL 8
ZifNabAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MatveyCommented:
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)
0
ZifNabAuthor Commented:
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 :

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

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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

ZifNabAuthor Commented:
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?

0
MatveyCommented:
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...)
0
jurisCommented:
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

0
ZifNabAuthor Commented:
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.

Juris,
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.

Regards,
Zif.



           
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.