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.
