Sign in with
By continuing you indicate that you have read and agree to our Terms of service and Privacy policy
Sign in with
By continuing you indicate that you have read and agree to our Terms of service and Privacy policy
Explore all Education related open source software, libraries, packages, source code, cloud functions and APIs.
The Education Industry comprises establishments whose primary objective is to provide education. These establishments can be public, non-profit, or for-profit institutions. They include elementary schools, secondary schools, online learning, community colleges, universities, and ministries or departments of education.
These software components cover functions across Academia, e learning, Edutech, Hackathon, LMS, Research areas.
The Education Industry comprises establishments whose primary objective is to provide education. These establishments can be public, non-profit, or for-profit institutions. They include elementary schools, secondary schools, online learning, community colleges, universities, and ministries or departments of education.
These software components cover functions across Academia, e learning, Edutech, Hackathon, LMS, Research areas.
Popular New Releases in Education
three.js | r139 |
keras | Keras Release 2.9.0 RC2 |
scikit-learn | scikit-learn 1.0.2 |
faceswap | Faceswap Windows and Linux Installers v2.0.0 |
phaser | Phaser v3.55.2 |
three.js r139 |
keras Keras Release 2.9.0 RC2 |
scikit-learn scikit-learn 1.0.2 |
faceswap Faceswap Windows and Linux Installers v2.0.0 |
phaser Phaser v3.55.2 |
Popular Libraries in Education
freeCodeCamp
by freeCodeCamp javascript
344419 BSD-3-Clause
freeCodeCamp.org's open-source codebase and curriculum. Learn to code for free.
system-design-primer
by donnemartin python
143449 NOASSERTION
Learn how to design large-scale systems. Prep for the system design interview. Includes Anki flashcards.
three.js
by mrdoob javascript
80965 MIT
JavaScript 3D Library.
keras
by keras-team python
55007 Apache-2.0
Deep Learning for humans
awesome-machine-learning
by josephmisiti python
51223 NOASSERTION
A curated list of awesome Machine Learning frameworks, libraries and software.
scikit-learn
by scikit-learn python
49728 BSD-3-Clause
scikit-learn: machine learning in Python
Web-Dev-For-Beginners
by microsoft javascript
44908 MIT
24 Lessons, 12 Weeks, Get Started as a Web Developer
faceswap
by deepfakes python
38275 GPL-3.0
Deepfakes Software For All
Deep-Learning-Papers-Reading-Roadmap
by floodsung python
30347
Deep Learning papers reading roadmap for anyone who are eager to learn this amazing tech!
freeCodeCamp
by freeCodeCamp javascript
344419 BSD-3-Clause
freeCodeCamp.org's open-source codebase and curriculum. Learn to code for free.
system-design-primer
by donnemartin python
143449 NOASSERTION
Learn how to design large-scale systems. Prep for the system design interview. Includes Anki flashcards.
three.js
by mrdoob javascript
80965 MIT
JavaScript 3D Library.
keras
by keras-team python
55007 Apache-2.0
Deep Learning for humans
awesome-machine-learning
by josephmisiti python
51223 NOASSERTION
A curated list of awesome Machine Learning frameworks, libraries and software.
scikit-learn
by scikit-learn python
49728 BSD-3-Clause
scikit-learn: machine learning in Python
Web-Dev-For-Beginners
by microsoft javascript
44908 MIT
24 Lessons, 12 Weeks, Get Started as a Web Developer
faceswap
by deepfakes python
38275 GPL-3.0
Deepfakes Software For All
Deep-Learning-Papers-Reading-Roadmap
by floodsung python
30347
Deep Learning papers reading roadmap for anyone who are eager to learn this amazing tech!
Trending New libraries in Education
Web-Dev-For-Beginners
by microsoft javascript
44908 MIT
24 Lessons, 12 Weeks, Get Started as a Web Developer
AI-Expert-Roadmap
by AMAI-GmbH javascript
13925 MIT
Roadmap to becoming an Artificial Intelligence Expert in 2021
30-Days-Of-React
by Asabeneh javascript
8139
30 Days of React challenge is a step by step guide to learn React in 30 days. It requires HTML, CSS, and JavaScript knowledge. You should be comfortable with JavaScript before you start to React. If you are not comfortable with JavaScript check out 30DaysOfJavaScript. This is a continuation of 30 Days Of JS. This challenge may take more than 100 days, follow your own pace.
DeepSpeed
by microsoft python
6633 MIT
DeepSpeed is a deep learning optimization library that makes distributed training and inference easy, efficient, and effective.
ultimate-python
by huangsam python
2986 MIT
Ultimate Python study guide for newcomers and professionals alike. :snake: :snake: :snake:
igel
by nidhaloff python
2921 MIT
a delightful machine learning tool that allows you to train, test, and use models without writing code
stylegan2-pytorch
by lucidrains python
2738 MIT
Simplest working implementation of Stylegan2, state of the art generative adversarial network, in Pytorch. Enabling everyone to experience disentanglement
gpt3-sandbox
by shreyashankar javascript
2398 MIT
The goal of this project is to enable users to create cool web demos using the newly released OpenAI GPT-3 API with just a few lines of Python.
TensorFlowTTS
by TensorSpeech python
2140 Apache-2.0
:stuck_out_tongue_closed_eyes: TensorFlowTTS: Real-Time State-of-the-art Speech Synthesis for Tensorflow 2 (supported including English, French, Korean, Chinese, German and Easy to adapt for other languages)
Web-Dev-For-Beginners
by microsoft javascript
44908 MIT
24 Lessons, 12 Weeks, Get Started as a Web Developer
AI-Expert-Roadmap
by AMAI-GmbH javascript
13925 MIT
Roadmap to becoming an Artificial Intelligence Expert in 2021
30-Days-Of-React
by Asabeneh javascript
8139
30 Days of React challenge is a step by step guide to learn React in 30 days. It requires HTML, CSS, and JavaScript knowledge. You should be comfortable with JavaScript before you start to React. If you are not comfortable with JavaScript check out 30DaysOfJavaScript. This is a continuation of 30 Days Of JS. This challenge may take more than 100 days, follow your own pace.
DeepSpeed
by microsoft python
6633 MIT
DeepSpeed is a deep learning optimization library that makes distributed training and inference easy, efficient, and effective.
ultimate-python
by huangsam python
2986 MIT
Ultimate Python study guide for newcomers and professionals alike. :snake: :snake: :snake:
igel
by nidhaloff python
2921 MIT
a delightful machine learning tool that allows you to train, test, and use models without writing code
stylegan2-pytorch
by lucidrains python
2738 MIT
Simplest working implementation of Stylegan2, state of the art generative adversarial network, in Pytorch. Enabling everyone to experience disentanglement
gpt3-sandbox
by shreyashankar javascript
2398 MIT
The goal of this project is to enable users to create cool web demos using the newly released OpenAI GPT-3 API with just a few lines of Python.
TensorFlowTTS
by TensorSpeech python
2140 Apache-2.0
:stuck_out_tongue_closed_eyes: TensorFlowTTS: Real-Time State-of-the-art Speech Synthesis for Tensorflow 2 (supported including English, French, Korean, Chinese, German and Easy to adapt for other languages)
Top Authors in Education
1
PacktPublishing
128 Libraries
4389
2
microsoft
46 Libraries
68102
3
IBM
19 Libraries
377
4
facebookresearch
13 Libraries
34537
5
rimorsoft
11 Libraries
177
6
moodleou
11 Libraries
135
7
mozilla
10 Libraries
1971
8
aws-samples
10 Libraries
3506
9
UKPLab
10 Libraries
126
10
Azure-Samples
10 Libraries
131
Trending Kits in Education
It has been 10 years since the first blog post by Eben Upton announcing the Raspberry Pi. After 6 families of Raspberry Pi releases and over forty million boards sold, the Raspberry Pi has become a fan favorite. While the initial intent of the Raspberry Pi project was teaching introductory computer science in schools, especially in developing countries, it has found massive success in the hobbyist market. Hobbyist Projects Refer below libraries for interesting projects across use cases in health care, morse code, vision, servo motors, bitcoin, gaming, music, and others. Home Automation Projects Use these libraries for projects ranging from a secure offline home automation framework to interesting projects like magic mirror, bathroom occupancy notifier to more serious pursuits like gas sensors. IoT Libraries for Raspberry Pi From learning IoT to implementing the full stack, these libraries also provide use cases to connect with AWS and Azure. Operating Systems for Raspberry Pi From base Linux to lightweight and hardened versions, there are multiple OS choices to experiment with your Raspberry Pi project. Utilities for Raspberry Pi Leverage these popular utilities in your Raspberry Pi projects.
The Raspberry Pi is an economical computer that runs Linux and provides GPIO (general purpose input/output) pins, allowing control of components for physical computing and the Internet of Things (IoT). Developers use the Raspberry Pi to learn to program, build hardware projects, do home automation, implement Kubernetes clusters and Edge computing, and even use them in industrial applications.
The Raspberry Pi Foundation works to put the power of computing and digital making into the hands of people all over the world. Code Club and CoderDojo are part of the Raspberry Pi Foundation. Raspberry Jams are Raspberry Pi focused events for people of all ages to learn about Raspberry Pi and share ideas and projects.
kandi collections on 10 Years of Raspberry Pi, showcases the most popular libraries across hobbyist uses cases, home automation, IoT, OS and utilities for Raspberry Pi. Hobbyist usecases span across health care, morse code, vision, servo motors, bitcoin, gaming, music, and many others demonstrating the versatility of the humble Raspberry Pi.
Describe your solution here.
e.g. Our solution is a Mental Health Monitor and Virtual Companion for students in the digital classroom. It acts as a virtual friend to the student and keeps her company during online classes. It also reminds her of class schedules, submissions, and other aspects of the classroom. It monitors for mental wellbeing vitals like class participation, connecting with friends, and sends motivational messages. In future versions, we can add connect with parents, teachers, and other stakeholders to address the well-being and academic excellence in a holistic manner.
We have used the below techniques for our solution.
1. Machine learning to train for the virtual conversations for the virtual companion.
2. Data exploration to explore the academic and digital activity data to use in training for predicting behavior patterns.
Machine Learning You may be using multiple libraries for different functions in your solution.
Please create one group for each function and add the libraries to each group.
e.g. I have created a group for Machine Learning which has the libraries used in my solution.
The below libraries helps in capturing the embeddings for the text. The embeddings are vectoral representation of text with their semantics. Data Exploration You may be using multiple libraries for different functions in your solution.
Please create one group for each function and add the libraries to each group.
e.g. I have created a group for the libraries used for Data Exploration in my solution.
The data exploration helps in doing extensive analysis of different data types and in assisting to understand the patterns. Pandas is used in our solution for data manipulation and analysis.
Challenge Statement 1
The Pandemic has impacted education - classes have moved online, students have been isolated on screens and coping with this change. Despite the challenges, the digital school has the potential to transform education. How can we empower students and teachers in this new digital school paradigm? In this challenge, we are inviting AI-powered solutions for the digital school of tomorrow
Current State
According to UNESCO, by the end of March 2020 over 1.5 billion pupils or 87 per cent of the world’s student population across 165 countries had been affected by school closures caused by COVID-19.1 In Australia, K-12 schools have experienced interruptions in every state and territory, although the extent and period of closures have varied significantly across jurisdictions. In early May, only 3 percent of children in Victorian government schools were in attendance, whereas the Northern Territory had returned to normal levels of 79 per cent. As school moves online, students, teachers and parents are faced with numerous challenges:
• reduced one-to-one engagement with teachers
• difficulty in ascertaining engagement levels of students
• restricted ability to monitor individual student progress
• increased level of oversight required from parents and carers (particularly for younger children)
• increased social isolation and reduced ability to support student wellbeing
• interruption to learning support for those children with additional needs
• differential levels of access to technology, including internet and devices, to support learning.
Solution Overview
1. Value Proposition
IBUDDY is a virtual study buddy to make online learning more engaging, enjoyable and effective for kids
- Utilise AI technology to monitor the attention level of children during class and provide timely reminder
- Monitor student’s interest and engagement
- Provide virtual companion and a sense of social interaction
2. Competitive Landscape
This is grounded in evidence that getting students DOING within the learning experience is a factor in student success (Kilgo, Ezell Sheets, & Pascarella, 2015). However, there is no existing tools or program to support both students and teachers in the online learning process. Even though Zoom and Microsoft Team have provided some interactive tools like Breakout rooms, Polls and Whiteboard sharing, these tools show minimal impacts on young students, the one who suffer most with digital learning.
Considering that online learning will still be a potential learning method and environment after the pandemic, IBuddy expects to provide long lasting values to both learners and teachers. The program also poses a great first-mover advantage
Kilgo, C. A., Ezell Sheets, J. K., & Pascarella, E. T. (2015). The link between high-impact practices and student learning: some longitudinal evidence. Higher Education, 69(4), 509-525. doi:10.1007/s10734-014-9788-z
UNESCO, UNESCO rallies international organizations, civ Eye-contact-cnn Zoom-interaction
It has been 10 years since the first blog post by Eben Upton announcing the Raspberry Pi. After 6 families of Raspberry Pi releases and over forty million boards sold, the Raspberry Pi has become a fan favorite. While the initial intent of the Raspberry Pi project was teaching introductory computer science in schools, especially in developing countries, it has found massive success in the hobbyist market. Hobbyist Projects Refer below libraries for interesting projects across use cases in health care, morse code, vision, servo motors, bitcoin, gaming, music, and others. Home Automation Projects Use these libraries for projects ranging from a secure offline home automation framework to interesting projects like magic mirror, bathroom occupancy notifier to more serious pursuits like gas sensors. IoT Libraries for Raspberry Pi From learning IoT to implementing the full stack, these libraries also provide use cases to connect with AWS and Azure. Operating Systems for Raspberry Pi From base Linux to lightweight and hardened versions, there are multiple OS choices to experiment with your Raspberry Pi project. Utilities for Raspberry Pi Leverage these popular utilities in your Raspberry Pi projects.
The Raspberry Pi is an economical computer that runs Linux and provides GPIO (general purpose input/output) pins, allowing control of components for physical computing and the Internet of Things (IoT). Developers use the Raspberry Pi to learn to program, build hardware projects, do home automation, implement Kubernetes clusters and Edge computing, and even use them in industrial applications.
The Raspberry Pi Foundation works to put the power of computing and digital making into the hands of people all over the world. Code Club and CoderDojo are part of the Raspberry Pi Foundation. Raspberry Jams are Raspberry Pi focused events for people of all ages to learn about Raspberry Pi and share ideas and projects.
kandi collections on 10 Years of Raspberry Pi, showcases the most popular libraries across hobbyist uses cases, home automation, IoT, OS and utilities for Raspberry Pi. Hobbyist usecases span across health care, morse code, vision, servo motors, bitcoin, gaming, music, and many others demonstrating the versatility of the humble Raspberry Pi.
Describe your solution here.
e.g. Our solution is a Mental Health Monitor and Virtual Companion for students in the digital classroom. It acts as a virtual friend to the student and keeps her company during online classes. It also reminds her of class schedules, submissions, and other aspects of the classroom. It monitors for mental wellbeing vitals like class participation, connecting with friends, and sends motivational messages. In future versions, we can add connect with parents, teachers, and other stakeholders to address the well-being and academic excellence in a holistic manner.
We have used the below techniques for our solution.
1. Machine learning to train for the virtual conversations for the virtual companion.
2. Data exploration to explore the academic and digital activity data to use in training for predicting behavior patterns.
Machine Learning You may be using multiple libraries for different functions in your solution.
Please create one group for each function and add the libraries to each group.
e.g. I have created a group for Machine Learning which has the libraries used in my solution.
The below libraries helps in capturing the embeddings for the text. The embeddings are vectoral representation of text with their semantics. Data Exploration You may be using multiple libraries for different functions in your solution.
Please create one group for each function and add the libraries to each group.
e.g. I have created a group for the libraries used for Data Exploration in my solution.
The data exploration helps in doing extensive analysis of different data types and in assisting to understand the patterns. Pandas is used in our solution for data manipulation and analysis.
Challenge Statement 1
The Pandemic has impacted education - classes have moved online, students have been isolated on screens and coping with this change. Despite the challenges, the digital school has the potential to transform education. How can we empower students and teachers in this new digital school paradigm? In this challenge, we are inviting AI-powered solutions for the digital school of tomorrow
Current State
According to UNESCO, by the end of March 2020 over 1.5 billion pupils or 87 per cent of the world’s student population across 165 countries had been affected by school closures caused by COVID-19.1 In Australia, K-12 schools have experienced interruptions in every state and territory, although the extent and period of closures have varied significantly across jurisdictions. In early May, only 3 percent of children in Victorian government schools were in attendance, whereas the Northern Territory had returned to normal levels of 79 per cent. As school moves online, students, teachers and parents are faced with numerous challenges:
• reduced one-to-one engagement with teachers
• difficulty in ascertaining engagement levels of students
• restricted ability to monitor individual student progress
• increased level of oversight required from parents and carers (particularly for younger children)
• increased social isolation and reduced ability to support student wellbeing
• interruption to learning support for those children with additional needs
• differential levels of access to technology, including internet and devices, to support learning.
Solution Overview
1. Value Proposition
IBUDDY is a virtual study buddy to make online learning more engaging, enjoyable and effective for kids
- Utilise AI technology to monitor the attention level of children during class and provide timely reminder
- Monitor student’s interest and engagement
- Provide virtual companion and a sense of social interaction
2. Competitive Landscape
This is grounded in evidence that getting students DOING within the learning experience is a factor in student success (Kilgo, Ezell Sheets, & Pascarella, 2015). However, there is no existing tools or program to support both students and teachers in the online learning process. Even though Zoom and Microsoft Team have provided some interactive tools like Breakout rooms, Polls and Whiteboard sharing, these tools show minimal impacts on young students, the one who suffer most with digital learning.
Considering that online learning will still be a potential learning method and environment after the pandemic, IBuddy expects to provide long lasting values to both learners and teachers. The program also poses a great first-mover advantage
Kilgo, C. A., Ezell Sheets, J. K., & Pascarella, E. T. (2015). The link between high-impact practices and student learning: some longitudinal evidence. Higher Education, 69(4), 509-525. doi:10.1007/s10734-014-9788-z
UNESCO, UNESCO rallies international organizations, civ Eye-contact-cnn Zoom-interaction
Trending Discussions on Education
Add commas every digits in numbers of kable()
Canceling a Future vs stopping a Thread
How do you plot smooth components of different GAMs in same panel?
Coefficient plot - Increase gap between rows and alternative background colors in rows
MySQL SQL Performance need some improvement
Grabbing certain data from one object to another object
Manually construct factor from integer vector
react-native-render-html: "You seem to update the X prop of the Y component in short periods of time..."
Reproduce a complex table with double headesrs
Why Effects package in Julia is returning an error following the output of MixedModels?
QUESTION
Add commas every digits in numbers of kable()
Asked 2022-Mar-21 at 16:36I have the dataframe below and I create a kable out of this. How could I add commas between numbers every 3 digits?
1Descs<-structure(list(Mean = c(NaN, 943330388, NaN, NaN, NaN, 543234645,
245831420, NaN, 27301292, 160818771), Median = c(NaN, 943330388,
3NaN, NaN, NaN, 543234645, 45831420, NaN, 27301292, 160818771),
4 SD = c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
5 NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), MAD = c(NA,
6 0, NA, NA, NA, 0, 0, NA, 0, 0), MIN = c(NA, 943330388, NA,
7 NA, NA, 543234645, 45831420, NA, 27301292, 160818771), MAX = c(NA,
8 943330388, NA, NA, NA, 543234645, 45831420, NA, 27301292,
9 160818771), VAR = c(NA_real_, NA_real_, NA_real_, NA_real_,
10 NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_
11 ), RANGE = structure(c(NA, 943330388, NA, NA, NA, 543234645,
12 45831420, NA, 27301292, 160818771, NA, 943330388, NA, NA,
13 NA, 543234645, 45831420, NA, 27301292, 160818771), .Dim = c(10L,
14 2L)), QUANTILES = structure(c(NA, 943330388, NA, NA, NA,
15 543234645, 45831420, NA, 27301292, 160818771, NA, 943330388,
16 NA, NA, NA, 543234645, 45831420, NA, 27301292, 160818771), .Dim = c(10L,
17 2L), .Dimnames = list(NULL, c("25%", "75%")))), row.names = c("Comedy",
18"Education", "Entertainment", "Film & Animation", "Gaming", "Howto & Style",
19"Music", "People & Blogs", "Science & Technology", "Sports"), class = "data.frame")
20
21library(kableExtra)
22
23kable(Descs) %>%
24 kable_styling(
25 font_size = 15,
26 bootstrap_options = c("striped", "hover", "condensed")
27 )
28ANSWER
Answered 2022-Mar-21 at 16:36You could use the kable format argument, this avoids mucking around with the data prior to putting into the table.
And if you want to clear up the NAs and NaNs you could add in this line of code: options(knitr.kable.NA = '')
1Descs<-structure(list(Mean = c(NaN, 943330388, NaN, NaN, NaN, 543234645,
245831420, NaN, 27301292, 160818771), Median = c(NaN, 943330388,
3NaN, NaN, NaN, 543234645, 45831420, NaN, 27301292, 160818771),
4 SD = c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
5 NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), MAD = c(NA,
6 0, NA, NA, NA, 0, 0, NA, 0, 0), MIN = c(NA, 943330388, NA,
7 NA, NA, 543234645, 45831420, NA, 27301292, 160818771), MAX = c(NA,
8 943330388, NA, NA, NA, 543234645, 45831420, NA, 27301292,
9 160818771), VAR = c(NA_real_, NA_real_, NA_real_, NA_real_,
10 NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_
11 ), RANGE = structure(c(NA, 943330388, NA, NA, NA, 543234645,
12 45831420, NA, 27301292, 160818771, NA, 943330388, NA, NA,
13 NA, 543234645, 45831420, NA, 27301292, 160818771), .Dim = c(10L,
14 2L)), QUANTILES = structure(c(NA, 943330388, NA, NA, NA,
15 543234645, 45831420, NA, 27301292, 160818771, NA, 943330388,
16 NA, NA, NA, 543234645, 45831420, NA, 27301292, 160818771), .Dim = c(10L,
17 2L), .Dimnames = list(NULL, c("25%", "75%")))), row.names = c("Comedy",
18"Education", "Entertainment", "Film & Animation", "Gaming", "Howto & Style",
19"Music", "People & Blogs", "Science & Technology", "Sports"), class = "data.frame")
20
21library(kableExtra)
22
23kable(Descs) %>%
24 kable_styling(
25 font_size = 15,
26 bootstrap_options = c("striped", "hover", "condensed")
27 )
28library(kableExtra)
29
30kable(Descs,
31 format.args = list(big.mark = ",")) %>%
32 kable_styling(
33 font_size = 15,
34 bootstrap_options = c("striped", "hover", "condensed")
35 )
36
37
QUESTION
Canceling a Future vs stopping a Thread
Asked 2022-Mar-11 at 22:59In very ancient days, there used to be a way to "stop" or "kill" a thread, though this is has been deprecated due to it allowing system instability. I noticed though, it's possible to "cancel" a running future. The quotes are there because I don't know how the thread is treated at an OS level.
For my education, What's the difference between canceling a Future, effectively canceling the thread, and stopping the thread? Why is cancelling okay, but stopping/killing a thread in the old days bad?
ANSWER
Answered 2022-Mar-11 at 22:59Cancelling tells the Future its result is no longer desired (if it hasn't already completed), and lets it stop cleanly, whereas Thread.stop() kills the underlying native thread and releases all its monitors. The thread doesn't get a chance to wrap up what it was doing or put anything into a known, good state.
The difference is, one sends a message, but lets the recipient respond to the message on its own terms, and the other just forcefully stops it, effectively instantly. (Letting the recipient respond in its own way can even mean that the job will continue to execute, since different implementations of Future may handle cancellation differently, but any result will still be ignored.)
It's like the difference between putting out the red flag in an auto race, telling a car it needs to come in to the pit stop on the next lap, versus suddenly putting a wall in the middle of the racetrack right in front of the car, which it then crashes into.
QUESTION
How do you plot smooth components of different GAMs in same panel?
Asked 2022-Feb-19 at 12:38I have two GAMs which have the same predictor variables but different independent variables. I would like to combine the two GAMs to a set of plots where the smooth component (partial residuals) of each predictor variable are in the same panel (differentiated with e.g. color). Reproducible example:
1# Required packages
2require(mgcv)
3require(mgcViz)
4
5# Dataset
6data("swiss")
7
8# GAM models
9fit1 <- mgcv::gam(Fertility ~ s(Examination) + s(Education), data = swiss)
10fit2 <- mgcv::gam(Agriculture ~ s(Examination) + s(Education), data = swiss)
11
12# Converting GAM objects to a gamViz objects
13viz_fit1 <- mgcViz::getViz(fit1)
14viz_fit2 <- mgcViz::getViz(fit2)
15
16# Make plotGAM objects
17trt_fit1 <- plot(viz_fit1, allTerms = T) + l_fitLine()
18trt_fit2 <- plot(viz_fit2, allTerms = T) + l_fitLine()
19
20# Print plots
21print(trt_fit1, pages = 1)
22print(trt_fit2, pages = 1)
23Plot of fit1 looks like this:
And fit2 like this:
So I would like to combine the two Examinations into one panel, and the two Educations into another one, showing the independent variable (from different GAMs) with different color/linetype.
ANSWER
Answered 2022-Feb-18 at 17:55If you want them in the same plot, you can pull the data from your fit with trt_fit1[["plots"]][[1]]$data$fit and plot them yourself. I looked at the plot style from the mgcViz github. You can add a second axis or scale as necessary.
1# Required packages
2require(mgcv)
3require(mgcViz)
4
5# Dataset
6data("swiss")
7
8# GAM models
9fit1 <- mgcv::gam(Fertility ~ s(Examination) + s(Education), data = swiss)
10fit2 <- mgcv::gam(Agriculture ~ s(Examination) + s(Education), data = swiss)
11
12# Converting GAM objects to a gamViz objects
13viz_fit1 <- mgcViz::getViz(fit1)
14viz_fit2 <- mgcViz::getViz(fit2)
15
16# Make plotGAM objects
17trt_fit1 <- plot(viz_fit1, allTerms = T) + l_fitLine()
18trt_fit2 <- plot(viz_fit2, allTerms = T) + l_fitLine()
19
20# Print plots
21print(trt_fit1, pages = 1)
22print(trt_fit2, pages = 1)
23library(tidyverse)
24exam_dat <-
25 bind_rows(trt_fit1[["plots"]][[1]]$data$fit %>% mutate(fit = "Fit 1"),
26 trt_fit2[["plots"]][[1]]$data$fit %>% mutate(fit = "Fit 2"))
27
28
29ggplot(data = exam_dat, aes(x = x, y = y, colour = fit)) +
30 geom_line() +
31 labs(x = "Examination", y = "s(Examination)") +
32 theme_bw() +
33 theme(panel.grid.major = element_blank(), panel.grid.minor = element_blank())
34
To simply get them on the same panel, you could use gridExtra as fit1 and fit2 have a ggplot object.
1# Required packages
2require(mgcv)
3require(mgcViz)
4
5# Dataset
6data("swiss")
7
8# GAM models
9fit1 <- mgcv::gam(Fertility ~ s(Examination) + s(Education), data = swiss)
10fit2 <- mgcv::gam(Agriculture ~ s(Examination) + s(Education), data = swiss)
11
12# Converting GAM objects to a gamViz objects
13viz_fit1 <- mgcViz::getViz(fit1)
14viz_fit2 <- mgcViz::getViz(fit2)
15
16# Make plotGAM objects
17trt_fit1 <- plot(viz_fit1, allTerms = T) + l_fitLine()
18trt_fit2 <- plot(viz_fit2, allTerms = T) + l_fitLine()
19
20# Print plots
21print(trt_fit1, pages = 1)
22print(trt_fit2, pages = 1)
23library(tidyverse)
24exam_dat <-
25 bind_rows(trt_fit1[["plots"]][[1]]$data$fit %>% mutate(fit = "Fit 1"),
26 trt_fit2[["plots"]][[1]]$data$fit %>% mutate(fit = "Fit 2"))
27
28
29ggplot(data = exam_dat, aes(x = x, y = y, colour = fit)) +
30 geom_line() +
31 labs(x = "Examination", y = "s(Examination)") +
32 theme_bw() +
33 theme(panel.grid.major = element_blank(), panel.grid.minor = element_blank())
34gridExtra::grid.arrange(
35 trt_fit1[["plots"]][[2]]$ggObj,
36 trt_fit2[["plots"]][[2]]$ggObj,
37 nrow = 1)
38
Created on 2022-02-18 by the reprex package (v2.0.1)
QUESTION
Coefficient plot - Increase gap between rows and alternative background colors in rows
Asked 2022-Jan-29 at 17:41I have created this coefficient plot. However, I cannot increase the gap between rows. I also like to add an alternative background colour of row (like row-wise grey then white then grey ) to make it easier for the reader to read the plot. Would you please support improving its visualization?
I used the following code to create this plot.
1mydf <- data.frame(
2 SubgroupH=c('Age',NA,NA,NA,NA,'Marital or Union Status',NA,NA, NA, 'Place of Residence', NA, NA, 'Education', NA, NA, NA, NA,'Occupation', NA, NA, NA, NA, 'Wealth', NA, NA, NA, NA, NA, 'Reading newspaper or magazine', NA, NA, NA, 'Frequency of watching television', NA, NA, NA, 'Frequency of listening radio', NA, NA, NA ),
3 Subgroup=c(NA,'15-19','20-29','30-39','40-49', NA, 'Currently Married or Union', 'Never Married or Union','Formally Married or Union', NA, 'Rural', 'Urban', NA, 'Higher', 'Secondary', 'Primary', 'No eduction', NA, 'Not working', 'Professional/technical/managerial/services', 'Agriculture', 'Skilled/unskilled manual', NA, 'Poorest', 'Poorer', 'Middle','Richer', 'Richest', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week'),
4 AdjustedOR=c(NA,1,'2.76 (2.49-3.07)','3.68 (3.26-4.15)','4.61 (4.06-5.23)',NA,1,'1.03 (0.94-1.13)', '1.26 (1.04-1.54)', NA, 1,'1.12 (1.04-1.21)', NA, 1, '1.42 (1.30-1.56)', '2.09 (1.85-2.36)', '2.20 (1.93-2.49)', NA, 1, '1.39 (1.25-1.54)', '1.37 (1.24-1.51)', '1.55 (1.39-1.72)', NA, 1, '0.91 (0.84-0.99)', '0.77 (0.71-0.84)', '0.72 (0.65-0.79)', '0.61 (0.54-0.69)', NA, 1, '1 (0.91-1.10)', '0.92 (0.85-0.998)', NA, 1, '1.19 (1.07-1.31)', '1.29 (1.19-1.40)', NA, 1, '1.19 (1.09-1.30)', '1.13 (1.05-1.21)'),
5 OddsRatio=c(NA,1,2.76,3.68,4.61, NA,1,1.03, 1.26, NA, 1,1.12, NA, 1, 1.42, 2.09, 2.20, NA, 1, 1.39, 1.37, 1.55, NA, 1, 0.91, 0.77, 0.78, 0.61 , NA, 1, 1,0.92, NA, 1,1.19,1.29, NA, 1, 1.19, 1.13),
6 ORLower=c(NA,NA,2.49,3.26,4.06,NA,NA,0.94, 1.04, NA, NA,1.04, NA, NA, 1.30,1.85, 1.93, NA, NA,1.25, 1.24, 1.39, NA, NA, 0.84, 0.71, 0.65, 0.54, NA, NA, 0.91, 0.85, NA, NA, 1.07, 1.19, NA, NA,1.09, 1.05),
7 ORUpper=c(NA,NA,3.07,4.15,5.23,NA,NA,1.13, 1.54, NA, NA,1.21, NA, NA, 1.56, 2.36, 2.49, NA, NA, 1.54, 1.51,1.72, NA, NA, 0.99, 0.84, 0.79, 0.69, NA, NA, 1.10, 0.998, NA, NA, 1.31, 1.40, NA, NA, 1.30,1.21),
8 Pvalue=c(NA,NA,'< 0.001','< 0.001','< 0.001', NA,NA, 0.518, 0.021, NA, NA, 0.003, NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, 0.029, '< 0.001','< 0.001','< 0.001', NA, NA, 0.993, 0.045, NA, NA, '< 0.001','< 0.001',NA, NA, '< 0.001', 0.002),
9 stringsAsFactors=FALSE )
10
11#png('temp.png', width=8, height=4, units='in', res=400)
12
13rowseq <- seq(nrow(mydf),1)
14par(mai=c(0.7,0,0,0))
15plot(mydf$OddsRatio, rowseq, pch=15,
16 xlim=c(-0.8,6.2), ylim=c(0,42),
17 xlab='', ylab='', yaxt='n', xaxt='n',
18 bty='n')
19axis(1, seq(0.5, 5,by=0.5), cex.axis=1)
20
21segments(1,-1,1,40.20, lty=3, )
22segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
23
24mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=1, font=2)
25
26text(-1,42, "Factors", cex=1.4, font=2, pos=4)
27t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
28text(-1,rowseq, t1h, cex=1.3, pos=4, font=2)
29t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
30text(-0.98,rowseq, t1, cex=1.3, pos=4)
31
32text(4.6,42, "Adjusted Odds Ratio (95% CI)", cex=1.4, font=2, pos=4)
33t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR,big.mark=","), '')
34text(6, rowseq, t2, cex=1.3, pos=2)
35
36text(6,42, "P-value", cex=1.4, font=2, pos=4)
37t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
38text(6,rowseq, t4, cex=1.3, pos=4)
39ANSWER
Answered 2022-Jan-29 at 09:56You could play with flexible and different cex and adjust with the png parameters. This looks already better. For line-by-line gray shading we can simply use abline with modulo 2.
1mydf <- data.frame(
2 SubgroupH=c('Age',NA,NA,NA,NA,'Marital or Union Status',NA,NA, NA, 'Place of Residence', NA, NA, 'Education', NA, NA, NA, NA,'Occupation', NA, NA, NA, NA, 'Wealth', NA, NA, NA, NA, NA, 'Reading newspaper or magazine', NA, NA, NA, 'Frequency of watching television', NA, NA, NA, 'Frequency of listening radio', NA, NA, NA ),
3 Subgroup=c(NA,'15-19','20-29','30-39','40-49', NA, 'Currently Married or Union', 'Never Married or Union','Formally Married or Union', NA, 'Rural', 'Urban', NA, 'Higher', 'Secondary', 'Primary', 'No eduction', NA, 'Not working', 'Professional/technical/managerial/services', 'Agriculture', 'Skilled/unskilled manual', NA, 'Poorest', 'Poorer', 'Middle','Richer', 'Richest', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week'),
4 AdjustedOR=c(NA,1,'2.76 (2.49-3.07)','3.68 (3.26-4.15)','4.61 (4.06-5.23)',NA,1,'1.03 (0.94-1.13)', '1.26 (1.04-1.54)', NA, 1,'1.12 (1.04-1.21)', NA, 1, '1.42 (1.30-1.56)', '2.09 (1.85-2.36)', '2.20 (1.93-2.49)', NA, 1, '1.39 (1.25-1.54)', '1.37 (1.24-1.51)', '1.55 (1.39-1.72)', NA, 1, '0.91 (0.84-0.99)', '0.77 (0.71-0.84)', '0.72 (0.65-0.79)', '0.61 (0.54-0.69)', NA, 1, '1 (0.91-1.10)', '0.92 (0.85-0.998)', NA, 1, '1.19 (1.07-1.31)', '1.29 (1.19-1.40)', NA, 1, '1.19 (1.09-1.30)', '1.13 (1.05-1.21)'),
5 OddsRatio=c(NA,1,2.76,3.68,4.61, NA,1,1.03, 1.26, NA, 1,1.12, NA, 1, 1.42, 2.09, 2.20, NA, 1, 1.39, 1.37, 1.55, NA, 1, 0.91, 0.77, 0.78, 0.61 , NA, 1, 1,0.92, NA, 1,1.19,1.29, NA, 1, 1.19, 1.13),
6 ORLower=c(NA,NA,2.49,3.26,4.06,NA,NA,0.94, 1.04, NA, NA,1.04, NA, NA, 1.30,1.85, 1.93, NA, NA,1.25, 1.24, 1.39, NA, NA, 0.84, 0.71, 0.65, 0.54, NA, NA, 0.91, 0.85, NA, NA, 1.07, 1.19, NA, NA,1.09, 1.05),
7 ORUpper=c(NA,NA,3.07,4.15,5.23,NA,NA,1.13, 1.54, NA, NA,1.21, NA, NA, 1.56, 2.36, 2.49, NA, NA, 1.54, 1.51,1.72, NA, NA, 0.99, 0.84, 0.79, 0.69, NA, NA, 1.10, 0.998, NA, NA, 1.31, 1.40, NA, NA, 1.30,1.21),
8 Pvalue=c(NA,NA,'< 0.001','< 0.001','< 0.001', NA,NA, 0.518, 0.021, NA, NA, 0.003, NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, 0.029, '< 0.001','< 0.001','< 0.001', NA, NA, 0.993, 0.045, NA, NA, '< 0.001','< 0.001',NA, NA, '< 0.001', 0.002),
9 stringsAsFactors=FALSE )
10
11#png('temp.png', width=8, height=4, units='in', res=400)
12
13rowseq <- seq(nrow(mydf),1)
14par(mai=c(0.7,0,0,0))
15plot(mydf$OddsRatio, rowseq, pch=15,
16 xlim=c(-0.8,6.2), ylim=c(0,42),
17 xlab='', ylab='', yaxt='n', xaxt='n',
18 bty='n')
19axis(1, seq(0.5, 5,by=0.5), cex.axis=1)
20
21segments(1,-1,1,40.20, lty=3, )
22segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
23
24mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=1, font=2)
25
26text(-1,42, "Factors", cex=1.4, font=2, pos=4)
27t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
28text(-1,rowseq, t1h, cex=1.3, pos=4, font=2)
29t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
30text(-0.98,rowseq, t1, cex=1.3, pos=4)
31
32text(4.6,42, "Adjusted Odds Ratio (95% CI)", cex=1.4, font=2, pos=4)
33t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR,big.mark=","), '')
34text(6, rowseq, t2, cex=1.3, pos=2)
35
36text(6,42, "P-value", cex=1.4, font=2, pos=4)
37t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
38text(6,rowseq, t4, cex=1.3, pos=4)
39cex11 <- 1
40cex12 <- 1.2
41cex42 <- cex41 <- cex23 <- cex22 <- cex21 <- 1.3
42
43
44png('temp.png', width=23, height=12, units='in', res=400)
45
46par(mai=c(0.7, 0, 0, 0))
47
48rowseq <- seq(nrow(mydf), 1)
49plot(mydf$OddsRatio, rowseq, xlim=c(-0.8, 6.2), ylim=c(0, 42),
50 xlab='', ylab='', yaxt='n', xaxt='n', bty='n')
51abline(h=rowseq[rowseq %% 2 != 0], lwd=25, col='grey90') ## grey shading
52points(mydf$OddsRatio, rowseq, pch=15)
53axis(1, seq(0.5, 5, by=0.5), cex.axis=cex11)
54segments(1, -1, 1, 40.20, lty=3, )
55segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
56mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=cex12, font=2)
57text(-1, 42, "Factors", cex=cex21, font=2, pos=4)
58t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
59text(-1, rowseq, t1h, cex=cex22, pos=4, font=2)
60t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
61text(-0.98, rowseq, t1, cex=cex23, pos=4)
62text(4.6, 42, "Adjusted Odds Ratio (95% CI)", cex=cex41, font=2, pos=4)
63t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
64text(6, rowseq, t2, cex=cex42, pos=2)
65text(6, 42, "P-value", cex=cex41, font=2, pos=4)
66t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
67text(6, rowseq, t4, cex=cex41, pos=4)
68
69dev.off()
70
it might be more convenient to expand the margins and use mtext instead of text. The code parts could also be better organized to avoid confusion. For the names of text parameters, use numbers according to their plot margin which are numbered according to their quadrant (1=bottom, 2=left, 3=top, 4=right). So try this:
1mydf <- data.frame(
2 SubgroupH=c('Age',NA,NA,NA,NA,'Marital or Union Status',NA,NA, NA, 'Place of Residence', NA, NA, 'Education', NA, NA, NA, NA,'Occupation', NA, NA, NA, NA, 'Wealth', NA, NA, NA, NA, NA, 'Reading newspaper or magazine', NA, NA, NA, 'Frequency of watching television', NA, NA, NA, 'Frequency of listening radio', NA, NA, NA ),
3 Subgroup=c(NA,'15-19','20-29','30-39','40-49', NA, 'Currently Married or Union', 'Never Married or Union','Formally Married or Union', NA, 'Rural', 'Urban', NA, 'Higher', 'Secondary', 'Primary', 'No eduction', NA, 'Not working', 'Professional/technical/managerial/services', 'Agriculture', 'Skilled/unskilled manual', NA, 'Poorest', 'Poorer', 'Middle','Richer', 'Richest', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week'),
4 AdjustedOR=c(NA,1,'2.76 (2.49-3.07)','3.68 (3.26-4.15)','4.61 (4.06-5.23)',NA,1,'1.03 (0.94-1.13)', '1.26 (1.04-1.54)', NA, 1,'1.12 (1.04-1.21)', NA, 1, '1.42 (1.30-1.56)', '2.09 (1.85-2.36)', '2.20 (1.93-2.49)', NA, 1, '1.39 (1.25-1.54)', '1.37 (1.24-1.51)', '1.55 (1.39-1.72)', NA, 1, '0.91 (0.84-0.99)', '0.77 (0.71-0.84)', '0.72 (0.65-0.79)', '0.61 (0.54-0.69)', NA, 1, '1 (0.91-1.10)', '0.92 (0.85-0.998)', NA, 1, '1.19 (1.07-1.31)', '1.29 (1.19-1.40)', NA, 1, '1.19 (1.09-1.30)', '1.13 (1.05-1.21)'),
5 OddsRatio=c(NA,1,2.76,3.68,4.61, NA,1,1.03, 1.26, NA, 1,1.12, NA, 1, 1.42, 2.09, 2.20, NA, 1, 1.39, 1.37, 1.55, NA, 1, 0.91, 0.77, 0.78, 0.61 , NA, 1, 1,0.92, NA, 1,1.19,1.29, NA, 1, 1.19, 1.13),
6 ORLower=c(NA,NA,2.49,3.26,4.06,NA,NA,0.94, 1.04, NA, NA,1.04, NA, NA, 1.30,1.85, 1.93, NA, NA,1.25, 1.24, 1.39, NA, NA, 0.84, 0.71, 0.65, 0.54, NA, NA, 0.91, 0.85, NA, NA, 1.07, 1.19, NA, NA,1.09, 1.05),
7 ORUpper=c(NA,NA,3.07,4.15,5.23,NA,NA,1.13, 1.54, NA, NA,1.21, NA, NA, 1.56, 2.36, 2.49, NA, NA, 1.54, 1.51,1.72, NA, NA, 0.99, 0.84, 0.79, 0.69, NA, NA, 1.10, 0.998, NA, NA, 1.31, 1.40, NA, NA, 1.30,1.21),
8 Pvalue=c(NA,NA,'< 0.001','< 0.001','< 0.001', NA,NA, 0.518, 0.021, NA, NA, 0.003, NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, 0.029, '< 0.001','< 0.001','< 0.001', NA, NA, 0.993, 0.045, NA, NA, '< 0.001','< 0.001',NA, NA, '< 0.001', 0.002),
9 stringsAsFactors=FALSE )
10
11#png('temp.png', width=8, height=4, units='in', res=400)
12
13rowseq <- seq(nrow(mydf),1)
14par(mai=c(0.7,0,0,0))
15plot(mydf$OddsRatio, rowseq, pch=15,
16 xlim=c(-0.8,6.2), ylim=c(0,42),
17 xlab='', ylab='', yaxt='n', xaxt='n',
18 bty='n')
19axis(1, seq(0.5, 5,by=0.5), cex.axis=1)
20
21segments(1,-1,1,40.20, lty=3, )
22segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
23
24mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=1, font=2)
25
26text(-1,42, "Factors", cex=1.4, font=2, pos=4)
27t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
28text(-1,rowseq, t1h, cex=1.3, pos=4, font=2)
29t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
30text(-0.98,rowseq, t1, cex=1.3, pos=4)
31
32text(4.6,42, "Adjusted Odds Ratio (95% CI)", cex=1.4, font=2, pos=4)
33t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR,big.mark=","), '')
34text(6, rowseq, t2, cex=1.3, pos=2)
35
36text(6,42, "P-value", cex=1.4, font=2, pos=4)
37t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
38text(6,rowseq, t4, cex=1.3, pos=4)
39cex11 <- 1
40cex12 <- 1.2
41cex42 <- cex41 <- cex23 <- cex22 <- cex21 <- 1.3
42
43
44png('temp.png', width=23, height=12, units='in', res=400)
45
46par(mai=c(0.7, 0, 0, 0))
47
48rowseq <- seq(nrow(mydf), 1)
49plot(mydf$OddsRatio, rowseq, xlim=c(-0.8, 6.2), ylim=c(0, 42),
50 xlab='', ylab='', yaxt='n', xaxt='n', bty='n')
51abline(h=rowseq[rowseq %% 2 != 0], lwd=25, col='grey90') ## grey shading
52points(mydf$OddsRatio, rowseq, pch=15)
53axis(1, seq(0.5, 5, by=0.5), cex.axis=cex11)
54segments(1, -1, 1, 40.20, lty=3, )
55segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
56mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=cex12, font=2)
57text(-1, 42, "Factors", cex=cex21, font=2, pos=4)
58t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
59text(-1, rowseq, t1h, cex=cex22, pos=4, font=2)
60t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
61text(-0.98, rowseq, t1, cex=cex23, pos=4)
62text(4.6, 42, "Adjusted Odds Ratio (95% CI)", cex=cex41, font=2, pos=4)
63t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
64text(6, rowseq, t2, cex=cex42, pos=2)
65text(6, 42, "P-value", cex=cex41, font=2, pos=4)
66t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
67text(6, rowseq, t4, cex=cex41, pos=4)
68
69dev.off()
70## parameters
71rowseq <- rev(seq_len(dim(mydf)[1]))
72rg <- range(mydf[c('ORLower', 'ORUpper')], na.rm=TRUE)
73
74t2h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
75t2 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
76t4or <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
77t4p <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
78
79cexh1 <- 1.3
80cexh2 <- 1.2
81cext <- 1.1
821mydf <- data.frame(
2 SubgroupH=c('Age',NA,NA,NA,NA,'Marital or Union Status',NA,NA, NA, 'Place of Residence', NA, NA, 'Education', NA, NA, NA, NA,'Occupation', NA, NA, NA, NA, 'Wealth', NA, NA, NA, NA, NA, 'Reading newspaper or magazine', NA, NA, NA, 'Frequency of watching television', NA, NA, NA, 'Frequency of listening radio', NA, NA, NA ),
3 Subgroup=c(NA,'15-19','20-29','30-39','40-49', NA, 'Currently Married or Union', 'Never Married or Union','Formally Married or Union', NA, 'Rural', 'Urban', NA, 'Higher', 'Secondary', 'Primary', 'No eduction', NA, 'Not working', 'Professional/technical/managerial/services', 'Agriculture', 'Skilled/unskilled manual', NA, 'Poorest', 'Poorer', 'Middle','Richer', 'Richest', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week'),
4 AdjustedOR=c(NA,1,'2.76 (2.49-3.07)','3.68 (3.26-4.15)','4.61 (4.06-5.23)',NA,1,'1.03 (0.94-1.13)', '1.26 (1.04-1.54)', NA, 1,'1.12 (1.04-1.21)', NA, 1, '1.42 (1.30-1.56)', '2.09 (1.85-2.36)', '2.20 (1.93-2.49)', NA, 1, '1.39 (1.25-1.54)', '1.37 (1.24-1.51)', '1.55 (1.39-1.72)', NA, 1, '0.91 (0.84-0.99)', '0.77 (0.71-0.84)', '0.72 (0.65-0.79)', '0.61 (0.54-0.69)', NA, 1, '1 (0.91-1.10)', '0.92 (0.85-0.998)', NA, 1, '1.19 (1.07-1.31)', '1.29 (1.19-1.40)', NA, 1, '1.19 (1.09-1.30)', '1.13 (1.05-1.21)'),
5 OddsRatio=c(NA,1,2.76,3.68,4.61, NA,1,1.03, 1.26, NA, 1,1.12, NA, 1, 1.42, 2.09, 2.20, NA, 1, 1.39, 1.37, 1.55, NA, 1, 0.91, 0.77, 0.78, 0.61 , NA, 1, 1,0.92, NA, 1,1.19,1.29, NA, 1, 1.19, 1.13),
6 ORLower=c(NA,NA,2.49,3.26,4.06,NA,NA,0.94, 1.04, NA, NA,1.04, NA, NA, 1.30,1.85, 1.93, NA, NA,1.25, 1.24, 1.39, NA, NA, 0.84, 0.71, 0.65, 0.54, NA, NA, 0.91, 0.85, NA, NA, 1.07, 1.19, NA, NA,1.09, 1.05),
7 ORUpper=c(NA,NA,3.07,4.15,5.23,NA,NA,1.13, 1.54, NA, NA,1.21, NA, NA, 1.56, 2.36, 2.49, NA, NA, 1.54, 1.51,1.72, NA, NA, 0.99, 0.84, 0.79, 0.69, NA, NA, 1.10, 0.998, NA, NA, 1.31, 1.40, NA, NA, 1.30,1.21),
8 Pvalue=c(NA,NA,'< 0.001','< 0.001','< 0.001', NA,NA, 0.518, 0.021, NA, NA, 0.003, NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, 0.029, '< 0.001','< 0.001','< 0.001', NA, NA, 0.993, 0.045, NA, NA, '< 0.001','< 0.001',NA, NA, '< 0.001', 0.002),
9 stringsAsFactors=FALSE )
10
11#png('temp.png', width=8, height=4, units='in', res=400)
12
13rowseq <- seq(nrow(mydf),1)
14par(mai=c(0.7,0,0,0))
15plot(mydf$OddsRatio, rowseq, pch=15,
16 xlim=c(-0.8,6.2), ylim=c(0,42),
17 xlab='', ylab='', yaxt='n', xaxt='n',
18 bty='n')
19axis(1, seq(0.5, 5,by=0.5), cex.axis=1)
20
21segments(1,-1,1,40.20, lty=3, )
22segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
23
24mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=1, font=2)
25
26text(-1,42, "Factors", cex=1.4, font=2, pos=4)
27t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
28text(-1,rowseq, t1h, cex=1.3, pos=4, font=2)
29t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
30text(-0.98,rowseq, t1, cex=1.3, pos=4)
31
32text(4.6,42, "Adjusted Odds Ratio (95% CI)", cex=1.4, font=2, pos=4)
33t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR,big.mark=","), '')
34text(6, rowseq, t2, cex=1.3, pos=2)
35
36text(6,42, "P-value", cex=1.4, font=2, pos=4)
37t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
38text(6,rowseq, t4, cex=1.3, pos=4)
39cex11 <- 1
40cex12 <- 1.2
41cex42 <- cex41 <- cex23 <- cex22 <- cex21 <- 1.3
42
43
44png('temp.png', width=23, height=12, units='in', res=400)
45
46par(mai=c(0.7, 0, 0, 0))
47
48rowseq <- seq(nrow(mydf), 1)
49plot(mydf$OddsRatio, rowseq, xlim=c(-0.8, 6.2), ylim=c(0, 42),
50 xlab='', ylab='', yaxt='n', xaxt='n', bty='n')
51abline(h=rowseq[rowseq %% 2 != 0], lwd=25, col='grey90') ## grey shading
52points(mydf$OddsRatio, rowseq, pch=15)
53axis(1, seq(0.5, 5, by=0.5), cex.axis=cex11)
54segments(1, -1, 1, 40.20, lty=3, )
55segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
56mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=cex12, font=2)
57text(-1, 42, "Factors", cex=cex21, font=2, pos=4)
58t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
59text(-1, rowseq, t1h, cex=cex22, pos=4, font=2)
60t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
61text(-0.98, rowseq, t1, cex=cex23, pos=4)
62text(4.6, 42, "Adjusted Odds Ratio (95% CI)", cex=cex41, font=2, pos=4)
63t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
64text(6, rowseq, t2, cex=cex42, pos=2)
65text(6, 42, "P-value", cex=cex41, font=2, pos=4)
66t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
67text(6, rowseq, t4, cex=cex41, pos=4)
68
69dev.off()
70## parameters
71rowseq <- rev(seq_len(dim(mydf)[1]))
72rg <- range(mydf[c('ORLower', 'ORUpper')], na.rm=TRUE)
73
74t2h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
75t2 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
76t4or <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
77t4p <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
78
79cexh1 <- 1.3
80cexh2 <- 1.2
81cext <- 1.1
82## plot
83png('temp.png', width=18, height=12, units='in', res=400)
84
85op <- par(mar=c(5, 18.5, 4, 15)+.1)
86
87plot(mydf$OddsRatio, rowseq, type='n', xlim=rg, axes=FALSE, xlab='', ylab='')
88## content
89abline(h=rowseq[rowseq %% 2 == 0], lwd=20, col='grey95', xpd=TRUE) ## grey shades
90points(mydf$OddsRatio, rowseq, pch=15)
91segments(1, 0, 1, max(rowseq)*1.025, lty=3)
92segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
93## margin 1
94axis(1, seq(0.5, 5, by=0.5), cex.axis=cex11)
95mtext('Adjusted Odds Ratio (95% CI)', 1, line=2.5, at=1.2, cex=cexh1, font=2)
96## margin 2
97mtext("Factors", 2, 17.5, at=max(rowseq)*1.03, las=2, adj=0, font=2, cex=cexh1)
98mtext(t2h, 2, 17.5, at=rowseq, las=2, adj=0, font=2, cex=cexh2)
99mtext(t2, 2, 17, at=rowseq, las=2, adj=0, cex=cext)
100## margin 4
101mtext("Adjusted Odds Ratio (95% CI)", 4, 7, at=max(rowseq)*1.03, las=2, adj=1,
102 font=2, cex=cexh1)
103mtext(t4or, 4, 7, at=rowseq, las=2, adj=1, cex=cext)
104mtext("P-value", 4, 12, at=max(rowseq)*1.03, las=2, adj=1, font=2, cex=cexh1)
105mtext(t4p, 4, 12, at=rowseq, las=2, adj=1, cex=cext)
106
107par(op)
108
109dev.off()
110
Data:
1mydf <- data.frame(
2 SubgroupH=c('Age',NA,NA,NA,NA,'Marital or Union Status',NA,NA, NA, 'Place of Residence', NA, NA, 'Education', NA, NA, NA, NA,'Occupation', NA, NA, NA, NA, 'Wealth', NA, NA, NA, NA, NA, 'Reading newspaper or magazine', NA, NA, NA, 'Frequency of watching television', NA, NA, NA, 'Frequency of listening radio', NA, NA, NA ),
3 Subgroup=c(NA,'15-19','20-29','30-39','40-49', NA, 'Currently Married or Union', 'Never Married or Union','Formally Married or Union', NA, 'Rural', 'Urban', NA, 'Higher', 'Secondary', 'Primary', 'No eduction', NA, 'Not working', 'Professional/technical/managerial/services', 'Agriculture', 'Skilled/unskilled manual', NA, 'Poorest', 'Poorer', 'Middle','Richer', 'Richest', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week'),
4 AdjustedOR=c(NA,1,'2.76 (2.49-3.07)','3.68 (3.26-4.15)','4.61 (4.06-5.23)',NA,1,'1.03 (0.94-1.13)', '1.26 (1.04-1.54)', NA, 1,'1.12 (1.04-1.21)', NA, 1, '1.42 (1.30-1.56)', '2.09 (1.85-2.36)', '2.20 (1.93-2.49)', NA, 1, '1.39 (1.25-1.54)', '1.37 (1.24-1.51)', '1.55 (1.39-1.72)', NA, 1, '0.91 (0.84-0.99)', '0.77 (0.71-0.84)', '0.72 (0.65-0.79)', '0.61 (0.54-0.69)', NA, 1, '1 (0.91-1.10)', '0.92 (0.85-0.998)', NA, 1, '1.19 (1.07-1.31)', '1.29 (1.19-1.40)', NA, 1, '1.19 (1.09-1.30)', '1.13 (1.05-1.21)'),
5 OddsRatio=c(NA,1,2.76,3.68,4.61, NA,1,1.03, 1.26, NA, 1,1.12, NA, 1, 1.42, 2.09, 2.20, NA, 1, 1.39, 1.37, 1.55, NA, 1, 0.91, 0.77, 0.78, 0.61 , NA, 1, 1,0.92, NA, 1,1.19,1.29, NA, 1, 1.19, 1.13),
6 ORLower=c(NA,NA,2.49,3.26,4.06,NA,NA,0.94, 1.04, NA, NA,1.04, NA, NA, 1.30,1.85, 1.93, NA, NA,1.25, 1.24, 1.39, NA, NA, 0.84, 0.71, 0.65, 0.54, NA, NA, 0.91, 0.85, NA, NA, 1.07, 1.19, NA, NA,1.09, 1.05),
7 ORUpper=c(NA,NA,3.07,4.15,5.23,NA,NA,1.13, 1.54, NA, NA,1.21, NA, NA, 1.56, 2.36, 2.49, NA, NA, 1.54, 1.51,1.72, NA, NA, 0.99, 0.84, 0.79, 0.69, NA, NA, 1.10, 0.998, NA, NA, 1.31, 1.40, NA, NA, 1.30,1.21),
8 Pvalue=c(NA,NA,'< 0.001','< 0.001','< 0.001', NA,NA, 0.518, 0.021, NA, NA, 0.003, NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, 0.029, '< 0.001','< 0.001','< 0.001', NA, NA, 0.993, 0.045, NA, NA, '< 0.001','< 0.001',NA, NA, '< 0.001', 0.002),
9 stringsAsFactors=FALSE )
10
11#png('temp.png', width=8, height=4, units='in', res=400)
12
13rowseq <- seq(nrow(mydf),1)
14par(mai=c(0.7,0,0,0))
15plot(mydf$OddsRatio, rowseq, pch=15,
16 xlim=c(-0.8,6.2), ylim=c(0,42),
17 xlab='', ylab='', yaxt='n', xaxt='n',
18 bty='n')
19axis(1, seq(0.5, 5,by=0.5), cex.axis=1)
20
21segments(1,-1,1,40.20, lty=3, )
22segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
23
24mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=1, font=2)
25
26text(-1,42, "Factors", cex=1.4, font=2, pos=4)
27t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
28text(-1,rowseq, t1h, cex=1.3, pos=4, font=2)
29t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
30text(-0.98,rowseq, t1, cex=1.3, pos=4)
31
32text(4.6,42, "Adjusted Odds Ratio (95% CI)", cex=1.4, font=2, pos=4)
33t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR,big.mark=","), '')
34text(6, rowseq, t2, cex=1.3, pos=2)
35
36text(6,42, "P-value", cex=1.4, font=2, pos=4)
37t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
38text(6,rowseq, t4, cex=1.3, pos=4)
39cex11 <- 1
40cex12 <- 1.2
41cex42 <- cex41 <- cex23 <- cex22 <- cex21 <- 1.3
42
43
44png('temp.png', width=23, height=12, units='in', res=400)
45
46par(mai=c(0.7, 0, 0, 0))
47
48rowseq <- seq(nrow(mydf), 1)
49plot(mydf$OddsRatio, rowseq, xlim=c(-0.8, 6.2), ylim=c(0, 42),
50 xlab='', ylab='', yaxt='n', xaxt='n', bty='n')
51abline(h=rowseq[rowseq %% 2 != 0], lwd=25, col='grey90') ## grey shading
52points(mydf$OddsRatio, rowseq, pch=15)
53axis(1, seq(0.5, 5, by=0.5), cex.axis=cex11)
54segments(1, -1, 1, 40.20, lty=3, )
55segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
56mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=cex12, font=2)
57text(-1, 42, "Factors", cex=cex21, font=2, pos=4)
58t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
59text(-1, rowseq, t1h, cex=cex22, pos=4, font=2)
60t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
61text(-0.98, rowseq, t1, cex=cex23, pos=4)
62text(4.6, 42, "Adjusted Odds Ratio (95% CI)", cex=cex41, font=2, pos=4)
63t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
64text(6, rowseq, t2, cex=cex42, pos=2)
65text(6, 42, "P-value", cex=cex41, font=2, pos=4)
66t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
67text(6, rowseq, t4, cex=cex41, pos=4)
68
69dev.off()
70## parameters
71rowseq <- rev(seq_len(dim(mydf)[1]))
72rg <- range(mydf[c('ORLower', 'ORUpper')], na.rm=TRUE)
73
74t2h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
75t2 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
76t4or <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
77t4p <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
78
79cexh1 <- 1.3
80cexh2 <- 1.2
81cext <- 1.1
82## plot
83png('temp.png', width=18, height=12, units='in', res=400)
84
85op <- par(mar=c(5, 18.5, 4, 15)+.1)
86
87plot(mydf$OddsRatio, rowseq, type='n', xlim=rg, axes=FALSE, xlab='', ylab='')
88## content
89abline(h=rowseq[rowseq %% 2 == 0], lwd=20, col='grey95', xpd=TRUE) ## grey shades
90points(mydf$OddsRatio, rowseq, pch=15)
91segments(1, 0, 1, max(rowseq)*1.025, lty=3)
92segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
93## margin 1
94axis(1, seq(0.5, 5, by=0.5), cex.axis=cex11)
95mtext('Adjusted Odds Ratio (95% CI)', 1, line=2.5, at=1.2, cex=cexh1, font=2)
96## margin 2
97mtext("Factors", 2, 17.5, at=max(rowseq)*1.03, las=2, adj=0, font=2, cex=cexh1)
98mtext(t2h, 2, 17.5, at=rowseq, las=2, adj=0, font=2, cex=cexh2)
99mtext(t2, 2, 17, at=rowseq, las=2, adj=0, cex=cext)
100## margin 4
101mtext("Adjusted Odds Ratio (95% CI)", 4, 7, at=max(rowseq)*1.03, las=2, adj=1,
102 font=2, cex=cexh1)
103mtext(t4or, 4, 7, at=rowseq, las=2, adj=1, cex=cext)
104mtext("P-value", 4, 12, at=max(rowseq)*1.03, las=2, adj=1, font=2, cex=cexh1)
105mtext(t4p, 4, 12, at=rowseq, las=2, adj=1, cex=cext)
106
107par(op)
108
109dev.off()
110mydf <- structure(list(SubgroupH = c("Age", NA, NA, NA, NA, "Marital or Union Status",
111NA, NA, NA, "Place of Residence", NA, NA, "Education", NA, NA,
112NA, NA, "Occupation", NA, NA, NA, NA, "Wealth", NA, NA, NA, NA,
113NA, "Reading newspaper or magazine", NA, NA, NA, "Frequency of watching television",
114NA, NA, NA, "Frequency of listening radio", NA, NA, NA), Subgroup = c(NA,
115"15-19", "20-29", "30-39", "40-49", NA, "Currently Married or Union",
116"Never Married or Union", "Formally Married or Union", NA, "Rural",
117"Urban", NA, "Higher", "Secondary", "Primary", "No eduction",
118NA, "Not working", "Professional/technical/managerial/services",
119"Agriculture", "Skilled/unskilled manual", NA, "Poorest", "Poorer",
120"Middle", "Richer", "Richest", NA, "Not at all", "Less than once a week",
121"At least once a week", NA, "Not at all", "Less than once a week",
122"At least once a week", NA, "Not at all", "Less than once a week",
123"At least once a week"), AdjustedOR = c(NA, "1", "2.76 (2.49-3.07)",
124"3.68 (3.26-4.15)", "4.61 (4.06-5.23)", NA, "1", "1.03 (0.94-1.13)",
125"1.26 (1.04-1.54)", NA, "1", "1.12 (1.04-1.21)", NA, "1", "1.42 (1.30-1.56)",
126"2.09 (1.85-2.36)", "2.20 (1.93-2.49)", NA, "1", "1.39 (1.25-1.54)",
127"1.37 (1.24-1.51)", "1.55 (1.39-1.72)", NA, "1", "0.91 (0.84-0.99)",
128"0.77 (0.71-0.84)", "0.72 (0.65-0.79)", "0.61 (0.54-0.69)", NA,
129"1", "1 (0.91-1.10)", "0.92 (0.85-0.998)", NA, "1", "1.19 (1.07-1.31)",
130"1.29 (1.19-1.40)", NA, "1", "1.19 (1.09-1.30)", "1.13 (1.05-1.21)"
131), OddsRatio = c(NA, 1, 2.76, 3.68, 4.61, NA, 1, 1.03, 1.26,
132NA, 1, 1.12, NA, 1, 1.42, 2.09, 2.2, NA, 1, 1.39, 1.37, 1.55,
133NA, 1, 0.91, 0.77, 0.78, 0.61, NA, 1, 1, 0.92, NA, 1, 1.19, 1.29,
134NA, 1, 1.19, 1.13), ORLower = c(NA, NA, 2.49, 3.26, 4.06, NA,
135NA, 0.94, 1.04, NA, NA, 1.04, NA, NA, 1.3, 1.85, 1.93, NA, NA,
1361.25, 1.24, 1.39, NA, NA, 0.84, 0.71, 0.65, 0.54, NA, NA, 0.91,
1370.85, NA, NA, 1.07, 1.19, NA, NA, 1.09, 1.05), ORUpper = c(NA,
138NA, 3.07, 4.15, 5.23, NA, NA, 1.13, 1.54, NA, NA, 1.21, NA, NA,
1391.56, 2.36, 2.49, NA, NA, 1.54, 1.51, 1.72, NA, NA, 0.99, 0.84,
1400.79, 0.69, NA, NA, 1.1, 0.998, NA, NA, 1.31, 1.4, NA, NA, 1.3,
1411.21), Pvalue = c(NA, NA, "< 0.001", "< 0.001", "< 0.001", NA,
142NA, "0.518", "0.021", NA, NA, "0.003", NA, NA, "< 0.001", "< 0.001",
143"< 0.001", NA, NA, "< 0.001", "< 0.001", "< 0.001", NA, NA, "0.029",
144"< 0.001", "< 0.001", "< 0.001", NA, NA, "0.993", "0.045", NA,
145NA, "< 0.001", "< 0.001", NA, NA, "< 0.001", "0.002")), class = "data.frame", row.names = c(NA,
146-40L))
147QUESTION
MySQL SQL Performance need some improvement
Asked 2022-Jan-13 at 01:35I have worked my way around many challenges with MySQL, and i think right now i am able to build everything that i need, to get something to work. But now, for a pretty huge SQL statement that returns a lot of data, i need to work on MySQL performance for the first time.
I was hoping someone here could help me find out why the following statement is so incredibly slow. It takes over 3 minutes to collect 740 results out of different tables. The biggest table beeing the "reports" table, consisting of somewhere over 20.000 entries at the moment.
I can also educate myself if someone could just point me in the right direction. I don't even know where to search for answers for my current problem.
Okay, so here is the statement that i am talking about. Maybe, if someone has enough experience with SQL performance, something just right away jumps at them. I would be happy for any kind of feedback. I'll elaborate on the statement right after the code itself:
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46So what i have here is the following: reports (R_*) - This is the main table that is queried. I need some of it's data, but it's also the filter, since i only need results between specific timestamps.
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68userreports (UR_*) - Delivers some data that is calculated from the sourcedata in reports
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86customerreports (CR_*) - Same as userreports, but with calculated data from the customers perspective
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109users (U_*) - Obviously delivers Data to the user that created the report, e.g. name,...
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users` (
110 `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111 `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112 `U_PWInitial` tinyint(1) NOT NULL,
113 `U_FailedAttempts` int(1) NOT NULL,
114 `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115 `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116 `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117 `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118 `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119 `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120 `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121 `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122 `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123 `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124 `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125 `U_Birthdate` date NOT NULL,
126 `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127 `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128 `U_Severelydisabled` tinyint(1) NOT NULL,
129 `U_Severelydisabledspecify` int(3) NOT NULL,
130 `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131 `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132 `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133 `U_CLID` tinyint(1) NOT NULL,
134 `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135 `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136 `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137 `U_INID` int(11) DEFAULT NULL,
138 `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139 `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140 `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141 `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142 `U_Entry` date NOT NULL,
143 `U_TEntry` date NOT NULL,
144 `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145 `U_Hourscarryover` decimal(20, 2) NOT NULL,
146 `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147 `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148 `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149 `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150 `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151 `U_DeleteDate` date DEFAULT NULL,
152 PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154customers (C_*) - Same as users, but for the data of the customer that the user worked on
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users` (
110 `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111 `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112 `U_PWInitial` tinyint(1) NOT NULL,
113 `U_FailedAttempts` int(1) NOT NULL,
114 `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115 `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116 `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117 `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118 `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119 `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120 `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121 `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122 `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123 `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124 `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125 `U_Birthdate` date NOT NULL,
126 `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127 `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128 `U_Severelydisabled` tinyint(1) NOT NULL,
129 `U_Severelydisabledspecify` int(3) NOT NULL,
130 `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131 `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132 `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133 `U_CLID` tinyint(1) NOT NULL,
134 `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135 `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136 `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137 `U_INID` int(11) DEFAULT NULL,
138 `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139 `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140 `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141 `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142 `U_Entry` date NOT NULL,
143 `U_TEntry` date NOT NULL,
144 `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145 `U_Hourscarryover` decimal(20, 2) NOT NULL,
146 `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147 `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148 `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149 `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150 `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151 `U_DeleteDate` date DEFAULT NULL,
152 PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154CREATE TABLE `customers` (
155 `C_ID` int(10) NOT NULL AUTO_INCREMENT,
156 `C_MID` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
157 `C_Active` tinyint(1) NOT NULL,
158 `C_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci,
159 `C_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
160 `C_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
161 `C_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
162 `C_Birthdate` date NOT NULL,
163 `C_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
164 `C_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
165 `C_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
166 `C_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167 `C_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
168 `C_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
169 `C_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
170 `C_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
171 `C_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
172 `C_Insurancenumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
173 `C_INID` int(11) DEFAULT NULL,
174 `C_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
175 `C_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
176 `C_Contact1` text CHARACTER SET utf8 COLLATE utf8_general_ci,
177 `C_Contact2` text CHARACTER SET utf8 COLLATE utf8_general_ci,
178 `C_ContactChoice` int(1) DEFAULT 0,
179 `C_DeleteDate` date DEFAULT NULL,
180 `C_DeactivationDate` date DEFAULT NULL,
181 `C_CreationDate` date DEFAULT NULL,
182 `C_DeceasedDate` date DEFAULT NULL,
183 PRIMARY KEY (`C_ID`) USING BTREE
184) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
185integration (I_*) - Provides data on whether or not the report is already integrated (and can no longer be changed)
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users` (
110 `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111 `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112 `U_PWInitial` tinyint(1) NOT NULL,
113 `U_FailedAttempts` int(1) NOT NULL,
114 `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115 `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116 `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117 `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118 `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119 `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120 `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121 `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122 `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123 `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124 `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125 `U_Birthdate` date NOT NULL,
126 `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127 `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128 `U_Severelydisabled` tinyint(1) NOT NULL,
129 `U_Severelydisabledspecify` int(3) NOT NULL,
130 `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131 `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132 `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133 `U_CLID` tinyint(1) NOT NULL,
134 `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135 `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136 `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137 `U_INID` int(11) DEFAULT NULL,
138 `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139 `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140 `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141 `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142 `U_Entry` date NOT NULL,
143 `U_TEntry` date NOT NULL,
144 `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145 `U_Hourscarryover` decimal(20, 2) NOT NULL,
146 `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147 `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148 `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149 `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150 `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151 `U_DeleteDate` date DEFAULT NULL,
152 PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154CREATE TABLE `customers` (
155 `C_ID` int(10) NOT NULL AUTO_INCREMENT,
156 `C_MID` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
157 `C_Active` tinyint(1) NOT NULL,
158 `C_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci,
159 `C_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
160 `C_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
161 `C_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
162 `C_Birthdate` date NOT NULL,
163 `C_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
164 `C_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
165 `C_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
166 `C_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167 `C_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
168 `C_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
169 `C_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
170 `C_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
171 `C_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
172 `C_Insurancenumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
173 `C_INID` int(11) DEFAULT NULL,
174 `C_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
175 `C_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
176 `C_Contact1` text CHARACTER SET utf8 COLLATE utf8_general_ci,
177 `C_Contact2` text CHARACTER SET utf8 COLLATE utf8_general_ci,
178 `C_ContactChoice` int(1) DEFAULT 0,
179 `C_DeleteDate` date DEFAULT NULL,
180 `C_DeactivationDate` date DEFAULT NULL,
181 `C_CreationDate` date DEFAULT NULL,
182 `C_DeceasedDate` date DEFAULT NULL,
183 PRIMARY KEY (`C_ID`) USING BTREE
184) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
185CREATE TABLE `integration` (
186 `I_ID` int(11) NOT NULL AUTO_INCREMENT,
187 `I_UID` int(11) NOT NULL,
188 `I_LastIntegration` date NOT NULL DEFAULT '1970-01-01',
189 `I_SumFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
190 `I_OldHolidays` int(5) NOT NULL DEFAULT 0,
191 PRIMARY KEY (`I_ID`) USING BTREE
192) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
193customerterms (CT_*) - In this case only provides if the specified customer needs to sign the report
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users` (
110 `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111 `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112 `U_PWInitial` tinyint(1) NOT NULL,
113 `U_FailedAttempts` int(1) NOT NULL,
114 `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115 `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116 `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117 `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118 `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119 `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120 `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121 `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122 `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123 `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124 `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125 `U_Birthdate` date NOT NULL,
126 `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127 `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128 `U_Severelydisabled` tinyint(1) NOT NULL,
129 `U_Severelydisabledspecify` int(3) NOT NULL,
130 `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131 `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132 `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133 `U_CLID` tinyint(1) NOT NULL,
134 `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135 `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136 `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137 `U_INID` int(11) DEFAULT NULL,
138 `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139 `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140 `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141 `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142 `U_Entry` date NOT NULL,
143 `U_TEntry` date NOT NULL,
144 `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145 `U_Hourscarryover` decimal(20, 2) NOT NULL,
146 `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147 `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148 `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149 `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150 `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151 `U_DeleteDate` date DEFAULT NULL,
152 PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154CREATE TABLE `customers` (
155 `C_ID` int(10) NOT NULL AUTO_INCREMENT,
156 `C_MID` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
157 `C_Active` tinyint(1) NOT NULL,
158 `C_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci,
159 `C_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
160 `C_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
161 `C_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
162 `C_Birthdate` date NOT NULL,
163 `C_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
164 `C_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
165 `C_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
166 `C_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167 `C_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
168 `C_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
169 `C_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
170 `C_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
171 `C_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
172 `C_Insurancenumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
173 `C_INID` int(11) DEFAULT NULL,
174 `C_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
175 `C_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
176 `C_Contact1` text CHARACTER SET utf8 COLLATE utf8_general_ci,
177 `C_Contact2` text CHARACTER SET utf8 COLLATE utf8_general_ci,
178 `C_ContactChoice` int(1) DEFAULT 0,
179 `C_DeleteDate` date DEFAULT NULL,
180 `C_DeactivationDate` date DEFAULT NULL,
181 `C_CreationDate` date DEFAULT NULL,
182 `C_DeceasedDate` date DEFAULT NULL,
183 PRIMARY KEY (`C_ID`) USING BTREE
184) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
185CREATE TABLE `integration` (
186 `I_ID` int(11) NOT NULL AUTO_INCREMENT,
187 `I_UID` int(11) NOT NULL,
188 `I_LastIntegration` date NOT NULL DEFAULT '1970-01-01',
189 `I_SumFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
190 `I_OldHolidays` int(5) NOT NULL DEFAULT 0,
191 PRIMARY KEY (`I_ID`) USING BTREE
192) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
193CREATE TABLE `customerterms` (
194 `CT_ID` int(50) NOT NULL AUTO_INCREMENT,
195 `CT_CID` int(10) NOT NULL,
196 `CT_From` date NOT NULL,
197 `CT_To` date NOT NULL,
198 `CT_Hourly` decimal(20, 2) NOT NULL,
199 `CT_FixedTravelCompensation` decimal(20, 2) NOT NULL,
200 `CT_PerKMCompensationBase` decimal(20, 2) NOT NULL,
201 `CT_PerKMCompensationAdditional` decimal(20, 2) NOT NULL,
202 `CT_MaxTravelCompensationReport` decimal(20, 2) DEFAULT NULL,
203 `CT_MaxTravelCompensationMonthly` decimal(20, 2) DEFAULT NULL,
204 `CT_FixedSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
205 `CT_PercentageSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
206 `CT_FixedSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
207 `CT_PercentageSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
208 `CT_FixedHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
209 `CT_PercentageHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
210 `CT_SigReq` int(1) NOT NULL,
211 `CT_NighttimeFrom` time(0) NOT NULL DEFAULT '00:00:00',
212 `CT_NighttimeTo` time(0) NOT NULL DEFAULT '00:00:00',
213 `CT_FixedNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
214 `CT_PercentageNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
215 `CT_StackingSurcharge` tinyint(1) NOT NULL DEFAULT 0,
216 `CT_MinimumTime` int(11) NOT NULL DEFAULT 1,
217 `CT_TimeIncrement` int(11) NOT NULL DEFAULT 1,
218 PRIMARY KEY (`CT_ID`) USING BTREE
219) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
220The server is running MySQL 5.7, has 4 processors at 4,6Ghz, and 16GB of RAM available.
Since this is a hobby project, that i am supporting small care-businesses with, to allow them easier management of their daily tasks, i can change everything here. Code, Database Layout, you name it. As long as the poor people in the office don't have to wait for 5 minutes, just to sometimes even only get a timeout...
I'll add the result of EXPLAIN as image, since i can't get it to look good otherwise...
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users` (
110 `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111 `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112 `U_PWInitial` tinyint(1) NOT NULL,
113 `U_FailedAttempts` int(1) NOT NULL,
114 `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115 `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116 `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117 `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118 `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119 `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120 `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121 `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122 `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123 `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124 `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125 `U_Birthdate` date NOT NULL,
126 `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127 `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128 `U_Severelydisabled` tinyint(1) NOT NULL,
129 `U_Severelydisabledspecify` int(3) NOT NULL,
130 `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131 `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132 `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133 `U_CLID` tinyint(1) NOT NULL,
134 `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135 `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136 `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137 `U_INID` int(11) DEFAULT NULL,
138 `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139 `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140 `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141 `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142 `U_Entry` date NOT NULL,
143 `U_TEntry` date NOT NULL,
144 `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145 `U_Hourscarryover` decimal(20, 2) NOT NULL,
146 `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147 `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148 `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149 `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150 `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151 `U_DeleteDate` date DEFAULT NULL,
152 PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154CREATE TABLE `customers` (
155 `C_ID` int(10) NOT NULL AUTO_INCREMENT,
156 `C_MID` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
157 `C_Active` tinyint(1) NOT NULL,
158 `C_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci,
159 `C_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
160 `C_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
161 `C_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
162 `C_Birthdate` date NOT NULL,
163 `C_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
164 `C_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
165 `C_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
166 `C_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167 `C_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
168 `C_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
169 `C_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
170 `C_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
171 `C_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
172 `C_Insurancenumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
173 `C_INID` int(11) DEFAULT NULL,
174 `C_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
175 `C_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
176 `C_Contact1` text CHARACTER SET utf8 COLLATE utf8_general_ci,
177 `C_Contact2` text CHARACTER SET utf8 COLLATE utf8_general_ci,
178 `C_ContactChoice` int(1) DEFAULT 0,
179 `C_DeleteDate` date DEFAULT NULL,
180 `C_DeactivationDate` date DEFAULT NULL,
181 `C_CreationDate` date DEFAULT NULL,
182 `C_DeceasedDate` date DEFAULT NULL,
183 PRIMARY KEY (`C_ID`) USING BTREE
184) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
185CREATE TABLE `integration` (
186 `I_ID` int(11) NOT NULL AUTO_INCREMENT,
187 `I_UID` int(11) NOT NULL,
188 `I_LastIntegration` date NOT NULL DEFAULT '1970-01-01',
189 `I_SumFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
190 `I_OldHolidays` int(5) NOT NULL DEFAULT 0,
191 PRIMARY KEY (`I_ID`) USING BTREE
192) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
193CREATE TABLE `customerterms` (
194 `CT_ID` int(50) NOT NULL AUTO_INCREMENT,
195 `CT_CID` int(10) NOT NULL,
196 `CT_From` date NOT NULL,
197 `CT_To` date NOT NULL,
198 `CT_Hourly` decimal(20, 2) NOT NULL,
199 `CT_FixedTravelCompensation` decimal(20, 2) NOT NULL,
200 `CT_PerKMCompensationBase` decimal(20, 2) NOT NULL,
201 `CT_PerKMCompensationAdditional` decimal(20, 2) NOT NULL,
202 `CT_MaxTravelCompensationReport` decimal(20, 2) DEFAULT NULL,
203 `CT_MaxTravelCompensationMonthly` decimal(20, 2) DEFAULT NULL,
204 `CT_FixedSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
205 `CT_PercentageSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
206 `CT_FixedSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
207 `CT_PercentageSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
208 `CT_FixedHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
209 `CT_PercentageHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
210 `CT_SigReq` int(1) NOT NULL,
211 `CT_NighttimeFrom` time(0) NOT NULL DEFAULT '00:00:00',
212 `CT_NighttimeTo` time(0) NOT NULL DEFAULT '00:00:00',
213 `CT_FixedNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
214 `CT_PercentageNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
215 `CT_StackingSurcharge` tinyint(1) NOT NULL DEFAULT 0,
216 `CT_MinimumTime` int(11) NOT NULL DEFAULT 1,
217 `CT_TimeIncrement` int(11) NOT NULL DEFAULT 1,
218 PRIMARY KEY (`CT_ID`) USING BTREE
219) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
220+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
221| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
222+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
223| 1 | SIMPLE | reports | NULL | ALL | PRIMARY,R_From,R_To | NULL | NULL | NULL | 22249 | 29.76 | Using where; Using temporary; Using filesort |
224| 1 | SIMPLE | userreports | NULL | ALL | NULL | NULL | NULL | NULL | 21359 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
225| 1 | SIMPLE | users | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dbs671769.reports.R_UID | 1 | 100.00 | NULL |
226| 1 | SIMPLE | customers | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dbs671769.reports.R_CID | 1 | 100.00 | NULL |
227| 1 | SIMPLE | customerterms | NULL | ALL | NULL | NULL | NULL | NULL | 1429 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
228| 1 | SIMPLE | integration | NULL | ALL | NULL | NULL | NULL | NULL | 1134 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
229| 1 | SIMPLE | customerreports | NULL | ALL | NULL | NULL | NULL | NULL | 9078 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
230+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
231Is there any way to consolidate all this data faster, but as reliable?
Thanks a lot in advance, for any help or idea on this.
ANSWER
Answered 2022-Jan-13 at 01:03Let's start by adding an index for each of the foreign keys used in your query -
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users` (
110 `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111 `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112 `U_PWInitial` tinyint(1) NOT NULL,
113 `U_FailedAttempts` int(1) NOT NULL,
114 `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115 `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116 `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117 `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118 `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119 `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120 `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121 `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122 `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123 `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124 `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125 `U_Birthdate` date NOT NULL,
126 `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127 `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128 `U_Severelydisabled` tinyint(1) NOT NULL,
129 `U_Severelydisabledspecify` int(3) NOT NULL,
130 `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131 `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132 `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133 `U_CLID` tinyint(1) NOT NULL,
134 `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135 `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136 `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137 `U_INID` int(11) DEFAULT NULL,
138 `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139 `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140 `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141 `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142 `U_Entry` date NOT NULL,
143 `U_TEntry` date NOT NULL,
144 `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145 `U_Hourscarryover` decimal(20, 2) NOT NULL,
146 `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147 `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148 `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149 `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150 `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151 `U_DeleteDate` date DEFAULT NULL,
152 PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154CREATE TABLE `customers` (
155 `C_ID` int(10) NOT NULL AUTO_INCREMENT,
156 `C_MID` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
157 `C_Active` tinyint(1) NOT NULL,
158 `C_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci,
159 `C_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
160 `C_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
161 `C_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
162 `C_Birthdate` date NOT NULL,
163 `C_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
164 `C_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
165 `C_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
166 `C_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167 `C_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
168 `C_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
169 `C_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
170 `C_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
171 `C_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
172 `C_Insurancenumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
173 `C_INID` int(11) DEFAULT NULL,
174 `C_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
175 `C_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
176 `C_Contact1` text CHARACTER SET utf8 COLLATE utf8_general_ci,
177 `C_Contact2` text CHARACTER SET utf8 COLLATE utf8_general_ci,
178 `C_ContactChoice` int(1) DEFAULT 0,
179 `C_DeleteDate` date DEFAULT NULL,
180 `C_DeactivationDate` date DEFAULT NULL,
181 `C_CreationDate` date DEFAULT NULL,
182 `C_DeceasedDate` date DEFAULT NULL,
183 PRIMARY KEY (`C_ID`) USING BTREE
184) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
185CREATE TABLE `integration` (
186 `I_ID` int(11) NOT NULL AUTO_INCREMENT,
187 `I_UID` int(11) NOT NULL,
188 `I_LastIntegration` date NOT NULL DEFAULT '1970-01-01',
189 `I_SumFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
190 `I_OldHolidays` int(5) NOT NULL DEFAULT 0,
191 PRIMARY KEY (`I_ID`) USING BTREE
192) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
193CREATE TABLE `customerterms` (
194 `CT_ID` int(50) NOT NULL AUTO_INCREMENT,
195 `CT_CID` int(10) NOT NULL,
196 `CT_From` date NOT NULL,
197 `CT_To` date NOT NULL,
198 `CT_Hourly` decimal(20, 2) NOT NULL,
199 `CT_FixedTravelCompensation` decimal(20, 2) NOT NULL,
200 `CT_PerKMCompensationBase` decimal(20, 2) NOT NULL,
201 `CT_PerKMCompensationAdditional` decimal(20, 2) NOT NULL,
202 `CT_MaxTravelCompensationReport` decimal(20, 2) DEFAULT NULL,
203 `CT_MaxTravelCompensationMonthly` decimal(20, 2) DEFAULT NULL,
204 `CT_FixedSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
205 `CT_PercentageSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
206 `CT_FixedSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
207 `CT_PercentageSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
208 `CT_FixedHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
209 `CT_PercentageHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
210 `CT_SigReq` int(1) NOT NULL,
211 `CT_NighttimeFrom` time(0) NOT NULL DEFAULT '00:00:00',
212 `CT_NighttimeTo` time(0) NOT NULL DEFAULT '00:00:00',
213 `CT_FixedNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
214 `CT_PercentageNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
215 `CT_StackingSurcharge` tinyint(1) NOT NULL DEFAULT 0,
216 `CT_MinimumTime` int(11) NOT NULL DEFAULT 1,
217 `CT_TimeIncrement` int(11) NOT NULL DEFAULT 1,
218 PRIMARY KEY (`CT_ID`) USING BTREE
219) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
220+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
221| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
222+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
223| 1 | SIMPLE | reports | NULL | ALL | PRIMARY,R_From,R_To | NULL | NULL | NULL | 22249 | 29.76 | Using where; Using temporary; Using filesort |
224| 1 | SIMPLE | userreports | NULL | ALL | NULL | NULL | NULL | NULL | 21359 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
225| 1 | SIMPLE | users | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dbs671769.reports.R_UID | 1 | 100.00 | NULL |
226| 1 | SIMPLE | customers | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dbs671769.reports.R_CID | 1 | 100.00 | NULL |
227| 1 | SIMPLE | customerterms | NULL | ALL | NULL | NULL | NULL | NULL | 1429 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
228| 1 | SIMPLE | integration | NULL | ALL | NULL | NULL | NULL | NULL | 1134 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
229| 1 | SIMPLE | customerreports | NULL | ALL | NULL | NULL | NULL | NULL | 9078 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
230+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
231ALTER TABLE `userreports`
232 ADD INDEX `FK_UR_RID` (`UR_RID`);
233
234ALTER TABLE `customerterms`
235 ADD INDEX `FK_CT_CID` (`CT_CID`);
236
237ALTER TABLE `integration`
238 ADD INDEX `FK_I_UID` (`I_UID`);
239
240ALTER TABLE `customerreports`
241 ADD INDEX `FK_CR_RID` (`CR_RID`);
242Please add these indices and then add the updated EXPLAIN output plus the result of the following query to your question.
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users` (
110 `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111 `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112 `U_PWInitial` tinyint(1) NOT NULL,
113 `U_FailedAttempts` int(1) NOT NULL,
114 `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115 `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116 `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117 `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118 `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119 `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120 `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121 `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122 `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123 `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124 `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125 `U_Birthdate` date NOT NULL,
126 `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127 `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128 `U_Severelydisabled` tinyint(1) NOT NULL,
129 `U_Severelydisabledspecify` int(3) NOT NULL,
130 `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131 `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132 `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133 `U_CLID` tinyint(1) NOT NULL,
134 `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135 `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136 `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137 `U_INID` int(11) DEFAULT NULL,
138 `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139 `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140 `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141 `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142 `U_Entry` date NOT NULL,
143 `U_TEntry` date NOT NULL,
144 `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145 `U_Hourscarryover` decimal(20, 2) NOT NULL,
146 `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147 `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148 `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149 `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150 `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151 `U_DeleteDate` date DEFAULT NULL,
152 PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154CREATE TABLE `customers` (
155 `C_ID` int(10) NOT NULL AUTO_INCREMENT,
156 `C_MID` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
157 `C_Active` tinyint(1) NOT NULL,
158 `C_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci,
159 `C_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
160 `C_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
161 `C_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
162 `C_Birthdate` date NOT NULL,
163 `C_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
164 `C_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
165 `C_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
166 `C_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167 `C_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
168 `C_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
169 `C_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
170 `C_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
171 `C_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
172 `C_Insurancenumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
173 `C_INID` int(11) DEFAULT NULL,
174 `C_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
175 `C_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
176 `C_Contact1` text CHARACTER SET utf8 COLLATE utf8_general_ci,
177 `C_Contact2` text CHARACTER SET utf8 COLLATE utf8_general_ci,
178 `C_ContactChoice` int(1) DEFAULT 0,
179 `C_DeleteDate` date DEFAULT NULL,
180 `C_DeactivationDate` date DEFAULT NULL,
181 `C_CreationDate` date DEFAULT NULL,
182 `C_DeceasedDate` date DEFAULT NULL,
183 PRIMARY KEY (`C_ID`) USING BTREE
184) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
185CREATE TABLE `integration` (
186 `I_ID` int(11) NOT NULL AUTO_INCREMENT,
187 `I_UID` int(11) NOT NULL,
188 `I_LastIntegration` date NOT NULL DEFAULT '1970-01-01',
189 `I_SumFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
190 `I_OldHolidays` int(5) NOT NULL DEFAULT 0,
191 PRIMARY KEY (`I_ID`) USING BTREE
192) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
193CREATE TABLE `customerterms` (
194 `CT_ID` int(50) NOT NULL AUTO_INCREMENT,
195 `CT_CID` int(10) NOT NULL,
196 `CT_From` date NOT NULL,
197 `CT_To` date NOT NULL,
198 `CT_Hourly` decimal(20, 2) NOT NULL,
199 `CT_FixedTravelCompensation` decimal(20, 2) NOT NULL,
200 `CT_PerKMCompensationBase` decimal(20, 2) NOT NULL,
201 `CT_PerKMCompensationAdditional` decimal(20, 2) NOT NULL,
202 `CT_MaxTravelCompensationReport` decimal(20, 2) DEFAULT NULL,
203 `CT_MaxTravelCompensationMonthly` decimal(20, 2) DEFAULT NULL,
204 `CT_FixedSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
205 `CT_PercentageSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
206 `CT_FixedSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
207 `CT_PercentageSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
208 `CT_FixedHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
209 `CT_PercentageHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
210 `CT_SigReq` int(1) NOT NULL,
211 `CT_NighttimeFrom` time(0) NOT NULL DEFAULT '00:00:00',
212 `CT_NighttimeTo` time(0) NOT NULL DEFAULT '00:00:00',
213 `CT_FixedNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
214 `CT_PercentageNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
215 `CT_StackingSurcharge` tinyint(1) NOT NULL DEFAULT 0,
216 `CT_MinimumTime` int(11) NOT NULL DEFAULT 1,
217 `CT_TimeIncrement` int(11) NOT NULL DEFAULT 1,
218 PRIMARY KEY (`CT_ID`) USING BTREE
219) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
220+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
221| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
222+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
223| 1 | SIMPLE | reports | NULL | ALL | PRIMARY,R_From,R_To | NULL | NULL | NULL | 22249 | 29.76 | Using where; Using temporary; Using filesort |
224| 1 | SIMPLE | userreports | NULL | ALL | NULL | NULL | NULL | NULL | 21359 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
225| 1 | SIMPLE | users | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dbs671769.reports.R_UID | 1 | 100.00 | NULL |
226| 1 | SIMPLE | customers | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dbs671769.reports.R_CID | 1 | 100.00 | NULL |
227| 1 | SIMPLE | customerterms | NULL | ALL | NULL | NULL | NULL | NULL | 1429 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
228| 1 | SIMPLE | integration | NULL | ALL | NULL | NULL | NULL | NULL | 1134 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
229| 1 | SIMPLE | customerreports | NULL | ALL | NULL | NULL | NULL | NULL | 9078 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
230+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
231ALTER TABLE `userreports`
232 ADD INDEX `FK_UR_RID` (`UR_RID`);
233
234ALTER TABLE `customerterms`
235 ADD INDEX `FK_CT_CID` (`CT_CID`);
236
237ALTER TABLE `integration`
238 ADD INDEX `FK_I_UID` (`I_UID`);
239
240ALTER TABLE `customerreports`
241 ADD INDEX `FK_CR_RID` (`CR_RID`);
242-- this just retrieves some basic stats about size of each table used in your query
243SELECT TABLE_NAME, ENGINE, VERSION, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, INDEX_LENGTH
244FROM information_schema.TABLES
245WHERE TABLE_SCHEMA = 'dbs671769'
246AND TABLE_NAME IN('customerreports', 'customers', 'customerterms', 'integration', 'reports', 'userreports', 'users');
247QUESTION
Grabbing certain data from one object to another object
Asked 2022-Jan-08 at 16:38My goal is to grab certain values from database into curated_database, however I am basically stuck at adding multiple items into an object.
1var curated_database = {};
2
3var database = {
4 0: [{name: 'Micheal'},
5 {age: 45},
6 {education: 'BA'},
7 {income: 245000},
8 {occupation: 'director'}],
9 1: [{name: 'John'},
10 {age: 23},
11 {education: 'BA'},
12 {income: 60000},
13 {occupation: 'manager'}],
14 2: [{name: 'Judith'},
15 {age: 45},
16 {education: 'PhD'},
17 {income: 140000},
18 {occupation: 'professor'}],
19 3: [{name: 'Gill'},
20 {age: 28},
21 {education: 'MS'},
22 {income: 98000},
23 {occupation: 'scientist'}],
24 4: [{name: 'Dave'},
25 {age: 17},
26 {education: 'HS'},
27 {income: 30000},
28 {occupation: 'retail associate'}]
29};
30Goal is to grab similar certain information from the larger object
1var curated_database = {};
2
3var database = {
4 0: [{name: 'Micheal'},
5 {age: 45},
6 {education: 'BA'},
7 {income: 245000},
8 {occupation: 'director'}],
9 1: [{name: 'John'},
10 {age: 23},
11 {education: 'BA'},
12 {income: 60000},
13 {occupation: 'manager'}],
14 2: [{name: 'Judith'},
15 {age: 45},
16 {education: 'PhD'},
17 {income: 140000},
18 {occupation: 'professor'}],
19 3: [{name: 'Gill'},
20 {age: 28},
21 {education: 'MS'},
22 {income: 98000},
23 {occupation: 'scientist'}],
24 4: [{name: 'Dave'},
25 {age: 17},
26 {education: 'HS'},
27 {income: 30000},
28 {occupation: 'retail associate'}]
29};
30
31curated_database = {
320 : ['Micheal',245000,'director'],
331: ['John',245000,'manager'],
342: ['Judith',140000,'professor'],
353: ['Gill',98000,'scientist'],
364: ['Dave',30000,'retail associate']
37};
38My attempt
1var curated_database = {};
2
3var database = {
4 0: [{name: 'Micheal'},
5 {age: 45},
6 {education: 'BA'},
7 {income: 245000},
8 {occupation: 'director'}],
9 1: [{name: 'John'},
10 {age: 23},
11 {education: 'BA'},
12 {income: 60000},
13 {occupation: 'manager'}],
14 2: [{name: 'Judith'},
15 {age: 45},
16 {education: 'PhD'},
17 {income: 140000},
18 {occupation: 'professor'}],
19 3: [{name: 'Gill'},
20 {age: 28},
21 {education: 'MS'},
22 {income: 98000},
23 {occupation: 'scientist'}],
24 4: [{name: 'Dave'},
25 {age: 17},
26 {education: 'HS'},
27 {income: 30000},
28 {occupation: 'retail associate'}]
29};
30
31curated_database = {
320 : ['Micheal',245000,'director'],
331: ['John',245000,'manager'],
342: ['Judith',140000,'professor'],
353: ['Gill',98000,'scientist'],
364: ['Dave',30000,'retail associate']
37};
38for(data in database){
39 desired_contents = [0,3,4]
40 for(contents in desired_contents){
41 console.log(database[data][desired_contents[contents]]);
42 }
43 var k = database[data][0];
44 if (!currated_database[k.key]) {
45 currated_database[k.key] = [];
46 }
47 currated_database[k.key].push(k.val);
48}
49ANSWER
Answered 2022-Jan-08 at 16:35you can achieve it this way:
1var curated_database = {};
2
3var database = {
4 0: [{name: 'Micheal'},
5 {age: 45},
6 {education: 'BA'},
7 {income: 245000},
8 {occupation: 'director'}],
9 1: [{name: 'John'},
10 {age: 23},
11 {education: 'BA'},
12 {income: 60000},
13 {occupation: 'manager'}],
14 2: [{name: 'Judith'},
15 {age: 45},
16 {education: 'PhD'},
17 {income: 140000},
18 {occupation: 'professor'}],
19 3: [{name: 'Gill'},
20 {age: 28},
21 {education: 'MS'},
22 {income: 98000},
23 {occupation: 'scientist'}],
24 4: [{name: 'Dave'},
25 {age: 17},
26 {education: 'HS'},
27 {income: 30000},
28 {occupation: 'retail associate'}]
29};
30
31curated_database = {
320 : ['Micheal',245000,'director'],
331: ['John',245000,'manager'],
342: ['Judith',140000,'professor'],
353: ['Gill',98000,'scientist'],
364: ['Dave',30000,'retail associate']
37};
38for(data in database){
39 desired_contents = [0,3,4]
40 for(contents in desired_contents){
41 console.log(database[data][desired_contents[contents]]);
42 }
43 var k = database[data][0];
44 if (!currated_database[k.key]) {
45 currated_database[k.key] = [];
46 }
47 currated_database[k.key].push(k.val);
48}
49var curated_database = {};
50
51var database = {
52 0: [{name: 'Micheal'},
53 {age: 45},
54 {education: 'BA'},
55 {income: 245000},
56 {occupation: 'director'}],
57 1: [{name: 'John'},
58 {age: 23},
59 {education: 'BA'},
60 {income: 60000},
61 {occupation: 'manager'}],
62 2: [{name: 'Judith'},
63 {age: 45},
64 {education: 'PhD'},
65 {income: 140000},
66 {occupation: 'professor'}],
67 3: [{name: 'Gill'},
68 {age: 28},
69 {education: 'MS'},
70 {income: 98000},
71 {occupation: 'scientist'}],
72 4: [{name: 'Dave'},
73 {age: 17},
74 {education: 'HS'},
75 {income: 30000},
76 {occupation: 'retail associate'}]
77};
78
79
80
81var indexToExtract = new Set([0,3,4]);
82
83 var wantedLst = Object.values(database)
84 .map(lst => lst.filter((ob, idx) => indexToExtract.has(idx)))
85 .map((lst, idx) => curated_database[idx] = lst.map(ob => Object.values(ob)[0]))
86
87 console.log(curated_database);QUESTION
Manually construct factor from integer vector
Asked 2021-Dec-25 at 18:11I'm trying to understand how various objects in R are composed of atomic and generic vectors.
One can construct a data.frame out of a list by manually setting the attributes names, row.names, and class, see here.
I wonder how this might work for factors, which are internally represented as integer vectors. The solution I came up with is the following:
1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6But for some reason this still looks different than a properly constructed factor:
1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6> str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, "levels")= chr [1:3] "low" "medium" "high"
9> str(unclass(factor(c("low", "medium", "high"))))
10 int [1:3] 2 3 1
11 - attr(*, "levels")= chr [1:3] "high" "low" "medium"
12Am I missing something? (I know this probably should not be used in production code, instead it is for educational purposes only.)
ANSWER
Answered 2021-Dec-25 at 13:59The order matters.
1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6> str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, "levels")= chr [1:3] "low" "medium" "high"
9> str(unclass(factor(c("low", "medium", "high"))))
10 int [1:3] 2 3 1
11 - attr(*, "levels")= chr [1:3] "high" "low" "medium"
12f <- 1:3
13levels(f) <- c("low", "medium", "high") ## mark
14class(f) <- "factor"
15f
16# [ 1] low medium high
17# Levels: low medium high
18`levels<-` adds an attribute to the vector, instead of line ## mark you could also do
1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6> str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, "levels")= chr [1:3] "low" "medium" "high"
9> str(unclass(factor(c("low", "medium", "high"))))
10 int [1:3] 2 3 1
11 - attr(*, "levels")= chr [1:3] "high" "low" "medium"
12f <- 1:3
13levels(f) <- c("low", "medium", "high") ## mark
14class(f) <- "factor"
15f
16# [ 1] low medium high
17# Levels: low medium high
18attr(f, 'levels') <- c("low", "medium", "high")
19Here step by step what happens:
1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6> str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, "levels")= chr [1:3] "low" "medium" "high"
9> str(unclass(factor(c("low", "medium", "high"))))
10 int [1:3] 2 3 1
11 - attr(*, "levels")= chr [1:3] "high" "low" "medium"
12f <- 1:3
13levels(f) <- c("low", "medium", "high") ## mark
14class(f) <- "factor"
15f
16# [ 1] low medium high
17# Levels: low medium high
18attr(f, 'levels') <- c("low", "medium", "high")
19f <- 1:3
20attributes(f)
21# NULL
22
23levels(f) <- c("low", "medium", "high")
24attributes(f)
25# $levels
26# [1] "low" "medium" "high"
27
28class(f) <- "factor"
29attributes(f)
30# $levels
31# [1] "low" "medium" "high"
32#
33# $class
34# [1] "factor"
35Check with "automatic" factor generation.
1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6> str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, "levels")= chr [1:3] "low" "medium" "high"
9> str(unclass(factor(c("low", "medium", "high"))))
10 int [1:3] 2 3 1
11 - attr(*, "levels")= chr [1:3] "high" "low" "medium"
12f <- 1:3
13levels(f) <- c("low", "medium", "high") ## mark
14class(f) <- "factor"
15f
16# [ 1] low medium high
17# Levels: low medium high
18attr(f, 'levels') <- c("low", "medium", "high")
19f <- 1:3
20attributes(f)
21# NULL
22
23levels(f) <- c("low", "medium", "high")
24attributes(f)
25# $levels
26# [1] "low" "medium" "high"
27
28class(f) <- "factor"
29attributes(f)
30# $levels
31# [1] "low" "medium" "high"
32#
33# $class
34# [1] "factor"
35attributes(factor(1:3, labels=c("low", "medium", "high")))
36# $levels
37# [1] "low" "medium" "high"
38#
39# $class
40# [1] "factor"
41And, importantly
1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6> str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, "levels")= chr [1:3] "low" "medium" "high"
9> str(unclass(factor(c("low", "medium", "high"))))
10 int [1:3] 2 3 1
11 - attr(*, "levels")= chr [1:3] "high" "low" "medium"
12f <- 1:3
13levels(f) <- c("low", "medium", "high") ## mark
14class(f) <- "factor"
15f
16# [ 1] low medium high
17# Levels: low medium high
18attr(f, 'levels') <- c("low", "medium", "high")
19f <- 1:3
20attributes(f)
21# NULL
22
23levels(f) <- c("low", "medium", "high")
24attributes(f)
25# $levels
26# [1] "low" "medium" "high"
27
28class(f) <- "factor"
29attributes(f)
30# $levels
31# [1] "low" "medium" "high"
32#
33# $class