DB Query adjustments

My database gets a running "runtime hours" value from our PLCs in the field every morning. I use a query in an expression tag to, every morning, take the last written record for these runtimes and subtract the previous days total, which gives me an actual "previous days runtime hours" for some pumps. It's worked well since its been implemented, but now my customer is asking for runtime hours for every day for the last two years of data. I'm not fantastic at sql scripting, and I'm hoping that some simple modifications to my current script will generate the results that I want, but so far I haven't had any success. Hoping someone can point me in the right direction or give me some input.

This is the current script:

select
(a.ki_701r_pmp - b.ki_701r_pmp) as 701r_runtime,
(a.ki_nwf1_pmp - b.ki_nwf1_pmp) as nwf1_runtime,
(a.ki_nwf2_pmp - b.ki_nwf2_pmp) as nwf2_runtime,
(a.ki_nwf3_pmp - b.ki_nwf3_pmp) as nwf3_runtime,
(a.ki_nwf4_pmp - b.ki_nwf4_pmp) as nwf4_runtime,
(a.ki_nwf5_pmp - b.ki_nwf5_pmp) as nwf5_runtime,
(a.ki_pderbacr_pmp_1 - b.ki_pderbacr_pmp_1) as pderbacr_1_runtime,
(a.ki_sa2_pmp_1 - b.ki_sa2_pmp_1) as sa2_pmp_1_runtime,
(a.ki_sa2_pmp_2 - b.ki_sa2_pmp_2) as sa2_pmp_2_runtime,
(a.ki_sa2_pmp_3 - b.ki_sa2_pmp_3) as sa2_pmp_3_runtime,
(a.ki_sa2_pmp_4 - b.ki_sa2_pmp_4) as sa2_pmp_4_runtime,
(a.ki_sa_pmp_1 - b.ki_sa_pmp_1) as sa_pmp_1_runtime,
(a.ki_sa_pmp_2 - b.ki_sa_pmp_2) as sa_pmp_2_runtime,
(a.ki_sa_pmp_3 - b.ki_sa_pmp_3) as sa_pmp_3_runtime,
(a.ki_sa_pmp_4 - b.ki_sa_pmp_4) as sa_pmp_4_runtime,
(a.ki_sa_pmp_5 - b.ki_sa_pmp_5) as sa_pmp_5_runtime,
(a.ki_sa_pmp_6 - b.ki_sa_pmp_6) as sa_pmp_6_runtime,
(a.ki_sa_pmp_7 - b.ki_sa_pmp_7) as sa_pmp_7_runtime,
(a.ki_sb1_pmp_1 - b.ki_sb1_pmp_1) as sb1_pmp_1_runtime,
(a.ki_sb1_pmp_2 - b.ki_sb1_pmp_2) as sb1_pmp_2_runtime,
(a.ki_sb1_pmp_3 - b.ki_sb1_pmp_3) as sb1_pmp_3_runtime,
(a.ki_sb1_pmp_4 - b.ki_sb1_pmp_4) as sb1_pmp_4_runtime,
(a.ki_sb1_pmp_5 - b.ki_sb1_pmp_5) as sb1_pmp_5_runtime,
(a.ki_sb2_pmp_1 - b.ki_sb2_pmp_1) as sb2_pmp_1_runtime,
(a.ki_sb2_pmp_2 - b.ki_sb2_pmp_2) as sb2_pmp_2_runtime,
(a.ki_sb2_pmp_3 - b.ki_sb2_pmp_3) as sb2_pmp_3_runtime,
(a.ki_sb2_pmp_4 - b.ki_sb2_pmp_4) as sb2_pmp_4_runtime,
(a.ki_sb2_pmp_5 - b.ki_sb2_pmp_5) as sb2_pmp_5_runtime,
(a.ki_sb2_pmp_6 - b.ki_sb2_pmp_6) as sb2_pmp_6_runtime,
(a.ki_sb3_pmp_1 - b.ki_sb3_pmp_1) as sb3_pmp_1_runtime,
(a.ki_sb3_pmp_2 - b.ki_sb3_pmp_2) as sb3_pmp_2_runtime,
(a.ki_sb3_pmp_3 - b.ki_sb3_pmp_3) as sb3_pmp_3_runtime,
(a.ki_sb3_pmp_4 - b.ki_sb3_pmp_4) as sb3_pmp_4_runtime,
(a.ki_sb3_pmp_5 - b.ki_sb3_pmp_5) as sb3_pmp_5_runtime,
(a.ki_sc_pmp_1 - b.ki_sc_pmp_1) as sc_1_pmp_runtime,
(a.ki_sc_pmp_2 - b.ki_sc_pmp_2) as sc_2_pmp_runtime,
(a.ki_sc_pmp_3 - b.ki_sc_pmp_3) as sc_3_pmp_runtime,
(a.ki_sc_pmp_4 - b.ki_sc_pmp_4) as sc_4_pmp_runtime,
(a.ki_sc_pmp_5 - b.ki_sc_pmp_5) as sc_5_pmp_runtime,
(a.ki_sc_pmp_6 - b.ki_sc_pmp_6) as sc_6_pmp_runtime,
(a.ki_sc_pmp_8 - b.ki_sc_pmp_8) as sc_8_pmp_runtime,
(a.ki_sc_pmp_9 - b.ki_sc_pmp_9) as sc_9_pmp_runtime,
(a.ki_sc_pmp_10 - b.ki_sc_pmp_10) as sc_10_pmp_runtime,
(a.ki_sd_pmp_1 - b.ki_sd_pmp_1) as sd_pmp_1_runtime,
(a.ki_sd_pmp_2 - b.ki_sd_pmp_2) as sd_pmp_2_runtime,
(a.ki_sd_pmp_3 - b.ki_sd_pmp_3) as sd_pmp_3_runtime,
(a.ki_se_pmp_1 - b.ki_se_pmp_1) as se_pmp_1_runtime,
(a.ki_se_pmp_2 - b.ki_se_pmp_2) as se_pmp_2_runtime,
(a.ki_sf_pmp_1 - b.ki_sf_pmp_1) as sf_pmp_1_runtime,
(a.ki_sf_pmp_2 - b.ki_sf_pmp_2) as sf_pmp_2_runtime,
(a.ki_sf_pmp_3 - b.ki_sf_pmp_3) as sf_pmp_3_runtime,
(a.ki_sg2_pmp_1 - b.ki_sg2_pmp_1) as sg2_pmp_1_runtime,
(a.ki_sg2_pmp_2 - b.ki_sg2_pmp_2) as sg2_pmp_2_runtime,
(a.ki_sg2_pmp_3 - b.ki_sg2_pmp_3) as sg2_pmp_3_runtime,
(a.ki_sg2_pmp_4 - b.ki_sg2_pmp_4) as sg2_pmp_4_runtime,
(a.ki_sg_pmp_1 - b.ki_sg_pmp_1) as sg_pmp_1_runtime,
(a.ki_sg_pmp_2 - b.ki_sg_pmp_2) as sg_pmp_2_runtime,
(a.ki_sg_pmp_3 - b.ki_sg_pmp_3) as sg_pmp_3_runtime,
(a.ki_sg_pmp_4 - b.ki_sg_pmp_4) as sg_pmp_4_runtime,
(a.ki_sg_pmp_5 - b.ki_sg_pmp_5) as sg_pmp_5_runtime,
(a.ki_sh_pmp_1 - b.ki_sh_pmp_1) as sh_pmp_1_runtime,
(a.ki_sh_pmp_2 - b.ki_sh_pmp_2) as sh_pmp_2_runtime,
(a.ki_sj_pmp_1 - b.ki_sj_pmp_1) as sj_pmp_1_runtime,
(a.ki_sj_pmp_2 - b.ki_sj_pmp_2) as sj_pmp_2_runtime,
(a.ki_sj_pmp_3 - b.ki_sj_pmp_3) as sj_pmp_3_runtime,
(a.ki_sj_pmp_4 - b.ki_sj_pmp_4) as sj_pmp_4_runtime,
(a.ki_sk_pmp_1 - b.ki_sk_pmp_1) as sk_pmp_1_runtime,
(a.ki_sk_pmp_2 - b.ki_sk_pmp_2) as sk_pmp_2_runtime,
(a.ki_slp_pmp_1 - b.ki_slp_pmp_1) as slp_pmp_1_runtime,
(a.ki_slp_pmp_2 - b.ki_slp_pmp_2) as slp_pmp_2_runtime,
(a.ki_slp_pmp_3 - b.ki_slp_pmp_3) as slp_pmp_3_runtime,
(a.ki_slp_pmp_4 - b.ki_slp_pmp_4) as slp_pmp_4_runtime,
(a.ki_slp_pmp_5 - b.ki_slp_pmp_5) as slp_pmp_5_runtime,
(a.ki_sm_pmp_1 - b.ki_sm_pmp_1) as sm_pmp_1_runtime,
(a.ki_sm_pmp_2 - b.ki_sm_pmp_2) as sm_pmp_2_runtime,
(a.ki_sm_pmp_3 - b.ki_sm_pmp_3) as sm_pmp_3_runtime,
(a.ki_w201_pmp - b.ki_w201_pmp) as w201_runtime,
(a.ki_w202_pmp - b.ki_w202_pmp) as w202_runtime,
(a.ki_w601_pmp - b.ki_w601_pmp) as w601_runtime,
(a.ki_w602_pmp - b.ki_w602_pmp) as w602_runtime,
(a.ki_w603_pmp - b.ki_w603_pmp) as w603_runtime,
(a.ki_w604_pmp - b.ki_w604_pmp) as w604_runtime,
(a.ki_w701_pmp - b.ki_w701_pmp) as w701_runtime,
(a.ki_w702_pmp - b.ki_w702_pmp) as w702_runtime

from
(select * from pump_runtimes_results_new order by t_stamp desc limit 0,1) a,
(select * from pump_runtimes_results_new order by t_stamp desc limit 1,1) b

As I said, this script works for getting the actual runtime hours for a previous single day (and it may not even be the most efficient method, but its working for me). I need to run this script but over a much larger period of time. I have tried adding a "between X and X" to my select statements, but all I have been seeing is multiple records for a single day. In the instances where it seems like its working it looks like it is adding consecutive days per record (first record says pump ran for 24 hours, next 48, next 72, etc).

Any help would be appreciated. Thanks.

1 Like

Use should use the lag() database function for this kind of operation. Something like this:

SELECT
    ki_701r_pmp - lag(ki_701r_pmp) OVER w AS x701r_runtime,
    ki_nwf1_pmp - lag(ki_nwf1_pmp) OVER w AS nwf1_runtime,
    ki_nwf2_pmp - lag(ki_nwf2_pmp) OVER w AS nwf2_runtime,
    ki_nwf3_pmp - lag(ki_nwf3_pmp) OVER w AS nwf3_runtime,
    ki_nwf4_pmp - lag(ki_nwf4_pmp) OVER w AS nwf4_runtime,
    ki_nwf5_pmp - lag(ki_nwf5_pmp) OVER w AS nwf5_runtime,
    ki_pderbacr_pmp_1 - lag(ki_pderbacr_pmp_1) OVER w AS pderbacr_1_runtime,
    ki_sa2_pmp_1 - lag(ki_sa2_pmp_1) OVER w AS sa2_pmp_1_runtime,
    ki_sa2_pmp_2 - lag(ki_sa2_pmp_2) OVER w AS sa2_pmp_2_runtime,
    ki_sa2_pmp_3 - lag(ki_sa2_pmp_3) OVER w AS sa2_pmp_3_runtime,
    ki_sa2_pmp_4 - lag(ki_sa2_pmp_4) OVER w AS sa2_pmp_4_runtime,
    ki_sa_pmp_1 - lag(ki_sa_pmp_1) OVER w AS sa_pmp_1_runtime,
    ki_sa_pmp_2 - lag(ki_sa_pmp_2) OVER w AS sa_pmp_2_runtime,
    ki_sa_pmp_3 - lag(ki_sa_pmp_3) OVER w AS sa_pmp_3_runtime,
    ki_sa_pmp_4 - lag(ki_sa_pmp_4) OVER w AS sa_pmp_4_runtime,
    ki_sa_pmp_5 - lag(ki_sa_pmp_5) OVER w AS sa_pmp_5_runtime,
    ki_sa_pmp_6 - lag(ki_sa_pmp_6) OVER w AS sa_pmp_6_runtime,
    ki_sa_pmp_7 - lag(ki_sa_pmp_7) OVER w AS sa_pmp_7_runtime,
    ki_sb1_pmp_1 - lag(ki_sb1_pmp_1) OVER w AS sb1_pmp_1_runtime,
    ki_sb1_pmp_2 - lag(ki_sb1_pmp_2) OVER w AS sb1_pmp_2_runtime,
    ki_sb1_pmp_3 - lag(ki_sb1_pmp_3) OVER w AS sb1_pmp_3_runtime,
    ki_sb1_pmp_4 - lag(ki_sb1_pmp_4) OVER w AS sb1_pmp_4_runtime,
    ki_sb1_pmp_5 - lag(ki_sb1_pmp_5) OVER w AS sb1_pmp_5_runtime,
    ki_sb2_pmp_1 - lag(ki_sb2_pmp_1) OVER w AS sb2_pmp_1_runtime,
    ki_sb2_pmp_2 - lag(ki_sb2_pmp_2) OVER w AS sb2_pmp_2_runtime,
    ki_sb2_pmp_3 - lag(ki_sb2_pmp_3) OVER w AS sb2_pmp_3_runtime,
    ki_sb2_pmp_4 - lag(ki_sb2_pmp_4) OVER w AS sb2_pmp_4_runtime,
    ki_sb2_pmp_5 - lag(ki_sb2_pmp_5) OVER w AS sb2_pmp_5_runtime,
    ki_sb2_pmp_6 - lag(ki_sb2_pmp_6) OVER w AS sb2_pmp_6_runtime,
    ki_sb3_pmp_1 - lag(ki_sb3_pmp_1) OVER w AS sb3_pmp_1_runtime,
    ki_sb3_pmp_2 - lag(ki_sb3_pmp_2) OVER w AS sb3_pmp_2_runtime,
    ki_sb3_pmp_3 - lag(ki_sb3_pmp_3) OVER w AS sb3_pmp_3_runtime,
    ki_sb3_pmp_4 - lag(ki_sb3_pmp_4) OVER w AS sb3_pmp_4_runtime,
    ki_sb3_pmp_5 - lag(ki_sb3_pmp_5) OVER w AS sb3_pmp_5_runtime,
    ki_sc_pmp_1 - lag(ki_sc_pmp_1) OVER w AS sc_1_pmp_runtime,
    ki_sc_pmp_2 - lag(ki_sc_pmp_2) OVER w AS sc_2_pmp_runtime,
    ki_sc_pmp_3 - lag(ki_sc_pmp_3) OVER w AS sc_3_pmp_runtime,
    ki_sc_pmp_4 - lag(ki_sc_pmp_4) OVER w AS sc_4_pmp_runtime,
    ki_sc_pmp_5 - lag(ki_sc_pmp_5) OVER w AS sc_5_pmp_runtime,
    ki_sc_pmp_6 - lag(ki_sc_pmp_6) OVER w AS sc_6_pmp_runtime,
    ki_sc_pmp_8 - lag(ki_sc_pmp_8) OVER w AS sc_8_pmp_runtime,
    ki_sc_pmp_9 - lag(ki_sc_pmp_9) OVER w AS sc_9_pmp_runtime,
    ki_sc_pmp_10 - lag(ki_sc_pmp_10) OVER w AS sc_10_pmp_runtime,
    ki_sd_pmp_1 - lag(ki_sd_pmp_1) OVER w AS sd_pmp_1_runtime,
    ki_sd_pmp_2 - lag(ki_sd_pmp_2) OVER w AS sd_pmp_2_runtime,
    ki_sd_pmp_3 - lag(ki_sd_pmp_3) OVER w AS sd_pmp_3_runtime,
    ki_se_pmp_1 - lag(ki_se_pmp_1) OVER w AS se_pmp_1_runtime,
    ki_se_pmp_2 - lag(ki_se_pmp_2) OVER w AS se_pmp_2_runtime,
    ki_sf_pmp_1 - lag(ki_sf_pmp_1) OVER w AS sf_pmp_1_runtime,
    ki_sf_pmp_2 - lag(ki_sf_pmp_2) OVER w AS sf_pmp_2_runtime,
    ki_sf_pmp_3 - lag(ki_sf_pmp_3) OVER w AS sf_pmp_3_runtime,
    ki_sg2_pmp_1 - lag(ki_sg2_pmp_1) OVER w AS sg2_pmp_1_runtime,
    ki_sg2_pmp_2 - lag(ki_sg2_pmp_2) OVER w AS sg2_pmp_2_runtime,
    ki_sg2_pmp_3 - lag(ki_sg2_pmp_3) OVER w AS sg2_pmp_3_runtime,
    ki_sg2_pmp_4 - lag(ki_sg2_pmp_4) OVER w AS sg2_pmp_4_runtime,
    ki_sg_pmp_1 - lag(ki_sg_pmp_1) OVER w AS sg_pmp_1_runtime,
    ki_sg_pmp_2 - lag(ki_sg_pmp_2) OVER w AS sg_pmp_2_runtime,
    ki_sg_pmp_3 - lag(ki_sg_pmp_3) OVER w AS sg_pmp_3_runtime,
    ki_sg_pmp_4 - lag(ki_sg_pmp_4) OVER w AS sg_pmp_4_runtime,
    ki_sg_pmp_5 - lag(ki_sg_pmp_5) OVER w AS sg_pmp_5_runtime,
    ki_sh_pmp_1 - lag(ki_sh_pmp_1) OVER w AS sh_pmp_1_runtime,
    ki_sh_pmp_2 - lag(ki_sh_pmp_2) OVER w AS sh_pmp_2_runtime,
    ki_sj_pmp_1 - lag(ki_sj_pmp_1) OVER w AS sj_pmp_1_runtime,
    ki_sj_pmp_2 - lag(ki_sj_pmp_2) OVER w AS sj_pmp_2_runtime,
    ki_sj_pmp_3 - lag(ki_sj_pmp_3) OVER w AS sj_pmp_3_runtime,
    ki_sj_pmp_4 - lag(ki_sj_pmp_4) OVER w AS sj_pmp_4_runtime,
    ki_sk_pmp_1 - lag(ki_sk_pmp_1) OVER w AS sk_pmp_1_runtime,
    ki_sk_pmp_2 - lag(ki_sk_pmp_2) OVER w AS sk_pmp_2_runtime,
    ki_slp_pmp_1 - lag(ki_slp_pmp_1) OVER w AS slp_pmp_1_runtime,
    ki_slp_pmp_2 - lag(ki_slp_pmp_2) OVER w AS slp_pmp_2_runtime,
    ki_slp_pmp_3 - lag(ki_slp_pmp_3) OVER w AS slp_pmp_3_runtime,
    ki_slp_pmp_4 - lag(ki_slp_pmp_4) OVER w AS slp_pmp_4_runtime,
    ki_slp_pmp_5 - lag(ki_slp_pmp_5) OVER w AS slp_pmp_5_runtime,
    ki_sm_pmp_1 - lag(ki_sm_pmp_1) OVER w AS sm_pmp_1_runtime,
    ki_sm_pmp_2 - lag(ki_sm_pmp_2) OVER w AS sm_pmp_2_runtime,
    ki_sm_pmp_3 - lag(ki_sm_pmp_3) OVER w AS sm_pmp_3_runtime,
    ki_w201_pmp - lag(ki_w201_pmp) OVER w AS w201_runtime,
    ki_w202_pmp - lag(ki_w202_pmp) OVER w AS w202_runtime,
    ki_w601_pmp - lag(ki_w601_pmp) OVER w AS w601_runtime,
    ki_w602_pmp - lag(ki_w602_pmp) OVER w AS w602_runtime,
    ki_w603_pmp - lag(ki_w603_pmp) OVER w AS w603_runtime,
    ki_w604_pmp - lag(ki_w604_pmp) OVER w AS w604_runtime,
    ki_w701_pmp - lag(ki_w701_pmp) OVER w AS w701_runtime,
    ki_w702_pmp - lag(ki_w702_pmp) OVER w AS w702_runtime

FROM
    pump_runtimes_results_new
WINDOW w AS (ORDER BY t_stamp)
ORDER BY t_stamp DESC;

The lag() function is available in MariaDB v10.2 and later and MS SQL Server since 2012. Oracle and PostgreSQL have had this for many years (I switched from Oracle to Postgres back in 2004 because it became available).

3 Likes

I don’t think the version of MySQL I am running supports the WINDOW function (5.6.21), as this throws a syntax error. I’ll look into what version does support this function and the possibility of updating. Thanks for the info!

I realize I never stated in my original post that I was using MySQL…sorry bout that.

MariaDB is the advanced, backward compatible replacement for MySQL. Just drop it in.

2 Likes

Can you export and upload your tables as CSV?. Exporting the tables with sample data as sql file would be a lot better. I can easily import and analyze with my tools.

That’s actually a pretty good idea, as I only need to collect this much data one time! Thanks!

When i tried to answer your requirement, i found many SI's (including myself) are struggling with SQL queries and reports ever since. So i decided to open up a new topic which can help all of us to share our ideas, tools, tips and tricks in one place. Thanks :slight_smile:

1 Like

Nah. Feed Excel several million rows and see what happens. I don't know of any shortcuts to learning SQL. I would suggest interested parties read their favorite DB's technical manuals cover-to-cover.
I find PostgreSQL's documentation to be particularly thorough, and since it's free, easy to experiment with.

2 Likes

The purpose of a db query is to dig deeper and visualize the hidden information. The most powerful tool for data visualization is Pivot. Something which can not be visualized by pivoting a table, can not be visualized by a human sql coder. The best part of Excel is, it's user friendly and generates the sql code too. Do you know any other tool better than Excel?.

BTW, we don't need millions of rows to build a query. If it can work on few thousand rows, definitely we can make it work on millions of rows too. As the saying goes "proof of the pudding is in the eating", why can't we test it?. Provide me any number of tables with sample data as sql file which i can import into mysql, and throw a challenge which you think Excel can NOT do. Let me try.

This is already impossible with Excel (ignoring performance). Just check the specs

And yes, tables with million rows do happen in real life. I just checked out the historian of a random installation, and found over 4 million tags for this month alone.

3 Likes

And we have a winner! ( @Sanderd17 ) Excel is utterly unsuited to large, fully automated tasks.

It was true few years back. Today, excellent tutorials, sample codes, tips and tricks are freely available. If we are willing to pay, very good visual tools which can auto generate queries are available for a reasonable price. Again, one has to put some efforts to fine tune the code and get the job done.

I agree that Excel has a limit like any other tool and can't be used in every situation. But it comes handy for most. I am sure, NASA and CERN scientists are NOT using Excel to go through trillions and trillions of records to explore water on Mars or to determine what happened immediately after big bang !!!. cheers :slight_smile: