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 !🙏

I might have said ir wrong though above, port 80 in the container maps to port 8080 on the host which means I would need to access localhost using :8080 provided the container is not exited. I have tried it in the interactive mode to no avail. I get the same page. Please help resolve. Thanks.

Internally it exposes the 8080 on the container which is mapped to port 80 on the host. So simply a localhost should be working provided the container does not exit as soon as it starts. However the container status is exited unless in the interactive mode. If the container would be UP and not exited, I would think it maybe a network / port issue but since it's exited I feel the issue may lie else where. So what do you say. Thanks.

Hi, I know it allows the container to communicate with the host by mapping the container port 8080 to host port 80. But how does that link up to this ? I thought it to do with the settings of environment variables related to lynx as that's what google look-ups suggest. A better hint from you maybe might do the trick. 🙂 Thanks.

Hi, Sorry for the delay. So I ran it using : docker run -p 8080:80 -d mysite and the browser at localhost and got the following page So I ran it interactive and it is still the same. apachectl start, restart or apache2ctl -D FOREGROUND gives the following error: apachectl status gives So the problem seems to be somewhere here. I am googling for the solution. In case you have help, would be great ! Thanks !

I'll do another run and revert soon.

Also I used test.local because the command "apachectl start" gave the warning something like not fully defined server hostname or sth.

the www gets appended by self even when i type test.local in the browser. I tried using localhost before I came this route. That gave the same results.

Hi requinix ! 😀 Thanks for the response. This is how I invoked the container run : docker run -i -t -h test.local -p 8080:80 mysite /bin/bash which set the host to test.local and provided a bash for inspecting. Thanks !

Hi all, Apologies for REPOSTING as a part of the message had a wrong font color and was invisible. I am just trying out the docker example here It works with some minor changes to the dockerfile. Working dockerfile : FROM ubuntu: FROM ubuntu:18.04 MAINTAINER Dan Pupius <dan@pupi.us> # Install apache, PHP, and supplimentary programs. openssh-server, curl, and lynx-cur are for debugging the container. RUN apt-get update && apt-get -y upgrade && DEBIAN_FRONTEND=noninteractive apt-get -y install \ apache2 php7.2 php7.2-mysql libapache2-mod-php7.2 curl lynx-common lynx # Enable apache mods. RUN a2enmod php7.2 RUN a2enmod rewrite # Update the PHP.ini file, enable <? ?> tags and quieten logging. RUN sed -i "s/short_open_tag = Off/short_open_tag = On/" /etc/php/7.2/apache2/php.ini RUN sed -i "s/error_reporting = .*$/error_reporting = E_ERROR | E_WARNING | E_PARSE/" /etc/php/7.2/apache2/php.ini # Manually set up the apache environment variables ENV APACHE_RUN_USER www-data ENV APACHE_RUN_GROUP www-data ENV APACHE_LOG_DIR /var/log/apache2 ENV APACHE_LOCK_DIR /var/lock/apache2 ENV APACHE_PID_FILE /var/run/apache2.pid # Expose apache. EXPOSE 80 # Copy this repo into place. ADD www /var/www/site # Update the default apache site with the config we created. ADD apache-config.conf /etc/apache2/sites-enabled/000-default.conf # By default start up apache in the foreground, override with /bin/bash for interative. CMD /usr/sbin/apache2ctl -D FOREGROUNDs However I am unable to access the site on the browser on localhost. I get a page which says Hmm. We’re having trouble finding that site. We can’t connect to the server at www.test.local. If that address is correct, here are three other things you can try: Try again later. Check your network connection. If you are connected but behind a firewall, check that Firefox has permission to access the Web. Bashing into the server shows no errors. Everything seems to be in place, the vhost files are there. It is enabled. I even tried with the IP address in hosts file. But no luck. Anything else I should check ? What could I be missing here. I am spinning the container off centos7 and not using windows. Thanks !

Hi, I am just trying out the docker example here It works with some minor changes to the dockerfile. Working dockerfile : FROM ubuntu:18.04 MAINTAINER Dan Pupius <dan@pupi.us> # Install apache, PHP, and supplimentary programs. openssh-server, curl, and lynx-cur are for debugging the container. RUN apt-get update && apt-get -y upgrade && DEBIAN_FRONTEND=noninteractive apt-get -y install \ apache2 php7.2 php7.2-mysql libapache2-mod-php7.2 curl lynx-common lynx # Enable apache mods. RUN a2enmod php7.2 RUN a2enmod rewrite # Update the PHP.ini file, enable <? ?> tags and quieten logging. RUN sed -i "s/short_open_tag = Off/short_open_tag = On/" /etc/php/7.2/apache2/php.ini RUN sed -i "s/error_reporting = .*$/error_reporting = E_ERROR | E_WARNING | E_PARSE/" /etc/php/7.2/apache2/php.ini # Manually set up the apache environment variables ENV APACHE_RUN_USER www-data ENV APACHE_RUN_GROUP www-data ENV APACHE_LOG_DIR /var/log/apache2 ENV APACHE_LOCK_DIR /var/lock/apache2 ENV APACHE_PID_FILE /var/run/apache2.pid # Expose apache. EXPOSE 80 # Copy this repo into place. ADD www /var/www/site # Update the default apache site with the config we created. ADD apache-config.conf /etc/apache2/sites-enabled/000-default.conf # By default start up apache in the foreground, override with /bin/bash for interative. CMD /usr/sbin/apache2ctl -D FOREGROUNDs However I am unable to access the site on the browser on localhost. Bashing into the server shows no errors. Everything seems to be in place, the vhost files are there. It is enabled. I even tried with the IP address in hosts file. But no luck. Anything else I should check ? What could I be missing here. I am spinning the container off centos7 and not using windows. Thanks.