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.

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

Popular Libraries in Education

freeCodeCamp

by freeCodeCamp doticonjavascriptdoticon

star image 344419 doticonBSD-3-Clause

freeCodeCamp.org's open-source codebase and curriculum. Learn to code for free.

system-design-primer

by donnemartin doticonpythondoticon

star image 143449 doticonNOASSERTION

Learn how to design large-scale systems. Prep for the system design interview. Includes Anki flashcards.

three.js

by mrdoob doticonjavascriptdoticon

star image 80965 doticonMIT

JavaScript 3D Library.

keras

by keras-team doticonpythondoticon

star image 55007 doticonApache-2.0

Deep Learning for humans

awesome-machine-learning

by josephmisiti doticonpythondoticon

star image 51223 doticonNOASSERTION

A curated list of awesome Machine Learning frameworks, libraries and software.

scikit-learn

by scikit-learn doticonpythondoticon

star image 49728 doticonBSD-3-Clause

scikit-learn: machine learning in Python

Web-Dev-For-Beginners

by microsoft doticonjavascriptdoticon

star image 44908 doticonMIT

24 Lessons, 12 Weeks, Get Started as a Web Developer

faceswap

by deepfakes doticonpythondoticon

star image 38275 doticonGPL-3.0

Deepfakes Software For All

Deep-Learning-Papers-Reading-Roadmap

by floodsung doticonpythondoticon

star image 30347 doticon

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 doticonjavascriptdoticon

star image 44908 doticonMIT

24 Lessons, 12 Weeks, Get Started as a Web Developer

AI-Expert-Roadmap

by AMAI-GmbH doticonjavascriptdoticon

star image 13925 doticonMIT

Roadmap to becoming an Artificial Intelligence Expert in 2021

30-Days-Of-React

by Asabeneh doticonjavascriptdoticon

star image 8139 doticon

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 doticonpythondoticon

star image 6633 doticonMIT

DeepSpeed is a deep learning optimization library that makes distributed training and inference easy, efficient, and effective.

ultimate-python

by huangsam doticonpythondoticon

star image 2986 doticonMIT

Ultimate Python study guide for newcomers and professionals alike. :snake: :snake: :snake:

igel

by nidhaloff doticonpythondoticon

star image 2921 doticonMIT

a delightful machine learning tool that allows you to train, test, and use models without writing code

stylegan2-pytorch

by lucidrains doticonpythondoticon

star image 2738 doticonMIT

Simplest working implementation of Stylegan2, state of the art generative adversarial network, in Pytorch. Enabling everyone to experience disentanglement

gpt3-sandbox

by shreyashankar doticonjavascriptdoticon

star image 2398 doticonMIT

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 doticonpythondoticon

star image 2140 doticonApache-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

star icon4389

2

microsoft

46 Libraries

star icon68102

3

IBM

19 Libraries

star icon377

4

facebookresearch

13 Libraries

star icon34537

5

rimorsoft

11 Libraries

star icon177

6

moodleou

11 Libraries

star icon135

7

mozilla

10 Libraries

star icon1971

8

aws-samples

10 Libraries

star icon3506

9

UKPLab

10 Libraries

star icon126

10

Azure-Samples

10 Libraries

star icon131

1

128 Libraries

star icon4389

2

46 Libraries

star icon68102

3

19 Libraries

star icon377

4

13 Libraries

star icon34537

5

11 Libraries

star icon177

6

11 Libraries

star icon135

7

10 Libraries

star icon1971

8

10 Libraries

star icon3506

9

10 Libraries

star icon126

10

10 Libraries

star icon131

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.

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.

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 firm relies on data scientists to collect, evaluate, and extract business insights because there are many data-driven sectors. Data scientists are a crucial component of Google's business, helping the company understand how to make its cloud platforms more effective, better understand how its user-facing products are used, or simply use its own data to develop optimization techniques and provide answers to business questions.

Many companies have transformed the world as much as Google has. Check out the data science certification course to start upskilling. And continue reading to learn how to land a job with Google as a data scientist!


What Type of work environment does Google have?


Google takes pleasure in being "Googley," a phrase that describes company culture and the traits that make for a happy and effective workplace among its employees.

"Do you have an intellectual curiosity? Do you perform effectively in an atmosphere that needs to be clarified? Do you enjoy solving incredibly challenging problems? Stated Kyle Ewing, head of outreach and talent programs for Google's People Operations division. We know that person is the most successful type here.


Other "Googly" qualities, according to Google data scientists, include acting with the customer in mind, actively seeking ways to serve your team, taking the initiative outside of your core job duties, and participating in Google events like training or hiring.


What are the employment perks at Google like?


Regarding sprinkling employees with perks and advantages, Google is a role model for technological corporations. Google's additional employee perks include:


  • Full range of medical insurance, including access to doctors, physical therapists, chiropractors, and massage services nearby
  • Complementing 18–22 weeks of maternity leave
  • Charitable giving
  • Fertility support
  • Adoption support


 In addition to giving staff members very few reasons to leave its campuses since it takes care of their meals, healthcare, and wellness.


What's the Google interview procedure for Data Scientists like?


Like many other large tech firms, Google's hiring procedure for data scientists starts with a phone interview with a recruiter who asks detailed questions about the candidate's background, interest in the organization, and prior employment.

Recruiters determine whether a candidate meets the requirements for the position during this stage. These requirements include having an undergraduate or graduate degree in computer science, statistics, economics, mathematics, bioinformatics, physics, or a related subject. They will also evaluate a candidate's familiarity with advanced analytical techniques, operations research, and analytics.


In the final stage, which consists of a series of onsite interviews, candidates must respond to situational questions regarding Google's products, analyze statistical data, and provide business advice based on fictitious scenarios.


How do data scientists apply for internships at Google?


Google offers internships in three different areas: business, engineering, and technology, and BOLD, which stands for Build Opportunities for Leadership and Development and is a program for undergraduate seniors from backgrounds that have historically been underrepresented. During the summer, internships are frequently 12–14 weeks long, paid positions.


Candidates must excel during the application process on two fronts to land a sought-after Google internship: technical capabilities and "Googleyness." The latter concerns a candidate's "Googleyness" or whether they are the kind of person that other people want to work and hang out with. It pertains to their attitude and work ethic. Check out the data science course fees offered by Learnbay institute.


How does working as a data scientist at Google feel?


The product teams that a Google data scientist is a part of heavily influence their daily activities. All of Google's data scientists are proficient in Python, SQL, data processing, experiment design, conducting original research, dealing with large amounts of data, using statistical software, and creating data-driven hypotheses, but they all apply their knowledge to other parts of the company.

For instance, Artem Yankov, a Google data scientist, works on Google's forecasting team, where he utilizes data to help the firm predict how many customer service agents it should hire internationally to serve all of Google's products around the world and in various languages.


How to acquire the abilities a Google data scientist should have?


You need the ideal mindset, coding abilities, work experience, and education to be a data scientist at Google. It can be worthwhile to look at alternative paths to becoming a data scientist at Google, such as these transition routes if you lack the necessary training or professional experience. We've already discussed that if you don't have the correct personality type, working as a Google data scientist isn't the best choice for you.

In addition, check out the data scientist course fees at Learnbay data science training and you can take steps to sharpen your hard skills. The main focus of this section will be the best ways to acquire the coding abilities and database knowledge that are prerequisites for data scientist positions at Google.


Statistical terminology:


For the past ten years, "statistical NLP" has been most frequently used to describe non-symbolic and nonlogical NLP research. Any quantitative methods for automated language processing that use statistics are called statistical natural language processing (NLP).

R and Python are the two most widely used statistical languages, and they are both mentioned explicitly in job applications for Google Data Scientist positions. This means that if you want to educate yourself, you are not required to pay anything.


Database programming:


The backend is concerned with performance if the front end is about appearance. The kind of database queries that are written will determine this.

Database languages aka query languages are programming languages that developers use to define and retrieve data. These languages enable users to carry out operations within the database management system (DBMS), such as:


  • Limiting user access to data
  • Data definition and modification
  • Seeking information.


Simple interview techniques:


Don't forget to shake hands, follow up, make eye contact, and project confidence. But standard interview inquiries also include the following:


  • Pacing – You will go through five rounds of interviews for the position of Google Data Scientist, with only a lunch break in between. Remember that you have a long day ahead of you, and speak softly while drinking water as needed.
  • Friendliness – Your ability to work well with others and your suitability for the position is tested. People that enjoy working together are what Google is looking for. Strive to adopt an attitude of enjoyment and amusement for the interviewers.
  • Excellent listening – It is proven that people listen less effectively when they are anxious with your interview, practice effective active listening techniques. By doing so, you'll be able to better grasp the questions being asked of you, avoid misunderstandings, and establish a relationship with the interviewers. An uncommon talent is good listening.


Conclusion:


You'll be a rockstar if you have the necessary abilities, preparation, and passion. You have a one-way ticket to one of the top jobs in the world as long as you nail the fundamentals, adequately showcase your skills in the resume and interview process, and show how well you fit the culture. Further, look at our in-depth tutorial for the best data analytics course, which will walk you through each step required to become a professional data scientist and analyst.

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:36

I 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    ) 
28

ANSWER

Answered 2022-Mar-21 at 16:36

You 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

enter image description here

Source https://stackoverflow.com/questions/71560787

QUESTION

Canceling a Future vs stopping a Thread

Asked 2022-Mar-11 at 22:59

In 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:59

Cancelling 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.

Source https://stackoverflow.com/questions/71445135

QUESTION

How do you plot smooth components of different GAMs in same panel?

Asked 2022-Feb-19 at 12:38

I 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)
23

Plot of fit1 looks like this:

trt_fit1

And fit2 like this:

trt_fit2

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:55

If 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)

Source https://stackoverflow.com/questions/71173468

QUESTION

Coefficient plot - Increase gap between rows and alternative background colors in rows

Asked 2022-Jan-29 at 17:41

I 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?

my plot is here

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)
39

ANSWER

Answered 2022-Jan-29 at 09:56

You 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

enter image description here

However,

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
82

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()
110

enter image description here


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))
147

Source https://stackoverflow.com/questions/70895083

QUESTION

MySQL SQL Performance need some improvement

Asked 2022-Jan-13 at 01:35

I 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
46

So 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;
68

userreports (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;
86

customerreports (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;
109

users (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;
154

customers (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;
185

integration (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;
193

customerterms (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;
220

The 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...

RESULT of EXPLAIN

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+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
231

Is 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:03

Let'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`);
242

Please 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');
247

Source https://stackoverflow.com/questions/70685919

QUESTION

Grabbing certain data from one object to another object

Asked 2022-Jan-08 at 16:38

My 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};
30

Goal 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};
38

My 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}
49

ANSWER

Answered 2022-Jan-08 at 16:35

you 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);

Source https://stackoverflow.com/questions/70633937

QUESTION

Manually construct factor from integer vector

Asked 2021-Dec-25 at 18:11

I'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()
6

But 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"
12

Am 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:59

The 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&gt; f &lt;- 1:3
2&gt; class(f) &lt;- &quot;factor&quot;
3&gt; levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6&gt; str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, &quot;levels&quot;)= chr [1:3] &quot;low&quot; &quot;medium&quot; &quot;high&quot;
9&gt; str(unclass(factor(c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;))))
10 int [1:3] 2 3 1
11 - attr(*, &quot;levels&quot;)= chr [1:3] &quot;high&quot; &quot;low&quot; &quot;medium&quot;
12f &lt;- 1:3
13levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)  ## mark
14class(f) &lt;- &quot;factor&quot;
15f
16# [ 1] low    medium high  
17# Levels: low medium high
18attr(f, 'levels') &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
19

Here step by step what happens:

1&gt; f &lt;- 1:3
2&gt; class(f) &lt;- &quot;factor&quot;
3&gt; levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6&gt; str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, &quot;levels&quot;)= chr [1:3] &quot;low&quot; &quot;medium&quot; &quot;high&quot;
9&gt; str(unclass(factor(c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;))))
10 int [1:3] 2 3 1
11 - attr(*, &quot;levels&quot;)= chr [1:3] &quot;high&quot; &quot;low&quot; &quot;medium&quot;
12f &lt;- 1:3
13levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)  ## mark
14class(f) &lt;- &quot;factor&quot;
15f
16# [ 1] low    medium high  
17# Levels: low medium high
18attr(f, 'levels') &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
19f &lt;- 1:3
20attributes(f)
21# NULL
22
23levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
24attributes(f)
25# $levels
26# [1] &quot;low&quot;    &quot;medium&quot; &quot;high&quot;  
27
28class(f) &lt;- &quot;factor&quot;
29attributes(f)
30# $levels
31# [1] &quot;low&quot;    &quot;medium&quot; &quot;high&quot;  
32# 
33# $class
34# [1] &quot;factor&quot;
35

Check with "automatic" factor generation.

1&gt; f &lt;- 1:3
2&gt; class(f) &lt;- &quot;factor&quot;
3&gt; levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6&gt; str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, &quot;levels&quot;)= chr [1:3] &quot;low&quot; &quot;medium&quot; &quot;high&quot;
9&gt; str(unclass(factor(c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;))))
10 int [1:3] 2 3 1
11 - attr(*, &quot;levels&quot;)= chr [1:3] &quot;high&quot; &quot;low&quot; &quot;medium&quot;
12f &lt;- 1:3
13levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)  ## mark
14class(f) &lt;- &quot;factor&quot;
15f
16# [ 1] low    medium high  
17# Levels: low medium high
18attr(f, 'levels') &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
19f &lt;- 1:3
20attributes(f)
21# NULL
22
23levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
24attributes(f)
25# $levels
26# [1] &quot;low&quot;    &quot;medium&quot; &quot;high&quot;  
27
28class(f) &lt;- &quot;factor&quot;
29attributes(f)
30# $levels
31# [1] &quot;low&quot;    &quot;medium&quot; &quot;high&quot;  
32# 
33# $class
34# [1] &quot;factor&quot;
35attributes(factor(1:3, labels=c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)))
36# $levels
37# [1] &quot;low&quot;    &quot;medium&quot; &quot;high&quot;  
38# 
39# $class
40# [1] &quot;factor&quot;
41

And, importantly

1&gt; f &lt;- 1:3
2&gt; class(f) &lt;- &quot;factor&quot;
3&gt; levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6&gt; str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, &quot;levels&quot;)= chr [1:3] &quot;low&quot; &quot;medium&quot; &quot;high&quot;
9&gt; str(unclass(factor(c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;))))
10 int [1:3] 2 3 1
11 - attr(*, &quot;levels&quot;)= chr [1:3] &quot;high&quot; &quot;low&quot; &quot;medium&quot;
12f &lt;- 1:3
13levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)  ## mark
14class(f) &lt;- &quot;factor&quot;
15f
16# [ 1] low    medium high  
17# Levels: low medium high
18attr(f, 'levels') &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
19f &lt;- 1:3
20attributes(f)
21# NULL
22
23levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
24attributes(f)
25# $levels
26# [1] &quot;low&quot;    &quot;medium&quot; &quot;high&quot;  
27
28class(f) &lt;- &quot;factor&quot;
29attributes(f)
30# $levels
31# [1] &quot;low&quot;    &quot;medium&quot; &quot;high&quot;  
32# 
33# $class
34# [1] &quot;factor&quot;
35attributes(factor(1:3, labels=c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)))
36# $levels
37# [1] &quot;low&quot;    &quot;medium&quot; &quot;high&quot;  
38# 
39# $class
40# [1] &quot;factor&quot;
41stopifnot(all.equal(unclass(f), 
42                    unclass(factor(1:3, labels=c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)))))
43

Note 1, the order of f doesn't matter. Levels of f are identified by their index, and element n of the assigned levels vector becomes first level, i.e. `1`='low', `2`='medium', `3`='high' in following example.

1&gt; f &lt;- 1:3
2&gt; class(f) &lt;- &quot;factor&quot;
3&gt; levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6&gt; str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, &quot;levels&quot;)= chr [1:3] &quot;low&quot; &quot;medium&quot; &quot;high&quot;
9&gt; str(unclass(factor(c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;))))
10 int [1:3] 2 3 1
11 - attr(*, &quot;levels&quot;)= chr [1:3] &quot;high&quot; &quot;low&quot; &quot;medium&quot;
12f &lt;- 1:3
13levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)  ## mark
14class(f) &lt;- &quot;factor&quot;
15f
16# [ 1] low    medium high  
17# Levels: low medium high
18attr(f, 'levels') &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
19f &lt;- 1:3
20attributes(f)
21# NULL
22
23levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
24attributes(f)
25# $levels
26# [1] &quot;low&quot;    &quot;medium&quot; &quot;high&quot;  
27
28class(f) &lt;- &quot;factor&quot;
29attributes(f)
30# $levels
31# [1] &quot;low&quot;    &quot;medium&quot; &quot;high&quot;  
32# 
33# $class
34# [1] &quot;factor&quot;
35attributes(factor(1:3, labels=c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)))
36# $levels
37# [1] &quot;low&quot;    &quot;medium&quot; &quot;high&quot;  
38# 
39# $class
40# [1] &quot;factor&quot;
41stopifnot(all.equal(unclass(f), 
42                    unclass(factor(1:3, labels=c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)))))
43f &lt;- 3:1
44levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
45class(f) &lt;- 'factor'
46f
47# [1] high   medium low   
48# Levels: low medium high
49

Note 2, that this only works if f starts with 1 and also the levels increase by 1, because a factor is actually a labeled integer structure.

1&gt; f &lt;- 1:3
2&gt; class(f) &lt;- &quot;factor&quot;
3&gt; levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6&gt; str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, &quot;levels&quot;)= chr [1:3] &quot;low&quot; &quot;medium&quot; &quot;high&quot;
9&gt; str(unclass(factor(c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;))))
10 int [1:3] 2 3 1
11 - attr(*, &quot;levels&quot;)= chr [1:3] &quot;high&quot; &quot;low&quot; &quot;medium&quot;
12f &lt;- 1:3
13levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)  ## mark
14class(f) &lt;- &quot;factor&quot;
15f
16# [ 1] low    medium high  
17# Levels: low medium high
18attr(f, 'levels') &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
19f &lt;- 1:3
20attributes(f)
21# NULL
22
23levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
24attributes(f)
25# $levels
26# [1] &quot;low&quot;    &quot;medium&quot; &quot;high&quot;  
27
28class(f) &lt;- &quot;factor&quot;
29attributes(f)
30# $levels
31# [1] &quot;low&quot;    &quot;medium&quot; &quot;high&quot;  
32# 
33# $class
34# [1] &quot;factor&quot;
35attributes(factor(1:3, labels=c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)))
36# $levels
37# [1] &quot;low&quot;    &quot;medium&quot; &quot;high&quot;  
38# 
39# $class
40# [1] &quot;factor&quot;
41stopifnot(all.equal(unclass(f), 
42                    unclass(factor(1:3, labels=c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)))))
43f &lt;- 3:1
44levels(f) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
45class(f) &lt;- 'factor'
46f
47# [1] high   medium low   
48# Levels: low medium high
49g &lt;- 2:4
50levels(g) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
51class(g) &lt;- 'factor'
52g
53# Error in as.character.factor(x) : malformed factor
54
55h &lt;- c(1, 3, 4)
56levels(h) &lt;- c(&quot;low&quot;, &quot;medium&quot;, &quot;high&quot;)
57class(h) &lt;- 'factor'
58# Error in class(h) &lt;- &quot;factor&quot; : 
59#   adding class &quot;factor&quot; to an invalid object
60

Source https://stackoverflow.com/questions/70480377

QUESTION

react-native-render-html: &quot;You seem to update the X prop of the Y component in short periods of time...&quot;

Asked 2021-Dec-02 at 14:56

Notes: I'm the author of react-native-render-html. This question is for educational purposes, in compliance with StackOverflow policy.

I am rendering RenderHtml component in a WebDisplay component like so:

1import * as React from 'react';
2import {ScrollView, StyleSheet, Text, useWindowDimensions} from 'react-native';
3import RenderHtml from 'react-native-render-html';
4
5const html = '&lt;div&gt;Hello world!&lt;/div&gt;';
6
7function WebDisplay({html}) {
8  const {width: contentWidth} = useWindowDimensions();
9  const tagsStyles = {
10    a: {
11      textDecorationLine: 'none',
12    },
13  };
14  return (
15    &lt;RenderHtml
16      contentWidth={contentWidth}
17      source={{html}}
18      tagsStyles={tagsStyles}
19    /&gt;
20  );
21}
22
23export default function App() {
24  const [isToastVisible, setIsToastVisible] = React.useState(false);
25  React.useEffect(function flipToast() {
26    const timeout = setTimeout(() =&gt; {
27      setIsToastVisible((v) =&gt; !v);
28    }, 30);
29    return () =&gt; {
30      clearTimeout(timeout);
31    };
32  });
33  return (
34    &lt;ScrollView contentContainerStyle={styles.container}&gt;
35      &lt;WebDisplay html={html} /&gt;
36      {isToastVisible &amp;&amp; &lt;Text&gt;This is a toast!&lt;/Text&gt;}
37    &lt;/ScrollView&gt;
38  );
39}
40
41const styles = StyleSheet.create({
42  container: {
43    flexGrow: 1,
44  },
45});
46

Why am I getting this warning, what does it mean and how to fix it?

ANSWER

Answered 2021-Dec-02 at 14:56

Usually, this warning shows up when:

  • The parent (currently App) component updates very often and causes WebDisplay component to re-render. In the provided snippet, every 30 milliseconds;
  • At least one prop passed to RenderHTML is referentially unstable between each re-render. In the provided snippet, tagsStyles reference changes on every re-render.

Notice that between every update of the App component caused by the useEffect hook, the html prop passed to WebDisplay is unchanged. But WebDisplay is re-rendered anyway because it is not "pure".

For this very reason, a pretty straightforward solution is to wrap WebDisplay in React.memo:

1import * as React from 'react';
2import {ScrollView, StyleSheet, Text, useWindowDimensions} from 'react-native';
3import RenderHtml from 'react-native-render-html';
4
5const html = '&lt;div&gt;Hello world!&lt;/div&gt;';
6
7function WebDisplay({html}) {
8  const {width: contentWidth} = useWindowDimensions();
9  const tagsStyles = {
10    a: {
11      textDecorationLine: 'none',
12    },
13  };
14  return (
15    &lt;RenderHtml
16      contentWidth={contentWidth}
17      source={{html}}
18      tagsStyles={tagsStyles}
19    /&gt;
20  );
21}
22
23export default function App() {
24  const [isToastVisible, setIsToastVisible] = React.useState(false);
25  React.useEffect(function flipToast() {
26    const timeout = setTimeout(() =&gt; {
27      setIsToastVisible((v) =&gt; !v);
28    }, 30);
29    return () =&gt; {
30      clearTimeout(timeout);
31    };
32  });
33  return (
34    &lt;ScrollView contentContainerStyle={styles.container}&gt;
35      &lt;WebDisplay html={html} /&gt;
36      {isToastVisible &amp;&amp; &lt;Text&gt;This is a toast!&lt;/Text&gt;}
37    &lt;/ScrollView&gt;
38  );
39}
40
41const styles = StyleSheet.create({
42  container: {
43    flexGrow: 1,
44  },
45});
46const WebDisplay = React.memo(function WebDisplay({html}) {
47  const {width: contentWidth} = useWindowDimensions();
48  const tagsStyles = {
49    a: {
50      textDecorationLine: 'none',
51    },
52  };
53  return (
54    &lt;RenderHtml
55      contentWidth={contentWidth}
56      source={{html}}
57      tagsStyles={tagsStyles}
58    /&gt;
59  );
60});
61

You can learn more about this technique in the official documentation.

Note: "pure" terminology comes from React PureComponent class. I think React.pure would have been less ambiguous than React.memo because we now use "memoization" to designate optimization techniques applying to both components and props, which can be confusing. I prefer to preserve "pure" terminology for components, and "memoization" for props.

Another solution is to move tagsStyles outside the WebDisplay function body. In that case, it will be instantiated only once and become referentially stable. Because RenderHtml itself is pure, it won't re-render its own subcomponents and the warning should disappear:

1import * as React from 'react';
2import {ScrollView, StyleSheet, Text, useWindowDimensions} from 'react-native';
3import RenderHtml from 'react-native-render-html';
4
5const html = '&lt;div&gt;Hello world!&lt;/div&gt;';
6
7function WebDisplay({html}) {
8  const {width: contentWidth} = useWindowDimensions();
9  const tagsStyles = {
10    a: {
11      textDecorationLine: 'none',
12    },
13  };
14  return (
15    &lt;RenderHtml
16      contentWidth={contentWidth}
17      source={{html}}
18      tagsStyles={tagsStyles}
19    /&gt;
20  );
21}
22
23export default function App() {
24  const [isToastVisible, setIsToastVisible] = React.useState(false);
25  React.useEffect(function flipToast() {
26    const timeout = setTimeout(() =&gt; {
27      setIsToastVisible((v) =&gt; !v);
28    }, 30);
29    return () =&gt; {
30      clearTimeout(timeout);
31    };
32  });
33  return (
34    &lt;ScrollView contentContainerStyle={styles.container}&gt;
35      &lt;WebDisplay html={html} /&gt;
36      {isToastVisible &amp;&amp; &lt;Text&gt;This is a toast!&lt;/Text&gt;}
37    &lt;/ScrollView&gt;
38  );
39}
40
41const styles = StyleSheet.create({
42  container: {
43    flexGrow: 1,
44  },
45});
46const WebDisplay = React.memo(function WebDisplay({html}) {
47  const {width: contentWidth} = useWindowDimensions();
48  const tagsStyles = {
49    a: {
50      textDecorationLine: 'none',
51    },
52  };
53  return (
54    &lt;RenderHtml
55      contentWidth={contentWidth}
56      source={{html}}
57      tagsStyles={tagsStyles}
58    /&gt;
59  );
60});
61const tagsStyles = {
62  a: {
63    textDecorationLine: 'none',
64  },
65};
66
67function WebDisplay({html}) {
68  const {width: contentWidth} = useWindowDimensions();
69  return (
70    &lt;RenderHtml
71      contentWidth={contentWidth}
72      source={{html}}
73      tagsStyles={tagsStyles}
74    /&gt;
75  );
76};
77

Note: It's a great habit to move any props which does not depend on any other state or prop outside of the body of a functional component.

Finally, if your use case involves tagsStyles depending on a prop, you could memoize it with React.useMemo:

1import * as React from 'react';
2import {ScrollView, StyleSheet, Text, useWindowDimensions} from 'react-native';
3import RenderHtml from 'react-native-render-html';
4
5const html = '&lt;div&gt;Hello world!&lt;/div&gt;';
6
7function WebDisplay({html}) {
8  const {width: contentWidth} = useWindowDimensions();
9  const tagsStyles = {
10    a: {
11      textDecorationLine: 'none',
12    },
13  };
14  return (
15    &lt;RenderHtml
16      contentWidth={contentWidth}
17      source={{html}}
18      tagsStyles={tagsStyles}
19    /&gt;
20  );
21}
22
23export default function App() {
24  const [isToastVisible, setIsToastVisible] = React.useState(false);
25  React.useEffect(function flipToast() {
26    const timeout = setTimeout(() =&gt; {
27      setIsToastVisible((v) =&gt; !v);
28    }, 30);
29    return () =&gt; {
30      clearTimeout(timeout);
31    };
32  });
33  return (
34    &lt;ScrollView contentContainerStyle={styles.container}&gt;
35      &lt;WebDisplay html={html} /&gt;
36      {isToastVisible &amp;&amp; &lt;Text&gt;This is a toast!&lt;/Text&gt;}
37    &lt;/ScrollView&gt;
38  );
39}
40
41const styles = StyleSheet.create({
42  container: {
43    flexGrow: 1,
44  },
45});
46const WebDisplay = React.memo(function WebDisplay({html}) {
47  const {width: contentWidth} = useWindowDimensions();
48  const tagsStyles = {
49    a: {
50      textDecorationLine: 'none',
51    },
52  };
53  return (
54    &lt;RenderHtml
55      contentWidth={contentWidth}
56      source={{html}}
57      tagsStyles={tagsStyles}
58    /&gt;
59  );
60});
61const tagsStyles = {
62  a: {
63    textDecorationLine: 'none',
64  },
65};
66
67function WebDisplay({html}) {
68  const {width: contentWidth} = useWindowDimensions();
69  return (
70    &lt;RenderHtml
71      contentWidth={contentWidth}
72      source={{html}}
73      tagsStyles={tagsStyles}
74    /&gt;
75  );
76};
77function WebDisplay({html, anchorColor}) {
78  const {width: contentWidth} = useWindowDimensions();
79  const tagsStyles = React.useMemo(
80    () =&gt; ({
81      a: {
82        color: anchorColor,
83        textDecorationLine: 'none',
84      },
85    }),
86    [anchorColor],
87  );
88  return (
89    &lt;RenderHtml
90      contentWidth={contentWidth}
91      source={{html}}
92      tagsStyles={tagsStyles}
93    /&gt;
94  );
95}
96

More information on this hook in the official documentation.

If you still don't have a clear mental model of how component updates work in React, I suggest those readings to enhance your skills:

Source https://stackoverflow.com/questions/68966120

QUESTION

Reproduce a complex table with double headesrs

Asked 2021-Nov-23 at 06:57

I would like to create the following table

enter image description here

Where

1Alzheimer’s Disease Assessment Scale-Cognitive  = ADAS_CogT0
2Rey-Osterrieth Complex Figure Test immediate recall = ROCF_IT0
3Copy of Rey-Osterrieth Complex Figure test   = ROCF_CT0
4Rey-Osterrieth Complex Figure Test delayed recall   = ROCf_RT0
5Phonemic verbal fluency = PVF_T0
6Semantic verbal fluency = SVF_T0
7

I have started manipulating data as follows:

1Alzheimer’s Disease Assessment Scale-Cognitive  = ADAS_CogT0
2Rey-Osterrieth Complex Figure Test immediate recall = ROCF_IT0
3Copy of Rey-Osterrieth Complex Figure test   = ROCF_CT0
4Rey-Osterrieth Complex Figure Test delayed recall   = ROCf_RT0
5Phonemic verbal fluency = PVF_T0
6Semantic verbal fluency = SVF_T0
7library(tidyverse)
8library(readxl)
9library(rms)
10library(lme4)
11library(flextable)
12library(gtsummary)
13library(data.table)
14library(plotrix)
15library(purrr)
16
17out &lt;db %&gt;% 
18  mutate(GROUP = fct_recode(GROUP, CONTROL = &quot;CONTROLLO&quot;)) %&gt;%
19  to_long(keys  = c(&quot;tests0&quot;, &quot;tests7&quot;),
20          values =c(&quot;score0&quot;, &quot;score7&quot;),
21          grep(&quot;T0$&quot;, names(.), value = TRUE), 
22          grep(&quot;T7$&quot;, names(.), value = TRUE)) %&gt;%
23  as_tibble %&gt;%
24  mutate(Gender = factor(Gender, levels = c(1, 0), labels = c(&quot;M&quot;, &quot;F&quot;)),
25         across(c(tests0, tests7), factor))
26  
27out1 &lt;- out %&gt;%   
28  group_by(tests0, GROUP) %&gt;%
29  filter(GROUP == 'TRAINING') %&gt;% 
30  summarise(mean0 = mean(score0, na.rm = T),
31            stderr0 = std.error(score0, na.rm = T), 
32            mean7 = mean(score7, na.rm = T), 
33            stederr7 = std.error(score7, na.rm = T),
34            diff.std.mean = t.test(score0, score7, paired = T)$estimate, 
35            p.value = t.test(score0, score7, paired = T)$p.value  
36  ) %&gt;% 
37  ungroup()
38
39out2 &lt;-  out %&gt;% 
40  group_by(tests0, GROUP) %&gt;% 
41  filter(GROUP == 'CONTROL') %&gt;% 
42  summarise(mean0 = mean(score0, na.rm = T),
43            stderr0 = std.error(score0, na.rm = T), 
44            mean7 = mean(score7, na.rm = T), 
45            stederr7 = std.error(score7, na.rm = T),
46            diff.std.mean = t.test(score0, score7, paired = T)$estimate, 
47            p.value = t.test(score0, score7, paired = T)$p.value  
48  ) %&gt;% 
49  ungroup()
50  
51out3 &lt;- out1 %&gt;%
52  bind_cols(out2) %&gt;% 
53  select(., -'tests0...9') %&gt;%
54  flextable()
55

obtaing this results

enter image description here

I got stuck at this point, where I am a bit confuse as to create the final column for the between-group differences that are supposed to contain

mean difference of the of the two MEAN DIFFERENCE COLUMNS you can see in the blank picture and I don't how to enter it into the code. Moreover I am not able create the two headers referred to:

1Alzheimer’s Disease Assessment Scale-Cognitive  = ADAS_CogT0
2Rey-Osterrieth Complex Figure Test immediate recall = ROCF_IT0
3Copy of Rey-Osterrieth Complex Figure test   = ROCF_CT0
4Rey-Osterrieth Complex Figure Test delayed recall   = ROCf_RT0
5Phonemic verbal fluency = PVF_T0
6Semantic verbal fluency = SVF_T0
7library(tidyverse)
8library(readxl)
9library(rms)
10library(lme4)
11library(flextable)
12library(gtsummary)
13library(data.table)
14library(plotrix)
15library(purrr)
16
17out &lt;db %&gt;% 
18  mutate(GROUP = fct_recode(GROUP, CONTROL = &quot;CONTROLLO&quot;)) %&gt;%
19  to_long(keys  = c(&quot;tests0&quot;, &quot;tests7&quot;),
20          values =c(&quot;score0&quot;, &quot;score7&quot;),
21          grep(&quot;T0$&quot;, names(.), value = TRUE), 
22          grep(&quot;T7$&quot;, names(.), value = TRUE)) %&gt;%
23  as_tibble %&gt;%
24  mutate(Gender = factor(Gender, levels = c(1, 0), labels = c(&quot;M&quot;, &quot;F&quot;)),
25         across(c(tests0, tests7), factor))
26  
27out1 &lt;- out %&gt;%   
28  group_by(tests0, GROUP) %&gt;%
29  filter(GROUP == 'TRAINING') %&gt;% 
30  summarise(mean0 = mean(score0, na.rm = T),
31            stderr0 = std.error(score0, na.rm = T), 
32            mean7 = mean(score7, na.rm = T), 
33            stederr7 = std.error(score7, na.rm = T),
34            diff.std.mean = t.test(score0, score7, paired = T)$estimate, 
35            p.value = t.test(score0, score7, paired = T)$p.value  
36  ) %&gt;% 
37  ungroup()
38
39out2 &lt;-  out %&gt;% 
40  group_by(tests0, GROUP) %&gt;% 
41  filter(GROUP == 'CONTROL') %&gt;% 
42  summarise(mean0 = mean(score0, na.rm = T),
43            stderr0 = std.error(score0, na.rm = T), 
44            mean7 = mean(score7, na.rm = T), 
45            stederr7 = std.error(score7, na.rm = T),
46            diff.std.mean = t.test(score0, score7, paired = T)$estimate, 
47            p.value = t.test(score0, score7, paired = T)$p.value  
48  ) %&gt;% 
49  ungroup()
50  
51out3 &lt;- out1 %&gt;%
52  bind_cols(out2) %&gt;% 
53  select(., -'tests0...9') %&gt;%
54  flextable()
55TRAINING and CONTROL groups 
56
57WITHIN AND BETWEEN GROUPS
58

here the dataset

1Alzheimer’s Disease Assessment Scale-Cognitive  = ADAS_CogT0
2Rey-Osterrieth Complex Figure Test immediate recall = ROCF_IT0
3Copy of Rey-Osterrieth Complex Figure test   = ROCF_CT0
4Rey-Osterrieth Complex Figure Test delayed recall   = ROCf_RT0
5Phonemic verbal fluency = PVF_T0
6Semantic verbal fluency = SVF_T0
7library(tidyverse)
8library(readxl)
9library(rms)
10library(lme4)
11library(flextable)
12library(gtsummary)
13library(data.table)
14library(plotrix)
15library(purrr)
16
17out &lt;db %&gt;% 
18  mutate(GROUP = fct_recode(GROUP, CONTROL = &quot;CONTROLLO&quot;)) %&gt;%
19  to_long(keys  = c(&quot;tests0&quot;, &quot;tests7&quot;),
20          values =c(&quot;score0&quot;, &quot;score7&quot;),
21          grep(&quot;T0$&quot;, names(.), value = TRUE), 
22          grep(&quot;T7$&quot;, names(.), value = TRUE)) %&gt;%
23  as_tibble %&gt;%
24  mutate(Gender = factor(Gender, levels = c(1, 0), labels = c(&quot;M&quot;, &quot;F&quot;)),
25         across(c(tests0, tests7), factor))
26  
27out1 &lt;- out %&gt;%   
28  group_by(tests0, GROUP) %&gt;%
29  filter(GROUP == 'TRAINING') %&gt;% 
30  summarise(mean0 = mean(score0, na.rm = T),
31            stderr0 = std.error(score0, na.rm = T), 
32            mean7 = mean(score7, na.rm = T), 
33            stederr7 = std.error(score7, na.rm = T),
34            diff.std.mean = t.test(score0, score7, paired = T)$estimate, 
35            p.value = t.test(score0, score7, paired = T)$p.value  
36  ) %&gt;% 
37  ungroup()
38
39out2 &lt;-  out %&gt;% 
40  group_by(tests0, GROUP) %&gt;% 
41  filter(GROUP == 'CONTROL') %&gt;% 
42  summarise(mean0 = mean(score0, na.rm = T),
43            stderr0 = std.error(score0, na.rm = T), 
44            mean7 = mean(score7, na.rm = T), 
45            stederr7 = std.error(score7, na.rm = T),
46            diff.std.mean = t.test(score0, score7, paired = T)$estimate, 
47            p.value = t.test(score0, score7, paired = T)$p.value  
48  ) %&gt;% 
49  ungroup()
50  
51out3 &lt;- out1 %&gt;%
52  bind_cols(out2) %&gt;% 
53  select(., -'tests0...9') %&gt;%
54  flextable()
55TRAINING and CONTROL groups 
56
57WITHIN AND BETWEEN GROUPS
58dput(head(db, 100))
59structure(list(ID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 
6013, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 
6129, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 
6245, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 
6361, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 
6477, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 
6593, 94, 95, 96, 97, 98, 99, 100), GROUP = c(&quot;TRAINING&quot;, &quot;TRAINING&quot;, 
66&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
67&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
68&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
69&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
70&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
71&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
72&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
73&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
74&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;CONTROLLO&quot;, 
75&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
76&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
77&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
78&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
79&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
80&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
81&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
82&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
83&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;), Gender = c(1, 
840, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 1, 0, 1, 1, 
850, 0, 0, 0, 1, 0, 0, 1, 1, 1, 1, 1, 1, 0, 1, 1, 1, 0, 1, 1, 1, 
860, 0, 1, 0, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 
871, 1, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 0, 0, 0, 1, 1, 
880, 0, 1, 1, 1, 1, 1, 1, 0, 1, 1, 1, 1, 1, 0), Age = c(74, 76, 
8981, 74, 69, 72, 75, 83, 78, 72, 82, 68, 72, 72, 73, 80, 69, 72, 
9070, 80, 75, 80, 78, 74, 82, 74, 80, 82, 78, 81, 66, 71, 70, 79, 
9178, 73, 72, 77, 77, 71, 83, 74, 70, 71, 77, 69, 67, 64, 79, 71, 
9277, 77, 73, 67, 68, 79, 81, 67, 84, 75, 80, 73, 68, 74, 77, 79, 
9379, 72, 73, 78, 76, 78, 77, 74, 78, 77, 77, 82, 77, 70, 77, 81, 
9479, 75, 74, 78, 69, 77, 73, 77, 70, 79, 70, 72, 77, 72, 71, 71, 
9573, 81), Education = c(18, 4, 8, 5, 8, 11, 5, 5, 4, 8, 8, 12, 
965, 18, 13, 5, 13, 13, 5, 5, 13, 5, 3, 8, 17, 5, 8, 5, 5, 8, 17, 
978, 18, 18, 13, 13, 13, 13, 15, 17, 8, 5, 5, 13, 8, 5, 11, 13, 
988, 8, 8, 5, 13, 8, 5, 17, 8, 12, 13, 5, 8, 8, 8, 5, 3, 8, 18, 
995, 8, 13, 8, 5, 17, 8, 5, 17, 5, 8, 11, 8, 8, 5, 12, 3, 8, 8, 
1008, 13, 5, 5, 8, 8, 13, 5, 5, 8, 13, 5, 8, 12), ADAS_CogT0 = c(14.66, 
10115.33, 17.33, 19, 7.66, 12.6, 18.67, 14.99, 17.99, 17.33, 13.66, 
10216.99, 10.66, 9.66, 14.99, 15.66, 13.33, 4.33, 14.33, 15.99, 
10316.33, 10.66, 14.66, 10.66, 19.33, 17.66, 15.99, 20.66, 20.6, 
10417, 10.33, 6.33, 6.66, 19.99, 13.33, 24.33, 12.33, 10.33, 12.33, 
1059.66, 10.99, 13.99, 23, 6.32, 11.32, 13.99, 14.66, 8.99, 14.33, 
1069.99, 7.33, 15.66, 14, 7.99, 23.32, 14.66, 9.99, 5.66, 6.99, 
10711.66, 10.33, 6.99, 19.32, NA, 10, 17.66, 13.66, 10.32, NA, NA, 
1088.66, 9, 6.99, 14.99, 9.66, 13.66, 15.32, 12, 14, 13.66, 11.99, 
10915.66, 16, 15, 16.99, 20, 11, 7.99, 8.33, 8.32, 14.99, 18.66, 
11010.33, 11.99, 9.32, 17, 14.33, 14.66, 16.6, 9.99), ROCF_CT0 = c(32, 
11113.5, 34.7, 26.1, 35.4, 24.6, 28.9, 34.1, 12.1, 28.1, 22.7, 31.6, 
11230.4, 34.4, 37.2, 38.1, 18.6, 26.2, 34.8, 37.1, 26.2, 35.1, 37.1, 
11336, 11.2, 17.4, 32.7, 28.4, 21.1, 26.7, 31.9, 33.4, 27.4, 32.7, 
11433.9, 37.2, 28.2, 34.2, 29.7, 30, 16.2, 37.4, 34.8, 36.6, 33, 
11530.8, 34.6, 35.1, 29.7, 36.8, 28, 34.4, 33.7, 28.9, 31.8, 31.7, 
11610.2, 27.1, 36.8, 22.4, 35.7, 28, 30.9, 35.4, 26.9, 29.7, 26.7, 
11728.8, 32, 37.9, 27, 18.6, 33, 38, 25.1, 31, 27.1, 26.7, 37, 30.4, 
11837, 30.1, 36.9, 24.4, 34, 6.7, 20.4, 30.2, 31.1, 33.4, 17.4, 
11923.7, 30.6, 28.3, 19.4, 33.4, 31.6, 39.8, 27, 33.9), ROCF_IT0 = c(3.7, 
1207.6, 14.9, 13.6, 23.4, 4, 8.1, 23.9, 10.1, 7, 10, 9.2, 15.6, 
12110.8, 6.6, 13.6, 4, 15.3, 7.4, 0, 8.6, 22.9, 11.6, 10.1, 6.8, 
12212.1, 12.9, 0, 10.6, 5.9, 14.8, 10.9, 10.6, 10.6, 7.5, 7.3, 4.1, 
1233.1, 7.6, 13.4, 10.9, 19, 3.4, 11.7, 14.6, 9.4, 16.7, 15.7, 14.5, 
12412.5, 15.1, 22.6, 9.1, 15.5, 11, 6.1, 11.5, 5.6, 17.2, 18.1, 
12523.5, 15.6, 7.7, 13.5, 14.5, 11.9, 10.3, 11.5, 12.6, 19, 11.9, 
12615.4, 17.7, 8.4, 13.4, 5.2, 11.1, 16, 10.9, 8, 22.6, 13.6, 12.2, 
1276.5, 13.4, 11.5, 8, 18.1, 10, 11, 5.9, 14.9, 12, 11.4, 17, 5.9, 
12815, 10.9, 8.9, 15.7), ROCF_RT0 = c(3.9, 7.8, 16.3, 13.4, 24.1, 
1294.7, 7.8, 20.9, 9.8, 5.1, 9.8, 8.2, 16.3, 11.3, 5.9, 14.8, 4.7, 
13014.4, 6.7, 0, 4.9, 23.3, 14.8, 11.7, 6.4, 10.3, 14.3, 0, 10.8, 
1316.3, 15.4, 10.1, 5.3, 12, 6.9, 5.4, 4.4, 4.4, 7, 10.9, 11.9, 
13217.3, 3.7, 12.2, 6.7, 14.7, 16.2, 7.8, 10.8, 10.2, 16.7, 22.8, 
1339.1, 15.7, 10.7, 4, 8.8, 6.8, 14.6, 14.5, 24.8, 13.7, 0, 10.3, 
13417.3, 11.3, 8, 13.2, 15.2, 18.4, 4.3, 12.8, 18.9, 4.3, 11.8, 
1354.9, 11.8, 13.3, 10.3, 7.1, 23.7, 15.8, 16.4, 6.8, 20.3, 11.3, 
1367.6, 19.9, 10.8, 12.5, 5.6, 11.3, 11.7, 13.5, 12.3, 5.1, 14.2, 
1378.7, 12.3, 16.4), PVF_T0 = c(41.3, 35.4, 16.7, 28.4, 46.9, 33.6, 
13839.4, 27.4, 17.4, 37.9, 31.7, 37.6, 33.4, 21.5, 49.5, 40.4, 10.6, 
13940.5, 30.6, 28.4, 29.5, 22.4, 45.4, 29.7, 17.3, 39.4, 31.7, 24.4, 
14038.4, 22.7, 6.8, 19.9, 56.5, 35.3, 43.5, 32.5, 28.5, 38.5, 22.3, 
14132.3, 30.7, 23.4, 18.6, 46.6, 36.7, 35.6, 38.6, 16.9, 38.7, 37.6, 
14239.7, 34.4, 27.5, 16.2, 32.6, 48.3, 26.7, 49.9, 40.5, 34.4, 37.7, 
14339.7, 23.2, 19.4, 21.4, 33.7, 11.3, 41.6, 23.7, 31.5, 28.7, 34.4, 
14433.3, 27.7, 23.4, 32.3, 49.4, 35.7, 26.7, 31.9, 37.7, 35.4, 20.5, 
14528.4, 14.7, 30.7, 35.9, 16.5, 27.4, 49.4, 37.9, 27.7, 41.6, 31.6, 
14655.4, 42.9, 21.6, 25.6, 26.7, 43.5), SVF_T0 = c(40, 37, 30, 28, 
14749, 27, 34, 35, 29, 33, 20, 18, 36, 47, 43, 39, 36, 38, 48, 31, 
14825, 34, 31, 33, 24, 30, 45, 20, 33, 34, 29, 42, 40, 37, 37, 25, 
14928, 42, 19, 32, 37, 35, 20, 43, 33, 40, 40, 42, 27, 35, 29, 39, 
15031, 48, 41, 27, 32, 45, 39, 40, 36, 38, 27, 32, 47, 36, 24, 40, 
15138, 29, 43, 47, 38, 33, 44, 39, 46, 36, 32, 38, 38, 35, 26, 43, 
15229, 24, 33, 30, 33, 57, 40, 37, 44, 34, 46, 41, 33, 35, 29, 37
153), ADAS_CogT7 = c(16, 9.32, 21.33, 17, 8.32, 11, 14.99, 10.99, 
15417, 18.33, 13.32, 14.34, 8.99, 7, 11.99, 15.33, 6.99, 5.33, 12.32, 
15513, 21.32, 7.99, 13.33, 11.99, 17.32, 16.32, 16.33, 14.66, 18.99, 
15617.33, 7.99, 9.33, 10.99, NA, 12.99, 16.33, 21.66, 9, 9.34, 8.66, 
1578.33, 13.66, 15.66, 6.66, 10.99, 13.33, 13.33, 7.99, 11.99, 11.32, 
1587.33, 9.66, 6.99, NA, 15.99, 15.66, 14.66, 6.32, 7, 11, 14, 10.33, 
15924.66, NA, 14.99, NA, 15.99, 9.32, NA, NA, 9.99, 9.33, 7.66, 
16017.33, 10.32, 16, 17, 12.99, 15, 14.33, 10, 14.99, 19, 13.99, 
16119.33, NA, 10, 6.99, 11.66, 6.66, 14.33, 16, 8.66, 10, NA, 20, 
16214.99, 19.66, 26.66, 8.99), ROCF_CT7 = c(33, 23.9, 28.7, 39.1, 
16336.4, 29.2, 11.4, 38.1, 18.1, 28.4, 22.2, 18.6, 37, 35, 33.2, 
16423.6, 21.1, 30.2, 28.8, 37.1, 32.2, 33.1, 26.1, 38, 23.6, 21.4, 
16537.3, 36.4, 15.1, 35.7, 29.9, 31.4, 32.4, NA, 37.9, 28.2, 37.2, 
16631.9, 36.7, 24.5, 19.2, 23.4, 32.8, 36.2, 31.7, 24.3, 31.6, 33.1, 
16719.2, 30.4, 32.7, 24.4, 36.2, NA, 28.1, 31.7, 19.7, 31.6, 28.3, 
16827.4, 37.7, 35, 33.4, 34.4, 32.1, NA, 33.7, 31.8, NA, 34.9, 15, 
16920.1, 31, 35, 37.1, 29, 32.6, 31.7, 28.4, 34.4, 35.7, 35.1, 35.9, 
17027.4, 34, NA, 17.4, 28.9, 35.4, 33.4, 30.4, 14.7, 33.2, 26.8, 
171NA, 20, 33.6, 26.8, 36, 34.9), ROCF_IT7 = c(1.7, 12.1, 5.9, 12.1, 
17224.4, 9.8, 7.6, 24.4, 9.6, 7, 11.5, 9.7, 17.1, 14.2, 4.6, 12.1, 
1734.5, 18.8, 8.4, 14.6, 6.1, 17.1, 15.1, 13.1, 11.8, 12.1, 15.9, 
1747.6, 11.1, 5.9, 23.8, 12.4, 11.1, NA, 11, 7.8, 5.6, 6.5, 10.1, 
1759.9, 14.4, 17, 6.4, 27.3, 16, 14.4, 19.7, 16.2, 8.5, 8, 20.5, 
17615.1, 15.6, NA, 9, 8.6, 8.5, 13.2, 17.7, 14.1, 11, 9.1, 7, 16, 
17720.4, NA, 12.3, 16, NA, 15, 8.4, 17.9, 19.7, 11.9, 18.6, 4.7, 
17812.1, 17.5, 9, 5.5, 17.4, 12.1, 9.7, 9.5, 13.4, NA, 7.5, 14.5, 
17911, 19.5, 4.9, 11.9, 26, 12, NA, 3.9, 5.5, 6.4, 4.9, 16.2), ROCF_RT7 = c(2.4, 
1809.3, 6.3, 12.4, 22.6, 8.9, 7.8, 21.9, 9.8, 5.1, 10.8, 4.2, 12.3, 
18118.9, 2.9, 13.8, 1.2, 19.9, 4.2, 9.8, 5.9, 21.3, 12.3, 10.2, 
18212.1, 7.8, 6.3, 7.8, 9.8, 6.3, 16.9, 11.6, 11.3, NA, 8.9, 16.9, 
1833.9, 6.4, 13, 9.9, 14.9, 18.4, 3.7, 15.4, 8.8, 14.2, 14.7, 14.3, 
1848.8, 5.1, 21.8, 14.3, 11.4, NA, 8.7, 9.5, 8.8, 14.2, 17, 13.8, 
18512.8, 9.7, 5.1, 15.3, 22.8, NA, 6.4, 12.7, NA, 20.9, 7.8, 11.8, 
18622.4, 4.3, 17.8, 4.9, 13.8, 16.4, 7.4, 5.6, 24.8, 11.8, 9.4, 
18711.8, 20.3, NA, 7.1, 13.9, 12.3, 23.5, 2.6, 14.8, 15.2, 11.7, 
188NA, 4.3, 6.7, 5.2, 7.3, 18.4), PVF_T7 = c(38.3, 49.4, 16.7, 31.4, 
18960.9, 29.5, 48.4, 28.4, 17.4, 32.9, 40.7, 42.6, 42.4, 32.3, 38.5, 
19040.4, 20.4, 40.5, 42.6, 28.4, 27.5, 24.4, 40.4, 28.7, 5.3, 38.4, 
19146.7, 26.4, 37.4, 23.7, 9.8, 34.9, 55.5, NA, 38.5, 38.5, 25.5, 
19243.5, 36.3, 30.3, 32.7, 15.4, 21.6, 40.5, 38.7, 48.6, 47.6, 26.9, 
19335.7, 31.9, 47.7, 35.4, 29.5, NA, 33.6, 48.3, 27.7, 55.6, 39.5, 
19430.4, 36.7, 38.7, 23.9, 26.4, 28.4, NA, 18.3, 47.6, NA, 35.5, 
19529.7, 39.4, 44.3, 26.7, 29.4, 31.3, 44.4, 30.7, 11.5, 40.9, 37.7, 
19640.4, 14.5, 21.4, 14.7, NA, 29.9, 20.5, 34.4, 55.4, 32.9, 20.7, 
19727.6, 31.6, NA, 7.7, 25.6, 22.6, 22.7, 34.5), SVF_T7 = c(26, 
19848, 30, 29, 43, 25, 42, 36, 31, 27, 22, 21, 40, 46, 32, 29, 32, 
19938, 46, 31, 20, 36, 35, 32, 12, 28, 47, 20, 33, 30, 22, 44, 35, 
200NA, 36, 21, 24, 45, 25, 25, 30, 27, 34, 50, 31, 42, 34, 40, 25, 
20131, 41, 37, 38, NA, 38, 19, 29, 46, 40, 38, 36, 34, 31, 40, 39, 
202NA, 19, 40, NA, 29, 44, 43, 43, 31, 45, 28, 35, 31, 33, 36, 28, 
20335, 34, 34, 29, NA, 26, 30, 34, 57, 40, 36, 41, 36, NA, 22, 31, 
20429, 26, 31)), row.names = c(NA, -100L), class = c(&quot;tbl_df&quot;, &quot;tbl&quot;, 
205&quot;data.frame&quot;))
206&gt; 
207

ANSWER

Answered 2021-Nov-22 at 21:20

I could not reproduce your code as some datasets are missing.

Below an example with various method to add header rows.

1Alzheimer’s Disease Assessment Scale-Cognitive  = ADAS_CogT0
2Rey-Osterrieth Complex Figure Test immediate recall = ROCF_IT0
3Copy of Rey-Osterrieth Complex Figure test   = ROCF_CT0
4Rey-Osterrieth Complex Figure Test delayed recall   = ROCf_RT0
5Phonemic verbal fluency = PVF_T0
6Semantic verbal fluency = SVF_T0
7library(tidyverse)
8library(readxl)
9library(rms)
10library(lme4)
11library(flextable)
12library(gtsummary)
13library(data.table)
14library(plotrix)
15library(purrr)
16
17out &lt;db %&gt;% 
18  mutate(GROUP = fct_recode(GROUP, CONTROL = &quot;CONTROLLO&quot;)) %&gt;%
19  to_long(keys  = c(&quot;tests0&quot;, &quot;tests7&quot;),
20          values =c(&quot;score0&quot;, &quot;score7&quot;),
21          grep(&quot;T0$&quot;, names(.), value = TRUE), 
22          grep(&quot;T7$&quot;, names(.), value = TRUE)) %&gt;%
23  as_tibble %&gt;%
24  mutate(Gender = factor(Gender, levels = c(1, 0), labels = c(&quot;M&quot;, &quot;F&quot;)),
25         across(c(tests0, tests7), factor))
26  
27out1 &lt;- out %&gt;%   
28  group_by(tests0, GROUP) %&gt;%
29  filter(GROUP == 'TRAINING') %&gt;% 
30  summarise(mean0 = mean(score0, na.rm = T),
31            stderr0 = std.error(score0, na.rm = T), 
32            mean7 = mean(score7, na.rm = T), 
33            stederr7 = std.error(score7, na.rm = T),
34            diff.std.mean = t.test(score0, score7, paired = T)$estimate, 
35            p.value = t.test(score0, score7, paired = T)$p.value  
36  ) %&gt;% 
37  ungroup()
38
39out2 &lt;-  out %&gt;% 
40  group_by(tests0, GROUP) %&gt;% 
41  filter(GROUP == 'CONTROL') %&gt;% 
42  summarise(mean0 = mean(score0, na.rm = T),
43            stderr0 = std.error(score0, na.rm = T), 
44            mean7 = mean(score7, na.rm = T), 
45            stederr7 = std.error(score7, na.rm = T),
46            diff.std.mean = t.test(score0, score7, paired = T)$estimate, 
47            p.value = t.test(score0, score7, paired = T)$p.value  
48  ) %&gt;% 
49  ungroup()
50  
51out3 &lt;- out1 %&gt;%
52  bind_cols(out2) %&gt;% 
53  select(., -'tests0...9') %&gt;%
54  flextable()
55TRAINING and CONTROL groups 
56
57WITHIN AND BETWEEN GROUPS
58dput(head(db, 100))
59structure(list(ID = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 
6013, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 
6129, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 
6245, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 
6361, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 
6477, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 
6593, 94, 95, 96, 97, 98, 99, 100), GROUP = c(&quot;TRAINING&quot;, &quot;TRAINING&quot;, 
66&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
67&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
68&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
69&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
70&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
71&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
72&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
73&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, 
74&quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;TRAINING&quot;, &quot;CONTROLLO&quot;, 
75&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
76&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
77&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
78&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
79&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
80&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
81&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
82&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, 
83&quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;, &quot;CONTROLLO&quot;), Gender = c(1, 
840, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 1, 0, 1, 1, 
850, 0, 0, 0, 1, 0, 0, 1, 1, 1, 1, 1, 1, 0, 1, 1, 1, 0, 1, 1, 1, 
860, 0, 1, 0, 1, 0, 0, 0, 0, 1, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 
871, 1, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 0, 0, 1, 0, 0, 0, 0, 1, 1, 
880, 0, 1, 1, 1, 1, 1, 1, 0, 1, 1, 1, 1, 1, 0), Age = c(74, 76, 
8981, 74, 69, 72, 75, 83, 78, 72, 82, 68, 72, 72, 73, 80, 69, 72, 
9070, 80, 75, 80, 78, 74, 82, 74, 80, 82, 78, 81, 66, 71, 70, 79, 
9178, 73, 72, 77, 77, 71, 83, 74, 70, 71, 77, 69, 67, 64, 79, 71, 
9277, 77, 73, 67, 68, 79, 81, 67, 84, 75, 80, 73, 68, 74, 77, 79, 
9379, 72, 73, 78, 76, 78, 77, 74, 78, 77, 77, 82, 77, 70, 77, 81, 
9479, 75, 74, 78, 69, 77, 73, 77, 70, 79, 70, 72, 77, 72, 71, 71, 
9573, 81), Education = c(18, 4, 8, 5, 8, 11, 5, 5, 4, 8, 8, 12, 
965, 18, 13, 5, 13, 13, 5, 5, 13, 5, 3, 8, 17, 5, 8, 5, 5, 8, 17, 
978, 18, 18, 13, 13, 13, 13, 15, 17, 8, 5, 5, 13, 8, 5, 11, 13, 
988, 8, 8, 5, 13, 8, 5, 17, 8, 12, 13, 5, 8, 8, 8, 5, 3, 8, 18, 
995, 8, 13, 8, 5, 17, 8, 5, 17, 5, 8, 11, 8, 8, 5, 12, 3, 8, 8, 
1008, 13, 5, 5, 8, 8, 13, 5, 5, 8, 13, 5, 8, 12), ADAS_CogT0 = c(14.66, 
10115.33, 17.33, 19, 7.66, 12.6, 18.67, 14.99, 17.99, 17.33, 13.66, 
10216.99, 10.66, 9.66, 14.99, 15.66, 13.33, 4.33, 14.33, 15.99, 
10316.33, 10.66, 14.66, 10.66, 19.33, 17.66, 15.99, 20.66, 20.6, 
10417, 10.33, 6.33, 6.66, 19.99, 13.33, 24.33, 12.33, 10.33, 12.33, 
1059.66, 10.99, 13.99, 23, 6.32, 11.32, 13.99, 14.66, 8.99, 14.33, 
1069.99, 7.33, 15.66, 14, 7.99, 23.32, 14.66, 9.99, 5.66, 6.99, 
10711.66, 10.33, 6.99, 19.32, NA, 10, 17.66, 13.66, 10.32, NA, NA, 
1088.66, 9, 6.99, 14.99, 9.66, 13.66, 15.32, 12, 14, 13.66, 11.99, 
10915.66, 16, 15, 16.99, 20, 11, 7.99, 8.33, 8.32, 14.99, 18.66, 
11010.33, 11.99, 9.32, 17, 14.33, 14.66, 16.6, 9.99), ROCF_CT0 = c(32, 
11113.5, 34.7, 26.1, 35.4, 24.6, 28.9, 34.1, 12.1, 28.1, 22.7, 31.6, 
11230.4, 34.4, 37.2, 38.1, 18.6, 26.2, 34.8, 37.1, 26.2, 35.1, 37.1, 
11336, 11.2, 17.4, 32.7, 28.4, 21.1, 26.7, 31.9, 33.4, 27.4, 32.7, 
11433.9, 37.2, 28.2, 34.2, 29.7, 30, 16.2, 37.4, 34.8, 36.6, 33, 
11530.8, 34.6, 35.1, 29.7, 36.8, 28, 34.4, 33.7, 28.9, 31.8, 31.7, 
11610.2, 27.1, 36.8, 22.4, 35.7, 28, 30.9, 35.4, 26.9, 29.7, 26.7, 
11728.8, 32, 37.9, 27, 18.6, 33, 38, 25.1, 31, 27.1, 26.7, 37, 30.4, 
11837, 30.1, 36.9, 24.4, 34, 6.7, 20.4, 30.2, 31.1, 33.4, 17.4, 
11923.7, 30.6, 28.3, 19.4, 33.4, 31.6, 39.8, 27, 33.9), ROCF_IT0 = c(3.7, 
1207.6, 14.9, 13.6, 23.4, 4, 8.1, 23.9, 10.1, 7, 10, 9.2, 15.6, 
12110.8, 6.6, 13.6, 4, 15.3, 7.4, 0, 8.6, 22.9, 11.6, 10.1, 6.8, 
12212.1, 12.9, 0, 10.6, 5.9, 14.8, 10.9, 10.6, 10.6, 7.5, 7.3, 4.1, 
1233.1, 7.6, 13.4, 10.9, 19, 3.4, 11.7, 14.6, 9.4, 16.7, 15.7, 14.5, 
12412.5, 15.1, 22.6, 9.1, 15.5, 11, 6.1, 11.5, 5.6, 17.2, 18.1, 
12523.5, 15.6, 7.7, 13.5, 14.5, 11.9, 10.3, 11.5, 12.6, 19, 11.9, 
12615.4, 17.7, 8.4, 13.4, 5.2, 11.1, 16, 10.9, 8, 22.6, 13.6, 12.2, 
1276.5, 13.4, 11.5, 8, 18.1, 10, 11, 5.9, 14.9, 12, 11.4, 17, 5.9, 
12815, 10.9, 8.9, 15.7), ROCF_RT0 = c(3.9, 7.8, 16.3, 13.4, 24.1, 
1294.7, 7.8, 20.9, 9.8, 5.1, 9.8, 8.2, 16.3, 11.3, 5.9, 14.8, 4.7, 
13014.4, 6.7, 0, 4.9, 23.3, 14.8, 11.7, 6.4, 10.3, 14.3, 0, 10.8, 
1316.3, 15.4, 10.1, 5.3, 12, 6.9, 5.4, 4.4, 4.4, 7, 10.9, 11.9, 
13217.3, 3.7, 12.2, 6.7, 14.7, 16.2, 7.8, 10.8, 10.2, 16.7, 22.8, 
1339.1, 15.7, 10.7, 4, 8.8, 6.8, 14.6, 14.5, 24.8, 13.7, 0, 10.3, 
13417.3, 11.3, 8, 13.2, 15.2, 18.4, 4.3, 12.8, 18.9, 4.3, 11.8, 
1354.9, 11.8, 13.3, 10.3, 7.1, 23.7, 15.8, 16.4, 6.8, 20.3, 11.3, 
1367.6, 19.9, 10.8, 12.5, 5.6, 11.3, 11.7, 13.5, 12.3, 5.1, 14.2, 
1378.7, 12.3, 16.4), PVF_T0 = c(41.3, 35.4, 16.7, 28.4, 46.9, 33.6, 
13839.4, 27.4, 17.4, 37.9, 31.7, 37.6, 33.4, 21.5, 49.5, 40.4, 10.6, 
13940.5, 30.6, 28.4, 29.5, 22.4, 45.4, 29.7, 17.3, 39.4, 31.7, 24.4, 
14038.4, 22.7, 6.8, 19.9, 56.5, 35.3, 43.5, 32.5, 28.5, 38.5, 22.3, 
14132.3, 30.7, 23.4, 18.6, 46.6, 36.7, 35.6, 38.6, 16.9, 38.7, 37.6, 
14239.7, 34.4, 27.5, 16.2, 32.6, 48.3, 26.7, 49.9, 40.5, 34.4, 37.7, 
14339.7, 23.2, 19.4, 21.4, 33.7, 11.3, 41.6, 23.7, 31.5, 28.7, 34.4, 
14433.3, 27.7, 23.4, 32.3, 49.4, 35.7, 26.7, 31.9, 37.7, 35.4, 20.5, 
14528.4, 14.7, 30.7, 35.9, 16.5, 27.4, 49.4, 37.9, 27.7, 41.6, 31.6, 
14655.4, 42.9, 21.6, 25.6, 26.7, 43.5), SVF_T0 = c(40, 37, 30, 28, 
14749, 27, 34, 35, 29, 33, 20, 18, 36, 47, 43, 39, 36, 38, 48, 31, 
14825, 34, 31, 33, 24, 30, 45, 20, 33, 34, 29, 42, 40, 37, 37, 25, 
14928, 42, 19, 32, 37, 35, 20, 43, 33, 40, 40, 42, 27, 35, 29, 39, 
15031, 48, 41, 27, 32, 45, 39, 40, 36, 38, 27, 32, 47, 36, 24, 40, 
15138, 29, 43, 47, 38, 33, 44, 39, 46, 36, 32, 38, 38, 35, 26, 43, 
15229, 24, 33, 30, 33, 57, 40, 37, 44, 34, 46, 41, 33, 35, 29, 37
153), ADAS_CogT7 = c(16, 9.32, 21.33, 17, 8.32, 11, 14.99, 10.99, 
15417, 18.33, 13.32, 14.34, 8.99, 7, 11.99, 15.33, 6.99, 5.33, 12.32, 
15513, 21.32, 7.99, 13.33, 11.99, 17.32, 16.32, 16.33, 14.66, 18.99, 
15617.33, 7.99, 9.33, 10.99, NA, 12.99, 16.33, 21.66, 9, 9.34, 8.66, 
1578.33, 13.66, 15.66, 6.66, 10.99, 13.33, 13.33, 7.99, 11.99, 11.32, 
1587.33, 9.66, 6.99, NA, 15.99, 15.66, 14.66, 6.32, 7, 11, 14, 10.33, 
15924.66, NA, 14.99, NA, 15.99, 9.32, NA, NA, 9.99, 9.33, 7.66, 
16017.33, 10.32, 16, 17, 12.99, 15, 14.33, 10, 14.99, 19, 13.99, 
16119.33, NA, 10, 6.99, 11.66, 6.66, 14.33, 16, 8.66, 10, NA, 20, 
16214.99, 19.66, 26.66, 8.99), ROCF_CT7 = c(33, 23.9, 28.7, 39.1, 
16336.4, 29.2, 11.4, 38.1, 18.1, 28.4, 22.2, 18.6, 37, 35, 33.2, 
16423.6, 21.1, 30.2, 28.8, 37.1, 32.2, 33.1, 26.1, 38, 23.6, 21.4, 
16537.3, 36.4, 15.1, 35.7, 29.9, 31.4, 32.4, NA, 37.9, 28.2, 37.2, 
16631.9, 36.7, 24.5, 19.2, 23.4, 32.8, 36.2, 31.7, 24.3, 31.6, 33.1, 
16719.2, 30.4, 32.7, 24.4, 36.2, NA, 28.1, 31.7, 19.7, 31.6, 28.3, 
16827.4, 37.7, 35, 33.4, 34.4, 32.1, NA, 33.7, 31.8, NA, 34.9, 15, 
16920.1, 31, 35, 37.1, 29, 32.6, 31.7, 28.4, 34.4, 35.7, 35.1, 35.9, 
17027.4, 34, NA, 17.4, 28.9, 35.4, 33.4, 30.4, 14.7, 33.2, 26.8, 
171NA, 20, 33.6, 26.8, 36, 34.9), ROCF_IT7 = c(1.7, 12.1, 5.9, 12.1, 
17224.4, 9.8, 7.6, 24.4, 9.6, 7, 11.5, 9.7, 17.1, 14.2, 4.6, 12.1, 
1734.5, 18.8, 8.4, 14.6, 6.1, 17.1, 15.1, 13.1, 11.8, 12.1, 15.9, 
1747.6, 11.1, 5.9, 23.8, 12.4, 11.1, NA, 11, 7.8, 5.6, 6.5, 10.1, 
1759.9, 14.4, 17, 6.4, 27.3, 16, 14.4, 19.7, 16.2, 8.5, 8, 20.5, 
17615.1, 15.6, NA, 9, 8.6, 8.5, 13.2, 17.7, 14.1, 11, 9.1, 7, 16, 
17720.4, NA, 12.3, 16, NA, 15, 8.4, 17.9, 19.7, 11.9, 18.6, 4.7, 
17812.1, 17.5, 9, 5.5, 17.4, 12.1, 9.7, 9.5, 13.4, NA, 7.5, 14.5, 
17911, 19.5, 4.9, 11.9, 26, 12, NA, 3.9, 5.5, 6.4, 4.9, 16.2), ROCF_RT7 = c(2.4, 
1809.3, 6.3, 12.4, 22.6, 8.9, 7.8, 21.9, 9.8, 5.1, 10.8, 4.2, 12.3, 
18118.9, 2.9, 13.8, 1.2, 19.9, 4.2, 9.8, 5.9, 21.3, 12.3, 10.2, 
18212.1, 7.8, 6.3, 7.8, 9.8, 6.3, 16.9, 11.6, 11.3, NA, 8.9, 16.9, 
1833.9, 6.4, 13, 9.9, 14.9, 18.4, 3.7, 15.4, 8.8, 14.2, 14.7, 14.3, 
1848.8, 5.1, 21.8, 14.3, 11.4, NA, 8.7, 9.5, 8.8, 14.2, 17, 13.8, 
18512.8, 9.7, 5.1, 15.3, 22.8, NA, 6.4, 12.7, NA, 20.9, 7.8, 11.8, 
18622.4, 4.3, 17.8, 4.9, 13.8, 16.4, 7.4, 5.6, 24.8, 11.8, 9.4, 
18711.8, 20.3, NA, 7.1, 13.9, 12.3, 23.5, 2.6, 14.8, 15.2, 11.7, 
188NA, 4.3, 6.7, 5.2, 7.3, 18.4), PVF_T7 = c(38.3, 49.4, 16.7, 31.4, 
18960.9, 29.5, 48.4, 28.4, 17.4, 32.9, 40.7, 42.6, 42.4, 32.3, 38.5, 
19040.4, 20.4, 40.5, 42.6, 28.4, 27.5, 24.4, 40.4, 28.7, 5.3, 38.4, 
19146.7, 26.4, 37.4, 23.7, 9.8, 34.9, 55.5, NA, 38.5, 38.5, 25.5, 
19243.5, 36.3, 30.3, 32.7, 15.4, 21.6, 40.5, 38.7, 48.6, 47.6, 26.9, 
19335.7, 31.9, 47.7, 35.4, 29.5, NA, 33.6, 48.3, 27.7, 55.6, 39.5, 
19430.4, 36.7, 38.7, 23.9, 26.4, 28.4, NA, 18.3, 47.6, NA, 35.5, 
19529.7, 39.4, 44.3, 26.7, 29.4, 31.3, 44.4, 30.7, 11.5, 40.9, 37.7, 
19640.4, 14.5, 21.4, 14.7, NA, 29.9, 20.5, 34.4, 55.4, 32.9, 20.7, 
19727.6, 31.6, NA, 7.7, 25.6, 22.6, 22.7, 34.5), SVF_T7 = c(26, 
19848, 30, 29, 43, 25, 42, 36, 31, 27, 22, 21, 40, 46, 32, 29, 32, 
19938, 46, 31, 20, 36, 35, 32, 12, 28, 47, 20, 33, 30, 22, 44, 35, 
200NA, 36, 21, 24, 45, 25, 25, 30, 27, 34, 50, 31, 42, 34, 40, 25, 
20131, 41, 37, 38, NA, 38, 19, 29, 46, 40, 38, 36, 34, 31, 40, 39, 
202NA, 19, 40, NA, 29, 44, 43, 43, 31, 45, 28, 35, 31, 33, 36, 28, 
20335, 34, 34, 29, NA, 26, 30, 34, 57, 40, 36, 41, 36, NA, 22, 31, 
20429, 26, 31)), row.names = c(NA, -100L), class = c(&quot;tbl_df&quot;, &quot;tbl&quot;, 
205&quot;data.frame&quot;))
206&gt; 
207library(palmerpenguins)
208library(tidyverse)
209library(flextable)
210by &lt;- c(&quot;species&quot;, &quot;island&quot;)
211
212tabl &lt;- palmerpenguins::penguins %&gt;% 
213  group_by(across(all_of(by))) %&gt;% 
214  summarise(
215    across(ends_with(&quot;mm&quot;),
216           list(
217             mean = ~ mean(.x, na.rm = TRUE),
218             median = ~ median(.x, na.rm = TRUE),
219             sd = ~ sd(.x, na.rm = TRUE)
220           )
221           ),
222    .groups = &quot;drop&quot;
223  )
224
225tab_header &lt;- tibble( key = colnames(tabl)) %&gt;% 
226  separate(col = key, into = c(&quot;part&quot;, &quot;measure&quot;, &quot;unit&quot;, &quot;stat&quot;), 
227           fill = &quot;right&quot;, remove = FALSE) %&gt;% 
228  select(-unit) %&gt;% 
229  mutate(
230    measure = if_else(is.na(measure), part, measure),
231    stat = if_else(is.na(stat), measure, stat)
232  )
233tab_header
234
235flextable(tabl) %&gt;% 
236  flextable::set_header_df(tab_header, key = &quot;key&quot;) %&gt;% 
237  merge_h(part = &quot;header&quot;) %&gt;% 
238  merge_at(part = &quot;header&quot;, i = 1:3, j = 1) %&gt;%
239  merge_at(part = &quot;header&quot;, i = 1:3, j = 2) %&gt;%
240  theme_box() %&gt;%
241  add_header_row(values = c(&quot;&quot;, &quot;bada&quot;, &quot;boum&quot;), colwidths = c(1, 5, 5)) %&gt;%
242  add_header_lines(c(&quot;another way to add a row of text&quot;)) %&gt;% 
243  align(part = &quot;header&quot;, i = seq_len(nrow_part(., part = &quot;header&quot;)-1), align = &quot;center&quot;)
244
245
246

enter image description here

Source https://stackoverflow.com/questions/70059022

QUESTION

Why Effects package in Julia is returning an error following the output of MixedModels?

Asked 2021-Nov-12 at 11:06

I want to run the average marginal effects between two predictors: education and unemployment rate. While the package Effects in Julia can do the trick, i am having an error which i will describe below. First, i show the code that works perfectly fine and the results i get from running the MixedModelspackage. Second, i show the part of the code that is not working.

1using Arrow, Effects, DataFrames, MixedModels, PooledArrays, FreqTables
2dffnm = &quot;df4_full&quot;
3df = DataFrame(Arrow.Table(dffnm))
4
5df1 = DataFrame( 
6    unemploy = disallowmissing(df.macro_unemployment),
7    workhours_imputed = df.workhours_imputed,
8    education = PooledArray(df.education),
9    id = PooledArray(string.(disallowmissing(df.id))),
10    age = Int8.(disallowmissing(df.age)) .- Int8(42), # center the age at 42
11    sex = PooledArray(df.sex),)
12
13form1 = @formula workhours_imputed ~ 1 + sex + age  + education + education * unemploy + sex * unemploy + (1|id)
14
15contr = Dict(nm =&gt; Grouping() for nm in (:country, :country_year, :id))
16
17contr = Dict(:country =&gt; Grouping(),
18             :country_year =&gt; Grouping(),
19             :id =&gt; Grouping(),
20             :sex =&gt; DummyCoding(base=&quot;Female&quot;),
21             :education =&gt; DummyCoding(base=&quot;Tertiary&quot;))
22
23m1 = @time fit(MixedModel, form1, df1, contrasts=contr)
24

Here is the output: enter image description here

Everything works perfectly so far. The aim is to run the average marginal effects for education and unemployment on working hours. However, i thought that i would start with a simpler example which is just taking the average marginal effects of education (i follow their example here: https://beacon-biosignals.github.io/Effects.jl/stable/#Interaction-Terms-in-Effects):

1using Arrow, Effects, DataFrames, MixedModels, PooledArrays, FreqTables
2dffnm = &quot;df4_full&quot;
3df = DataFrame(Arrow.Table(dffnm))
4
5df1 = DataFrame( 
6    unemploy = disallowmissing(df.macro_unemployment),
7    workhours_imputed = df.workhours_imputed,
8    education = PooledArray(df.education),
9    id = PooledArray(string.(disallowmissing(df.id))),
10    age = Int8.(disallowmissing(df.age)) .- Int8(42), # center the age at 42
11    sex = PooledArray(df.sex),)
12
13form1 = @formula workhours_imputed ~ 1 + sex + age  + education + education * unemploy + sex * unemploy + (1|id)
14
15contr = Dict(nm =&gt; Grouping() for nm in (:country, :country_year, :id))
16
17contr = Dict(:country =&gt; Grouping(),
18             :country_year =&gt; Grouping(),
19             :id =&gt; Grouping(),
20             :sex =&gt; DummyCoding(base=&quot;Female&quot;),
21             :education =&gt; DummyCoding(base=&quot;Tertiary&quot;))
22
23m1 = @time fit(MixedModel, form1, df1, contrasts=contr)
24design = Dict(:education =&gt; unique(df1))
25effects(design, m1)
26

Error:

1using Arrow, Effects, DataFrames, MixedModels, PooledArrays, FreqTables
2dffnm = &quot;df4_full&quot;
3df = DataFrame(Arrow.Table(dffnm))
4
5df1 = DataFrame( 
6    unemploy = disallowmissing(df.macro_unemployment),
7    workhours_imputed = df.workhours_imputed,
8    education = PooledArray(df.education),
9    id = PooledArray(string.(disallowmissing(df.id))),
10    age = Int8.(disallowmissing(df.age)) .- Int8(42), # center the age at 42
11    sex = PooledArray(df.sex),)
12
13form1 = @formula workhours_imputed ~ 1 + sex + age  + education + education * unemploy + sex * unemploy + (1|id)
14
15contr = Dict(nm =&gt; Grouping() for nm in (:country, :country_year, :id))
16
17contr = Dict(:country =&gt; Grouping(),
18             :country_year =&gt; Grouping(),
19             :id =&gt; Grouping(),
20             :sex =&gt; DummyCoding(base=&quot;Female&quot;),
21             :education =&gt; DummyCoding(base=&quot;Tertiary&quot;))
22
23m1 = @time fit(MixedModel, form1, df1, contrasts=contr)
24design = Dict(:education =&gt; unique(df1))
25effects(design, m1)
26MethodError: no method matching effects!(::Dict{Symbol, DataFrame}, ::LinearMixedModel{Float64})
27    Closest candidates are:
28      effects!(::DataFrame, ::StatsBase.RegressionModel; eff_col, err_col, typical) at /Users/jmoawad/.julia/packages/Effects/B3CEy/src/regressionmodel.jl:40
29    
30    Stacktrace:
31     [1] top-level scope
32       @ In[28]:1
33     [2] eval
34       @ ./boot.jl:360 [inlined]
35     [3] include_string(mapexpr::typeof(REPL.softscope), mod::Module, code::String, filename::String)
36       @ Base ./loading.jl:1116
37

Could someone help me to fix this? I am also unsure how to run the average marginal effects for the interaction between education and unemployment rate.

ANSWER

Answered 2021-Nov-12 at 11:06

Based on the suggestions of @sundar & @Peter Deffebach (from another thread).

Here is the solution code:

You have to filter the missing first of education after running the model:

1using Arrow, Effects, DataFrames, MixedModels, PooledArrays, FreqTables
2dffnm = &quot;df4_full&quot;
3df = DataFrame(Arrow.Table(dffnm))
4
5df1 = DataFrame( 
6    unemploy = disallowmissing(df.macro_unemployment),
7    workhours_imputed = df.workhours_imputed,
8    education = PooledArray(df.education),
9    id = PooledArray(string.(disallowmissing(df.id))),
10    age = Int8.(disallowmissing(df.age)) .- Int8(42), # center the age at 42
11    sex = PooledArray(df.sex),)
12
13form1 = @formula workhours_imputed ~ 1 + sex + age  + education + education * unemploy + sex * unemploy + (1|id)
14
15contr = Dict(nm =&gt; Grouping() for nm in (:country, :country_year, :id))
16
17contr = Dict(:country =&gt; Grouping(),
18             :country_year =&gt; Grouping(),
19             :id =&gt; Grouping(),
20             :sex =&gt; DummyCoding(base=&quot;Female&quot;),
21             :education =&gt; DummyCoding(base=&quot;Tertiary&quot;))
22
23m1 = @time fit(MixedModel, form1, df1, contrasts=contr)
24design = Dict(:education =&gt; unique(df1))
25effects(design, m1)
26MethodError: no method matching effects!(::Dict{Symbol, DataFrame}, ::LinearMixedModel{Float64})
27    Closest candidates are:
28      effects!(::DataFrame, ::StatsBase.RegressionModel; eff_col, err_col, typical) at /Users/jmoawad/.julia/packages/Effects/B3CEy/src/regressionmodel.jl:40
29    
30    Stacktrace:
31     [1] top-level scope
32       @ In[28]:1
33     [2] eval
34       @ ./boot.jl:360 [inlined]
35     [3] include_string(mapexpr::typeof(REPL.softscope), mod::Module, code::String, filename::String)
36       @ Base ./loading.jl:1116
37filter!(!ismissing, df1.education)
38

Then you choose the what categories of interest or what range of interest among both variables:

1using Arrow, Effects, DataFrames, MixedModels, PooledArrays, FreqTables
2dffnm = &quot;df4_full&quot;
3df = DataFrame(Arrow.Table(dffnm))
4
5df1 = DataFrame( 
6    unemploy = disallowmissing(df.macro_unemployment),
7    workhours_imputed = df.workhours_imputed,
8    education = PooledArray(df.education),
9    id = PooledArray(string.(disallowmissing(df.id))),
10    age = Int8.(disallowmissing(df.age)) .- Int8(42), # center the age at 42
11    sex = PooledArray(df.sex),)
12
13form1 = @formula workhours_imputed ~ 1 + sex + age  + education + education * unemploy + sex * unemploy + (1|id)
14
15contr = Dict(nm =&gt; Grouping() for nm in (:country, :country_year, :id))
16
17contr = Dict(:country =&gt; Grouping(),
18             :country_year =&gt; Grouping(),
19             :id =&gt; Grouping(),
20             :sex =&gt; DummyCoding(base=&quot;Female&quot;),
21             :education =&gt; DummyCoding(base=&quot;Tertiary&quot;))
22
23m1 = @time fit(MixedModel, form1, df1, contrasts=contr)
24design = Dict(:education =&gt; unique(df1))
25effects(design, m1)
26MethodError: no method matching effects!(::Dict{Symbol, DataFrame}, ::LinearMixedModel{Float64})
27    Closest candidates are:
28      effects!(::DataFrame, ::StatsBase.RegressionModel; eff_col, err_col, typical) at /Users/jmoawad/.julia/packages/Effects/B3CEy/src/regressionmodel.jl:40
29    
30    Stacktrace:
31     [1] top-level scope
32       @ In[28]:1
33     [2] eval
34       @ ./boot.jl:360 [inlined]
35     [3] include_string(mapexpr::typeof(REPL.softscope), mod::Module, code::String, filename::String)
36       @ Base ./loading.jl:1116
37filter!(!ismissing, df1.education)