Project Description:
I need a complicated query written from a postgreSQL database. Ill try and explain what i need and how i think it might be done but im not familiar with SQL queries so you might have a better, easier method.
SQL Query 1)
I have a large database and a record is inserted with a timestamp and also a $ amount and a unique ID in a table, there is other columns other than timestamp and $ amount and ID but these are the only ones we need. There might be anywhere up to 500,000 records in total in the table but i would like a query that returns based on the 12 months of the year so it would be 12 queries in total, 1 for january, february, march and so on. I guess the query itself will be almost identical except for the month date range so once you do one the other 11 will be easy.
What the query should do is search all the records for that month and find records where the timestamp had no more than 1 hour between that record and the next record.
For example if i have record id 1-100 and every timestamp had less than 1 minutes between each record then these would be grouped together as 1 session as none exceeded 1 hour.
If the 101st id timestamp was 1hr 10mins after the last record then this would be a new group or session and not part of the last session.
Ill make it clear why we need to break these up into sessions later.
If there was another break of 1hr and 14mins to the next record no 102 then record 101 would be 1 hand on its own in the session, then from record 102-156 there was several 10 mins between some timestamps but no timestamp was more than 1 hour difference from the one directly before that so all the records from 102-156 would now be a new session.
We would now have 3 sessions
1-100
101
102-156
As i mentioned before every record in there has a timestamp, ID and amount either positive or negative. The query would need to allow me to search through a session in the order the hands were created and calculate the amount and if the amount is -300 (or whatever amount i decide) then every record from ONLY THAT SESSION/GROUP after the point where it exceeds -300 should be calculated and the result of how much the total was after it reached -300 should be put in a new Table in the database along with the 1st hand timestamp and the last hand timestamp FROM ONLY THAT SESSION and the total records in that session and finally the total records after the -300 was hit.
Now if i didnt make it clear the last paragraph is done for each session so it would check session 1 (1-100) and if it calculated the $ as -300 on hand no 60, then it would create an entry in the TABLE with how much the total was for record 61-100, it would also take the timestamp off hand 1 and 100 as the 1st and last timestamp and the total records would be 100 and the total after -300 was exceeded would be 40. For the second sesion it was only 1 hand so it just ignores this since it didnt exceed -300 and for the 3rd session it also didnt exceed -300 between the 102nd and 156th record so it skips to the next session until the end of the month is reached.
So the new table (lets call it Exceed Table) would show
UniqueID
Total_after_exceed (AFTER PREVIOUS TOTAL EXCEEDED -300)
1st_Timestamp (from that session group and not the whole month)
last_Timestamp (from that session group and not the whole month)
Total_records - of all hands in that session/group
Total_records_after (-300 was exceeded until the last record in the session_
WHERE IT DIDNT EXCEED -300 during a session it should just skip that and move to the next session and run the test. At the end of it i will have a table with all the info i mentioned above and then this is the final query.
It should calculate the total loss for the month for Total_after_exceed in the Exceed table.
With the code, i would need some commenting added are some guide where to change the amount from -300 to whatever i want and also i presume i would need to enter the database name somewhere in the code.
And id also need instructions where in PGADMIN or whatever i will enter the first query to divide the sessions up and create the Exceed Table and then somewhere i can type in the query to get the result of Total_after_exceed for the month and i presume this is done PGADMIN in the query tool.
Again there may be a much easier way of doing this, so please feel free to suggest, the main thing i want is the total from each session AFTER the session exceeded -300.
Id also like details of each sessions 1st and last timestamp where it exceeded -300 that i can review from a table, hence the reason i came up with the Exceeded table and finally the total record count and count after it hit -300 for that session.
I can provide you with a sample database if you need it, ill backup the database and let you restore it on your PC. Ill send you a smaller one though as youll only need a few records id imagine.
Finally we need to consider each monthly search will have up to 70k records so i dont want timing out or crashing.
