Solved

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

Posted on 1997-12-22
7
135 Views
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....

Thanks,
Zif.
0
Comment
Question by:ZifNab
[X]
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
7 Comments
 
LVL 3

Expert Comment

by:Matvey
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)
0
 
LVL 8

Author Comment

by:ZifNab
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 :

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

 
0
 

Accepted Solution

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

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Author Comment

by:ZifNab
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?

0
 
LVL 3

Expert Comment

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

Expert Comment

by:juris
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

0
 
LVL 8

Author Comment

by:ZifNab
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.

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

729 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