# DB Query adjustments **URL:** \ **Category:** General Discussion\ **Tags:** database\ **Created:** [February 22, 2018, 5:19pm UTC](https://forum.inductiveautomation.com/t/db-query-adjustments/17096 "2018-02-22T17:19:05Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![pjgonzales](https://avatars.discourse-cdn.com/v4/letter/p/59ef9b/32.png) [@pjgonzales](https://forum.inductiveautomation.com/u/pjgonzales)\ **Post date:** [February 22, 2018, 5:19pm UTC](https://forum.inductiveautomation.com/t/db-query-adjustments/17096/1 "2018-02-22T17:19:06Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 22, 2018, 7:05pm UTC](https://forum.inductiveautomation.com/t/db-query-adjustments/17096/2 "2018-02-22T19:05:53Z")
Use should use the lag() database function for this kind of operation. Something like this: ```auto 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). ---
**Author:** ![pjgonzales](https://avatars.discourse-cdn.com/v4/letter/p/59ef9b/32.png) [@pjgonzales](https://forum.inductiveautomation.com/u/pjgonzales)\ **Post date:** [February 22, 2018, 9:23pm UTC](https://forum.inductiveautomation.com/t/db-query-adjustments/17096/3 "2018-02-22T21:23:23Z")
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! ---
**Author:** ![pjgonzales](https://avatars.discourse-cdn.com/v4/letter/p/59ef9b/32.png) [@pjgonzales](https://forum.inductiveautomation.com/u/pjgonzales)\ **Post date:** [February 22, 2018, 9:39pm UTC](https://forum.inductiveautomation.com/t/db-query-adjustments/17096/4 "2018-02-22T21:39:52Z")
I realize I never stated in my original post that I was using MySQL…sorry bout that. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 22, 2018, 9:55pm UTC](https://forum.inductiveautomation.com/t/db-query-adjustments/17096/5 "2018-02-22T21:55:22Z")
MariaDB is the advanced, backward compatible replacement for MySQL. Just drop it in. ---
**Author:** ![R.Alamsha](https://avatars.discourse-cdn.com/v4/letter/r/e5b9ba/32.png) [@R.Alamsha](https://forum.inductiveautomation.com/u/R.Alamsha)\ **Post date:** [February 23, 2018, 8:06am UTC](https://forum.inductiveautomation.com/t/db-query-adjustments/17096/6 "2018-02-23T08:06:03Z")
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. ---
**Author:** ![pjgonzales](https://avatars.discourse-cdn.com/v4/letter/p/59ef9b/32.png) [@pjgonzales](https://forum.inductiveautomation.com/u/pjgonzales)\ **Post date:** [February 23, 2018, 4:10pm UTC](https://forum.inductiveautomation.com/t/db-query-adjustments/17096/7 "2018-02-23T16:10:09Z")
That’s actually a pretty good idea, as I only need to collect this much data one time! Thanks! ---
**Author:** ![R.Alamsha](https://avatars.discourse-cdn.com/v4/letter/r/e5b9ba/32.png) [@R.Alamsha](https://forum.inductiveautomation.com/u/R.Alamsha)\ **Post date:** [February 24, 2018, 6:30am UTC](https://forum.inductiveautomation.com/t/db-query-adjustments/17096/8 "2018-02-24T06:30:05Z")
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 🙂 > [@Mastering SQL+Data analytics with Excel](http://forum.inductiveautomation.com/t/mastering-sql-data-analytics-with-excel/17121): > > Few weeks back, a SAP specialist and a professional data scientist friend of mine commented: “All the BIG Data in SAP has no value without Excel. Nothing can beat Excel when it comes to data analytics”. This triggered my curiosity to dig deeper and i started playing around with Pivot Tables. I was astonished to realize, how much was i struggling all these days to build SQL queries and generate reports by ignoring this powerful tool. Yes!!. Excel can generate complex SQL queries which you can jus… ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 25, 2018, 2:25am UTC](https://forum.inductiveautomation.com/t/db-query-adjustments/17096/9 "2018-02-25T02:25:37Z")
> [@R.Alamsha](#): > > Nothing can beat Excel when it comes to data analytics 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. ---
**Author:** ![R.Alamsha](https://avatars.discourse-cdn.com/v4/letter/r/e5b9ba/32.png) [@R.Alamsha](https://forum.inductiveautomation.com/u/R.Alamsha)\ **Post date:** [February 25, 2018, 4:33am UTC](https://forum.inductiveautomation.com/t/db-query-adjustments/17096/10 "2018-02-25T04:33:02Z")
> [@pturmel](#): > > Feed Excel several million rows and see what happens. I don't know of any shortcuts to learning SQL. 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. ---
**Author:** ![Sanderd17](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sanderd17/32/8735_2.png) [@Sanderd17](https://forum.inductiveautomation.com/u/Sanderd17)\ **Post date:** [February 26, 2018, 11:31am UTC](https://forum.inductiveautomation.com/t/db-query-adjustments/17096/11 "2018-02-26T11:31:41Z")
> [@pturmel](#): > > Feed Excel several million rows This is already impossible with Excel (ignoring performance). Just check the specs > **[Excel specifications and limits - Microsoft Support](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3?ui=en-us&rs=en-us&ad=us)** > > In Excel 2010, the maximum worksheet size is 1,048,576 rows by 16,384 columns. In this article, find all workbook, worksheet, and feature specifications and limits. 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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 26, 2018, 1:51pm UTC](https://forum.inductiveautomation.com/t/db-query-adjustments/17096/12 "2018-02-26T13:51:02Z")
And we have a winner! ( @Sanderd17 ) Excel is utterly unsuited to large, fully automated tasks. ---
**Author:** ![R.Alamsha](https://avatars.discourse-cdn.com/v4/letter/r/e5b9ba/32.png) [@R.Alamsha](https://forum.inductiveautomation.com/u/R.Alamsha)\ **Post date:** [February 27, 2018, 3:40am UTC](https://forum.inductiveautomation.com/t/db-query-adjustments/17096/13 "2018-02-27T03:40:32Z")
> [@pturmel](#): > > I don't know of any shortcuts to learning SQL. 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. > [@pturmel](#): > > Excel is utterly unsuited to large, fully automated tasks. 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 🙂