|
I absolutely have to display the date difference that is how the specification is made.
Let me try saying it this way:
I have to have a running total for the times until there is a 30 minute or larger gap or if a new user ID which is a different person is the next row of the dataset. then that is a session then I have to increment the session ID for each Session.
like
Update
SET [Session ID] = [Session ID] + 1
WHERE [Date difference] < 30 Minutes
OR If dates run out for this person presenting a new person.
And for then the running total must start again.  It is allot to ask I am sorry but any help would be greatly appreciated.
|
|
|
|
|
Martin Niemandt wrote: I absolutely have to display the date difference
Fine, but you should calculate it at the time you need to display it. Storing calculated values in the database is bad design and prone to error. I would suggest reading http://www.w3schools.com/sql/func_datediff.asp[^].
|
|
|
|
|
Thank you for the function, but it is not quite solving my problem. If you could maybe give me a guideline on how I can update using this row combined with the following row. and then also group by as to not overlap users? if not then thank you for your effort.
|
|
|
|
|
Sorry, but my SQL skills are not very advanced. And I'm not sure that I fully understand what you are trying to achieve.
|
|
|
|
|
Yes it is quite difficult to explain as well, I would show you my query but you might puke! Thank you for your effort though! 
|
|
|
|
|
Martin Niemandt wrote: I would show you my query but you might puke!
Which suggests that your design needs looking at.
|
|
|
|
|
It is not the design really it is the indents and spacing that is terrible at the moment. thanks any way I figured it out!
|
|
|
|
|
Richard is correct in that the calcs should not be stored. Look into creating a view to service your requirements.
Where you need 2 rows to interact you can create a left join back to the same table on A.UserID = B.UserID and B.ID = A.ID + 1. Do not forget to test for null ISNULL(B.Value,0)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you that helps. I do understand when you guys say calculated fields should not be stored but I have to store summary's of 80 million rows. and when they are stored they will not change but get added to. I could show you my query then you could tell me what to improve if you want. but thank you any way
|
|
|
|
|
For the query where you need to access the 2nd row I would store the results during a process run.
A summary of 80 million implies less than! Most people dealing with this sort of volume create OLAP cubes for reporting purposes (summaries optomised for reporting purposes).
If you have a query where an calculation is being performed within the row data there is no need to store it simply do the calc in your select procedure.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Interesting I will go take a peek at OLAP, The only problem is I am moulding the data in parts updating parts after the initial insert. I just need to get a running total of the date difference (Which is used as time) and I need to restart the counting when the difference (not the total) is more than 30 minutes and then I need to assign that session an ID a session ID must not lap over different users. the part I am stuck with is 1. the running total of the time difference 2. assigning incremented ID's based on this logic
[Edit]: Your left join idea helped already thank you
|
|
|
|
|
Do some research into ROW_NUMBER and PARTITION OVER these may be the keywords you are looking for. Oh no I'm channelling POH
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
channelling POH? :P I am already using DENSE_RANK(). but I didn't think of partition thank you
|
|
|
|
|
Mycroft and Richard are completely correct in that you should never store duplicated data.
But theres a way to automize it, on SQLServer it's called a Computed Column[^] and if you're using Oracle it's a Virtual Column[^].
The best part is that you can put an index on them.
|
|
|
|
|
Haha I realize that they are correct. and it is not going to store duplicate data it will always change. see this is to analyse user actions and time they spent lingering about on certain pages. and I know about Computed columns I am a bit more intermediate with sql but thanks any way
|
|
|
|
|
Hi,
I want to know if there is a simple and efficient way to select a value from a row that has a column with minimum creation time for the given parameters.
Let us take a sample table named Patient
-----------------------------------------------------------
PatientId Observation Time Value
-----------------------------------------------------------
1 Temp 2014-02-19 03:55:00 35
1 Temp 2014-02-19 03:45:00 37
1 Weight 2014-02-19 03:40:00 60
If i am given the PatientId and Observation, I need to retrieve the value field with minimum creation time
For patient id 1 and observation Temp this would be Value 37
since it has 2014-02-19 03:45:00 as minimum creation time
|
|
|
|
|
This should work:
select top 1 Value
from Patient
where PatientId = 1
and Observation = 'Temp'
and Time in
(
select min(Time)
from Patient
where PatientId = 1
and Observation = 'Temp'
)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Thanks. That seems to be simple. If i have numerous entries for the specific patiend id and observation, will the use of top be performance efficient ?
|
|
|
|
|
Top ensures that only one line is returned.
You can remove the top 1 - however if more than one line is returned you will have to make a choice at some point if the values are different.
Simply replace the top 1 with distinct if you know there will only be one value returned.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
So...it seemed like a good idea at the time. I have a C# application that is distributed among multiple users (about a dozen). I set each user up with their own database (MS SQL 2005) so there is no interaction between users and it works great. Now, I am making a change to the program which requires a change to the database. In the past, with just a few users, I simply went into each database and altered the tables one at a time ... takes a few minutes for each one. But as the number of users grow and I keep adding features to the application more frequently ... well, you can see where this is going. My question is; what type of command can I use to alter the databases (a script of some kind?) to add or remove columns from tables, add tables, etc. to make it so that I can alter multiple identical database structures at the same time while maintaining the data in them. Although I have been making databases for my applications for about 15 years, this is an area that I have never had to deal with; something I would assume would normally be the job of a dedicated DBA. If applications end up being sold on a larger scale (and it is heading that way), then I would hire such a person immediately. I considered having everyone in a single large database at the start and simply use proper queries to pull the data, but I decided that the sheer size and loss of performance would not make it as nice as it is, especially if I were to end up with several hundred users. There are about 30 tables in the database. I never really understood the limits and capabilities of these databases; only to say I don't really trust them to do the job when they get too large(not very scientific; more of a gut feeling). Am I way underestimating the ability of the database? Until then, can you give me a lead in on how to do this? I love it here at Code Project and I Thank You for your time....Pat
|
|
|
|
|
Whew, lots of questions here.
Let me suggest one item.
You should create a script that would methodically alter the necessary tables, columns etc and use that script to update your various clients.
Here is a reference to the ALTER TABLE command ...
http://msdn.microsoft.com/en-us/library/ms190273.aspx[^]
There are tools out there which compare 2 schemas and generate scripts which make the necessary changes. The idea is that you point it at database1 ver 1.0 and at database2 ver 2.0 and the tool will detect that there are new tables, new columns, etc.
|
|
|
|
|
Thank you David. I appreciate your suggestion and I have found a little better link (solution) than Microsoft's to describe the Alter command's use. I will practice with it on a sample database and then include that script as part of the program update. If you get this and you specifically know of some better comparative software for database modification, please let me know, and THANKS again....
ps; sorry about all the questions. I am methodical and just want to get it right the first time if I can. I appreciate your response! Pat
|
|
|
|
|
I am wondering about your concerns about consolidating to a single large database. What are the largest row counts for your 30 tables for the biggest user? Database consolidation would seem to be a very attractive option, unless the numbers are extremely large. I suspect your may actually be losing performance by having seperate databases. A DB instance has a considerable base overhead and you are decreasing the available resources by that amount for each user you have. Further, the simplifacation in administration would be considerable.
Regards
Nicholas Swandel
|
|
|
|
|
Thank you for your thoughts Nicholas. Truthfully, I am confused due to my lack of experience with "big" databases. Big is a relative term. What is Big? Perhaps some numbers might help. Lets say that there is a table called 'Orders' and it has 30 columns. Now lets say that it creates about 10 new rows every day per client. It must be able to retrieve 2 years worth of data in this table before it can be purged, or about 500 days. So that would be 5000 rows per client. Now, if it was a single database being shared by potentially 1000 users, that would make 5 million rows with 30 columns each for this table. Assuming that about 8 of the tables have this same amount of data, and the others are much smaller, the question is "is this big"? The problem is that I do not know. It seems big to me, but then again, I never understood how a major search engine can look through billions of entries in a matter of seconds either. To your statement that separate databases decrease performance, I would disagree with that in its entirety. On the other hand, I totally agree that Administration, which is what triggered the request for information in the first place, is definitely the issue of concern for me with individual databases. I suppose I could write an 'update' script to modify the database each time I update the program (a possible solution). I am at the point that I must go one way or the other. You raise some excellent points. Looking it over, perhaps a Hybrid choice is possible. Nothing says I cannot have 10 databases instead of 1000 which would limit the total users to 100 and decrease the data storage by 90 percent. Perhaps? The question is...Is that Enough? At how many users do I stop and make another database...10, 50, 100? I will keep researching and considering expertise from developers like yourself, for which I am grateful. If you have any followup thoughts, or can shed some light for me on the numbers game, please feel free to write me either here or privately. I appreciate your time and expertise. Thank you again. Best Regards, Pat
|
|
|
|
|
5 million is not very big. Typically tables are indexed to make retrieval quick. Further there are other DB features such as partitioning that would allow the table to appear as one large table but the DB would actually store the table and its indexes in seperate partitions based on some attrubute, likely, in your case, a user id or more likely a set of user ids. Retreival would then only look at the index and table partitions for the individual user or set of users. Keep an open mind, I think your strong view that separate databases increase performance is miss placed. We have done a exercise of consolidating 50+ databases each with 30,000+ tables and 10 of millions of rows in some tables. Same harwdare and the performance gain was about 3 times, administration effort cut 3 fold. The memory and CPU requirements just to start a single DB is considerable, consolidating allows these resource to be better utilized and boosts performance. There is also the idea that each user could be contained in a schema within a single DB, this is a half measure though as will you would gain performance, administration would still be high. I would encourage you to try a proof of concept with a single table on 10-30 DBs shut down those DBS and compare it to the combined verion of the table on one DB with all the resources from the 10-30 DBS allocated to single DB. The single DB is typically dramatically faster.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
