intricate date query sql
-
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!
-
Hi can anyone help me I don't necessarily want a solution just an idea. I have a table e.g ID - Start Date - End Date - Difference(Duration) - SessTotal - SessID Now the Id is per user. and the date difference is the time spent doing something. If the difference is greater than 30 minutes I need to increment the session ID by 1. Second I need to create a running total for session total per session. 1 session ID must also not be able to have more than 1 UserID [ID] in it's row.
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
-
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
-
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
-
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
-
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
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.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
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
-
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.
Wrong is evil and must be defeated. - Jeff Ello[^]
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