ajoo

Guru Barand Magic ! Took me quite some time to figure out what you did. Your attempt is far more efficient as well as compared to the 3 temp tables solution. .0089 seconds vs 0.013 seconds. Thanks loads. 🙏

The following with yet another temporary table achieves it but temporary tables is what I wish to avoid. create temporary table mina1111_c Select * from ( select a.RecNo , a.User , a.V_Score , @count := @count-1 as reccount from ajoo as a JOIN (select @count:=6) as init where User = 'mina1111' ORDER BY RecNo DESC LIMIT 5)sub Order by RecNo ASC; SELECT a.RecNo , a.V_Score , ( SELECT AVG(b.V_Score) as avscor FROM mina1111_b b WHERE reccount BETWEEN a.reccount-4 and a.reccount ) as av5 , ( SELECT SUM(c.V_Score) as SUM3 FROM mina1111_c c WHERE reccount BETWEEN a.reccount-2 and a.reccount ) as SUMMED3 FROM mina1111_a a JOIN ajoo j using (RecNo); WHERE a.reccount > 4; Is there a better, more efficient way to achieve this or is this the only way to go? Thanks loads !

Thank you Guru Barand for the response. I understand that there are issues with the temporary tables as also mentioned by you in this very thread earlier. So if i must use them then creating a 3rd temporary table would be the only option. I also recall your mentioning that this is not the most efficient query, which would make it even more so if I created a 3rd temporary table. Therefore I ask what is the solution to this, if we are not to use temporary tables? What should be done ? Thanks loads again !

Hello all ! I have been trying to ADD another column to the final result of this query which is the TOTAL of the SCORE OF THE 3 most current rows like this: SELECT a.RecNo , a.V_Score , ( SELECT AVG(b.V_Score) as avscor FROM mina1111_b b WHERE reccount BETWEEN a.reccount-4 and a.reccount ) as av5 , (SELECT SUM(b.V_Score) as sumscore FROM mina1111_b b WHERE reccount BETWEEN a.reccount-2 and a.reccount ) as summed3 FROM mina1111_a a JOIN ajoo j using (RecNo); WHERE a.reccount > 4; But it does not seem to be working and gives the old familiar "can't reopen table b " error. Is there any simple way to achieve this ? The following is the output that I am trying to get. +-------+------------+--------+--------+ | RecNo | Wrt_V_Sums | av5 | summed3| +-------+------------+--------+--------+ | 10 | 5 | 5.0000 | 5 | | 11 | 0 | 2.5000 | 5 | | 13 | 1 | 2.0000 | 6 | | 14 | 1 | 1.7500 | 2 | | 15 | 1 | 1.6000 | 3 | +-------+------------+--------+--------+ All help appreciated. Thanks !

SIr, I think I will get the solution from all the solutions provided by you. In case I run into a roadblock. i'll ask again. is worth 2K Guru Barand. You are absolutely correct . The current date should be used as the last date for the edge condition. Thank you.🙏

I am getting 502 as the sum below. The last date is the last logout date. datein diff 30-03-2019 0 01-04-2019 1 04-04-2019 2 18-04-2019 13 23-04-2019 4 24-04-2019 0 01-05-2019 6 08-05-2019 6 09-05-2019 0 18-06-2019 39 05-09-2019 78 28-09-2019 22 28-09-2019 0 28-09-2019 0 03-10-2019 4 22-05-2020 231 27-08-2020 96 total 502

Hello Sir, I am quite sure that you are correct. Possibly my description of the problem was flawed. Though I had remarked once or twice in my replies that we should use just the days between logins to find the correct number of gaps or absents and then for the edge condition, add (dateout - datein) of the last record for that user to the total count. Sir I am not saying that your solution is wrong anywhere. I was only trying to spell out what is desired by me. Thank you so much.

Sir, I am referring to the examples above. If I may refer to the very first solution you proposed as below: An examination of output table produced on running the subquery in your example shows a total absents of 327 days. However inspecting row 11 showa that a gap of 8 days between the datein and dateout of record 11 is unaccounted for. This will happen for all cases where the datein and dateout of a given row are different. This can be rectified by using only the datein of each row and ignoring dateout Finally the absents of the last row (dateout - datein)-1 are not being accounted for in this and subsequent solutions. I hope I am clear in defining the issue this time ! 😅 Thanks loads !🙏

There are 2 gap difference that both these solutions have overlooked. A look at row 10 and 11 reveals that there a gap of 8 days between dateout on 10th row and datein on 11th row. which is unaccounted for. So on 11th row the diff should be 22 and not 14. Further on the last row there is a gap which could be calculated between dateout on 14th row and datein on 15th row i.e. DATEDIFF( '2020-08-27', '2020-05-22'). +-----+---------------+------------------------+ | Row | dateout | datein |diff | +-----+---------------+------------------------+ | 1 | '2019-03-30' | '2019-04-01' | 1 | | 2 | '2019-04-01' | '2019-04-04' | 2 | | 3 | '2019-04-04' | '2019-04-18' | 13 | | 4 | '2019-04-18' | '2019-04-23' | 4 | | 5 | '2019-04-23' | '2019-04-24' | 0 | | 6 | '2019-04-24' | '2019-05-01' | 6 | | 7 | '2019-05-01' | '2019-05-08' | 6 | | 8 | '2019-05-08' | '2019-05-09' | 0 | | 9 | '2019-05-09' | '2019-06-18' | 39 | | 10 | '2019-06-18' | '2019-09-05' | 78 | | 11 | '2019-09-13' | '2019-09-28' | 14 | | 12 | '2019-09-28' | '2019-10-03' | 4 | | 13 | '2019-10-01' | '2019-10-03' | 1 | | 14 | '2019-12-10' | '2020-05-22' | 163 | | 15 | '2020-08-27' | (null) 0 | | +-----+----------------------------------------+ Thanks !

Thank you Guru Barand. May I request you to clear some doubts that I expressed above in my earlier message. Thank you.🙏

Sir that's not my intention at all. It's just that sometimes there are so may aspects to a problem that I try and request help for the core of it while trying to attempt the changes around it myself. It's only if I get stuck subsequently, like because of what i added, then I ask again. But I will keep it in mind and try and avoid that as far as possible. Thank you.

Hi Guru Barand, Thank you very much. I couldn't have figured it out this way since I have never really worked much with @variable in mysql. I am not sure I understand their working very well. However, this below is what I tried and it almost works except for the first and last bit of absents and resembles somewhat what requinix suggested. SELECT RecNo, DATEDIFF(( SELECT t.TimeOfLogin FROM india_sessdata t WHERE t.RecNo > t1.RecNo ORDER BY t.RecNo LIMIT 1), MIN(TimeOfLogin) ) diff FROM india_sessdata t1 WHERE t1.StudentLogin = 'mina1111' GROUP BY RecNo; which gives the following output +-------+------+ | RecNo | diff | +-------+------+ | 3 | 184 | | 6 | 3 | | 7 | 14 | | 8 | 5 | | 9 | 1 | | 10 | 7 | | 11 | 7 | | 13 | 1 | | 14 | 40 | | 15 | 79 | | 17 | 23 | | 18 | 0 | | 19 | 0 | | 20 | 3 | | 22 | 0 | | 74 | 21 | +-------+------+ The first is incorrect because I think I am using a subset of values related to StudentLogin = 'mina1111'. This bit -- MIN(TimeOfLogin) -- in the query needs to be tweaked to get the correct first value. I tried a few things but none worked. The last entry is not really wrong but is incomplete. The SUM( ---) function needs to use only TimeOfLogin or dateIn (as used by you). The differences of all dateIn values needs to be calculated and in the end to complete the absents , for the last row alone, it needs to calculate (dateOut - dateIn). I am trying to understand your solution. The value of Date(@prevout) CASE WHEN DATE(datein) > DATE(@prevout) is initialized further down here JOIN (SELECT @prevout := NULL) init -- initialize @prevout Shouldn't it be initailized someplace before it is used at DATE(@prevout) ? Also I cannot really visualize what this join is doing. Some explanation would really help. Thanks.

Thanks Guru Barand and Requinix for the replies. @Guru Barand : Please find the data attached as data. I have tried your method but I am stuck at the comparison part where I need to reference the next date. @ Requinix : HI, I get the idea but I am not sure how to go about it. Thank you.

Hi, I have the following login and logout data of a student :- Would it be possible to calculate the absents by subtracting consecutive times of logins or logouts using just Mysql ? From the example above the absents between 2019-04-01 and 2019-03-30 should be SELECT DATEDIFF("2019-04-01", "2019-03-30") = 2. So actual absents is 2 -1 = 1 and so on for all the consecutive dates and then their sum. I have no clue how to go about this. Gurus please help. Thanks !

So eventually after a loads of tries on my existing image, I finally removed all the containers, images from my host and rebuilt the container again. To my utter delight, the status on the container showed UP !!!!!!!!!!! 😇 This time it ran straight away !! I think the image was corrupted somewhere and the container always was in the exited state immediately after going UP. Thank you very much requinix for that insight into the -p switch and to finally get this working. Thanks loads !🙏