Sign in with
By continuing you indicate that you have read and agree to our Terms of service and Privacy policy
Sign in with
By continuing you indicate that you have read and agree to our Terms of service and Privacy policy
Explore all Education related open source software, libraries, packages, source code, cloud functions and APIs.
The Education Industry comprises establishments whose primary objective is to provide education. These establishments can be public, non-profit, or for-profit institutions. They include elementary schools, secondary schools, online learning, community colleges, universities, and ministries or departments of education.
These software components cover functions across Academia, e learning, Edutech, Hackathon, LMS, Research areas.
The Education Industry comprises establishments whose primary objective is to provide education. These establishments can be public, non-profit, or for-profit institutions. They include elementary schools, secondary schools, online learning, community colleges, universities, and ministries or departments of education.
These software components cover functions across Academia, e learning, Edutech, Hackathon, LMS, Research areas.
Popular New Releases in Education
r139 | |
Keras Release 2.9.0 RC2 | |
scikit-learn 1.0.2 | |
Faceswap Windows and Linux Installers v2.0.0 | |
Phaser v3.55.2 |
| three.js r139 |
| keras Keras Release 2.9.0 RC2 |
| scikit-learn scikit-learn 1.0.2 |
| faceswap Faceswap Windows and Linux Installers v2.0.0 |
| phaser Phaser v3.55.2 |
Popular Libraries in Education
Trending New libraries in Education
Top Authors in Education
Trending Kits in Education
Trending Discussions on Education
Add commas every digits in numbers of kable()
Canceling a Future vs stopping a Thread
How do you plot smooth components of different GAMs in same panel?
Coefficient plot - Increase gap between rows and alternative background colors in rows
MySQL SQL Performance need some improvement
Grabbing certain data from one object to another object
Manually construct factor from integer vector
react-native-render-html: "You seem to update the X prop of the Y component in short periods of time..."
Reproduce a complex table with double headesrs
Why Effects package in Julia is returning an error following the output of MixedModels?
QUESTION
Add commas every digits in numbers of kable()
Asked 2022-Mar-21 at 16:36I have the dataframe below and I create a kable out of this. How could I add commas between numbers every 3 digits?
1Descs<-structure(list(Mean = c(NaN, 943330388, NaN, NaN, NaN, 543234645,
245831420, NaN, 27301292, 160818771), Median = c(NaN, 943330388,
3NaN, NaN, NaN, 543234645, 45831420, NaN, 27301292, 160818771),
4 SD = c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
5 NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), MAD = c(NA,
6 0, NA, NA, NA, 0, 0, NA, 0, 0), MIN = c(NA, 943330388, NA,
7 NA, NA, 543234645, 45831420, NA, 27301292, 160818771), MAX = c(NA,
8 943330388, NA, NA, NA, 543234645, 45831420, NA, 27301292,
9 160818771), VAR = c(NA_real_, NA_real_, NA_real_, NA_real_,
10 NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_
11 ), RANGE = structure(c(NA, 943330388, NA, NA, NA, 543234645,
12 45831420, NA, 27301292, 160818771, NA, 943330388, NA, NA,
13 NA, 543234645, 45831420, NA, 27301292, 160818771), .Dim = c(10L,
14 2L)), QUANTILES = structure(c(NA, 943330388, NA, NA, NA,
15 543234645, 45831420, NA, 27301292, 160818771, NA, 943330388,
16 NA, NA, NA, 543234645, 45831420, NA, 27301292, 160818771), .Dim = c(10L,
17 2L), .Dimnames = list(NULL, c("25%", "75%")))), row.names = c("Comedy",
18"Education", "Entertainment", "Film & Animation", "Gaming", "Howto & Style",
19"Music", "People & Blogs", "Science & Technology", "Sports"), class = "data.frame")
20
21library(kableExtra)
22
23kable(Descs) %>%
24 kable_styling(
25 font_size = 15,
26 bootstrap_options = c("striped", "hover", "condensed")
27 )
28ANSWER
Answered 2022-Mar-21 at 16:36You could use the kable format argument, this avoids mucking around with the data prior to putting into the table.
And if you want to clear up the NAs and NaNs you could add in this line of code: options(knitr.kable.NA = '')
1Descs<-structure(list(Mean = c(NaN, 943330388, NaN, NaN, NaN, 543234645,
245831420, NaN, 27301292, 160818771), Median = c(NaN, 943330388,
3NaN, NaN, NaN, 543234645, 45831420, NaN, 27301292, 160818771),
4 SD = c(NA_real_, NA_real_, NA_real_, NA_real_, NA_real_,
5 NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), MAD = c(NA,
6 0, NA, NA, NA, 0, 0, NA, 0, 0), MIN = c(NA, 943330388, NA,
7 NA, NA, 543234645, 45831420, NA, 27301292, 160818771), MAX = c(NA,
8 943330388, NA, NA, NA, 543234645, 45831420, NA, 27301292,
9 160818771), VAR = c(NA_real_, NA_real_, NA_real_, NA_real_,
10 NA_real_, NA_real_, NA_real_, NA_real_, NA_real_, NA_real_
11 ), RANGE = structure(c(NA, 943330388, NA, NA, NA, 543234645,
12 45831420, NA, 27301292, 160818771, NA, 943330388, NA, NA,
13 NA, 543234645, 45831420, NA, 27301292, 160818771), .Dim = c(10L,
14 2L)), QUANTILES = structure(c(NA, 943330388, NA, NA, NA,
15 543234645, 45831420, NA, 27301292, 160818771, NA, 943330388,
16 NA, NA, NA, 543234645, 45831420, NA, 27301292, 160818771), .Dim = c(10L,
17 2L), .Dimnames = list(NULL, c("25%", "75%")))), row.names = c("Comedy",
18"Education", "Entertainment", "Film & Animation", "Gaming", "Howto & Style",
19"Music", "People & Blogs", "Science & Technology", "Sports"), class = "data.frame")
20
21library(kableExtra)
22
23kable(Descs) %>%
24 kable_styling(
25 font_size = 15,
26 bootstrap_options = c("striped", "hover", "condensed")
27 )
28library(kableExtra)
29
30kable(Descs,
31 format.args = list(big.mark = ",")) %>%
32 kable_styling(
33 font_size = 15,
34 bootstrap_options = c("striped", "hover", "condensed")
35 )
36
37
QUESTION
Canceling a Future vs stopping a Thread
Asked 2022-Mar-11 at 22:59In very ancient days, there used to be a way to "stop" or "kill" a thread, though this is has been deprecated due to it allowing system instability. I noticed though, it's possible to "cancel" a running future. The quotes are there because I don't know how the thread is treated at an OS level.
For my education, What's the difference between canceling a Future, effectively canceling the thread, and stopping the thread? Why is cancelling okay, but stopping/killing a thread in the old days bad?
ANSWER
Answered 2022-Mar-11 at 22:59Cancelling tells the Future its result is no longer desired (if it hasn't already completed), and lets it stop cleanly, whereas Thread.stop() kills the underlying native thread and releases all its monitors. The thread doesn't get a chance to wrap up what it was doing or put anything into a known, good state.
The difference is, one sends a message, but lets the recipient respond to the message on its own terms, and the other just forcefully stops it, effectively instantly. (Letting the recipient respond in its own way can even mean that the job will continue to execute, since different implementations of Future may handle cancellation differently, but any result will still be ignored.)
It's like the difference between putting out the red flag in an auto race, telling a car it needs to come in to the pit stop on the next lap, versus suddenly putting a wall in the middle of the racetrack right in front of the car, which it then crashes into.
QUESTION
How do you plot smooth components of different GAMs in same panel?
Asked 2022-Feb-19 at 12:38I have two GAMs which have the same predictor variables but different independent variables. I would like to combine the two GAMs to a set of plots where the smooth component (partial residuals) of each predictor variable are in the same panel (differentiated with e.g. color). Reproducible example:
1# Required packages
2require(mgcv)
3require(mgcViz)
4
5# Dataset
6data("swiss")
7
8# GAM models
9fit1 <- mgcv::gam(Fertility ~ s(Examination) + s(Education), data = swiss)
10fit2 <- mgcv::gam(Agriculture ~ s(Examination) + s(Education), data = swiss)
11
12# Converting GAM objects to a gamViz objects
13viz_fit1 <- mgcViz::getViz(fit1)
14viz_fit2 <- mgcViz::getViz(fit2)
15
16# Make plotGAM objects
17trt_fit1 <- plot(viz_fit1, allTerms = T) + l_fitLine()
18trt_fit2 <- plot(viz_fit2, allTerms = T) + l_fitLine()
19
20# Print plots
21print(trt_fit1, pages = 1)
22print(trt_fit2, pages = 1)
23Plot of fit1 looks like this:
And fit2 like this:
So I would like to combine the two Examinations into one panel, and the two Educations into another one, showing the independent variable (from different GAMs) with different color/linetype.
ANSWER
Answered 2022-Feb-18 at 17:55If you want them in the same plot, you can pull the data from your fit with trt_fit1[["plots"]][[1]]$data$fit and plot them yourself. I looked at the plot style from the mgcViz github. You can add a second axis or scale as necessary.
1# Required packages
2require(mgcv)
3require(mgcViz)
4
5# Dataset
6data("swiss")
7
8# GAM models
9fit1 <- mgcv::gam(Fertility ~ s(Examination) + s(Education), data = swiss)
10fit2 <- mgcv::gam(Agriculture ~ s(Examination) + s(Education), data = swiss)
11
12# Converting GAM objects to a gamViz objects
13viz_fit1 <- mgcViz::getViz(fit1)
14viz_fit2 <- mgcViz::getViz(fit2)
15
16# Make plotGAM objects
17trt_fit1 <- plot(viz_fit1, allTerms = T) + l_fitLine()
18trt_fit2 <- plot(viz_fit2, allTerms = T) + l_fitLine()
19
20# Print plots
21print(trt_fit1, pages = 1)
22print(trt_fit2, pages = 1)
23library(tidyverse)
24exam_dat <-
25 bind_rows(trt_fit1[["plots"]][[1]]$data$fit %>% mutate(fit = "Fit 1"),
26 trt_fit2[["plots"]][[1]]$data$fit %>% mutate(fit = "Fit 2"))
27
28
29ggplot(data = exam_dat, aes(x = x, y = y, colour = fit)) +
30 geom_line() +
31 labs(x = "Examination", y = "s(Examination)") +
32 theme_bw() +
33 theme(panel.grid.major = element_blank(), panel.grid.minor = element_blank())
34
To simply get them on the same panel, you could use gridExtra as fit1 and fit2 have a ggplot object.
1# Required packages
2require(mgcv)
3require(mgcViz)
4
5# Dataset
6data("swiss")
7
8# GAM models
9fit1 <- mgcv::gam(Fertility ~ s(Examination) + s(Education), data = swiss)
10fit2 <- mgcv::gam(Agriculture ~ s(Examination) + s(Education), data = swiss)
11
12# Converting GAM objects to a gamViz objects
13viz_fit1 <- mgcViz::getViz(fit1)
14viz_fit2 <- mgcViz::getViz(fit2)
15
16# Make plotGAM objects
17trt_fit1 <- plot(viz_fit1, allTerms = T) + l_fitLine()
18trt_fit2 <- plot(viz_fit2, allTerms = T) + l_fitLine()
19
20# Print plots
21print(trt_fit1, pages = 1)
22print(trt_fit2, pages = 1)
23library(tidyverse)
24exam_dat <-
25 bind_rows(trt_fit1[["plots"]][[1]]$data$fit %>% mutate(fit = "Fit 1"),
26 trt_fit2[["plots"]][[1]]$data$fit %>% mutate(fit = "Fit 2"))
27
28
29ggplot(data = exam_dat, aes(x = x, y = y, colour = fit)) +
30 geom_line() +
31 labs(x = "Examination", y = "s(Examination)") +
32 theme_bw() +
33 theme(panel.grid.major = element_blank(), panel.grid.minor = element_blank())
34gridExtra::grid.arrange(
35 trt_fit1[["plots"]][[2]]$ggObj,
36 trt_fit2[["plots"]][[2]]$ggObj,
37 nrow = 1)
38
Created on 2022-02-18 by the reprex package (v2.0.1)
QUESTION
Coefficient plot - Increase gap between rows and alternative background colors in rows
Asked 2022-Jan-29 at 17:41I have created this coefficient plot. However, I cannot increase the gap between rows. I also like to add an alternative background colour of row (like row-wise grey then white then grey ) to make it easier for the reader to read the plot. Would you please support improving its visualization?
I used the following code to create this plot.
1mydf <- data.frame(
2 SubgroupH=c('Age',NA,NA,NA,NA,'Marital or Union Status',NA,NA, NA, 'Place of Residence', NA, NA, 'Education', NA, NA, NA, NA,'Occupation', NA, NA, NA, NA, 'Wealth', NA, NA, NA, NA, NA, 'Reading newspaper or magazine', NA, NA, NA, 'Frequency of watching television', NA, NA, NA, 'Frequency of listening radio', NA, NA, NA ),
3 Subgroup=c(NA,'15-19','20-29','30-39','40-49', NA, 'Currently Married or Union', 'Never Married or Union','Formally Married or Union', NA, 'Rural', 'Urban', NA, 'Higher', 'Secondary', 'Primary', 'No eduction', NA, 'Not working', 'Professional/technical/managerial/services', 'Agriculture', 'Skilled/unskilled manual', NA, 'Poorest', 'Poorer', 'Middle','Richer', 'Richest', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week'),
4 AdjustedOR=c(NA,1,'2.76 (2.49-3.07)','3.68 (3.26-4.15)','4.61 (4.06-5.23)',NA,1,'1.03 (0.94-1.13)', '1.26 (1.04-1.54)', NA, 1,'1.12 (1.04-1.21)', NA, 1, '1.42 (1.30-1.56)', '2.09 (1.85-2.36)', '2.20 (1.93-2.49)', NA, 1, '1.39 (1.25-1.54)', '1.37 (1.24-1.51)', '1.55 (1.39-1.72)', NA, 1, '0.91 (0.84-0.99)', '0.77 (0.71-0.84)', '0.72 (0.65-0.79)', '0.61 (0.54-0.69)', NA, 1, '1 (0.91-1.10)', '0.92 (0.85-0.998)', NA, 1, '1.19 (1.07-1.31)', '1.29 (1.19-1.40)', NA, 1, '1.19 (1.09-1.30)', '1.13 (1.05-1.21)'),
5 OddsRatio=c(NA,1,2.76,3.68,4.61, NA,1,1.03, 1.26, NA, 1,1.12, NA, 1, 1.42, 2.09, 2.20, NA, 1, 1.39, 1.37, 1.55, NA, 1, 0.91, 0.77, 0.78, 0.61 , NA, 1, 1,0.92, NA, 1,1.19,1.29, NA, 1, 1.19, 1.13),
6 ORLower=c(NA,NA,2.49,3.26,4.06,NA,NA,0.94, 1.04, NA, NA,1.04, NA, NA, 1.30,1.85, 1.93, NA, NA,1.25, 1.24, 1.39, NA, NA, 0.84, 0.71, 0.65, 0.54, NA, NA, 0.91, 0.85, NA, NA, 1.07, 1.19, NA, NA,1.09, 1.05),
7 ORUpper=c(NA,NA,3.07,4.15,5.23,NA,NA,1.13, 1.54, NA, NA,1.21, NA, NA, 1.56, 2.36, 2.49, NA, NA, 1.54, 1.51,1.72, NA, NA, 0.99, 0.84, 0.79, 0.69, NA, NA, 1.10, 0.998, NA, NA, 1.31, 1.40, NA, NA, 1.30,1.21),
8 Pvalue=c(NA,NA,'< 0.001','< 0.001','< 0.001', NA,NA, 0.518, 0.021, NA, NA, 0.003, NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, 0.029, '< 0.001','< 0.001','< 0.001', NA, NA, 0.993, 0.045, NA, NA, '< 0.001','< 0.001',NA, NA, '< 0.001', 0.002),
9 stringsAsFactors=FALSE )
10
11#png('temp.png', width=8, height=4, units='in', res=400)
12
13rowseq <- seq(nrow(mydf),1)
14par(mai=c(0.7,0,0,0))
15plot(mydf$OddsRatio, rowseq, pch=15,
16 xlim=c(-0.8,6.2), ylim=c(0,42),
17 xlab='', ylab='', yaxt='n', xaxt='n',
18 bty='n')
19axis(1, seq(0.5, 5,by=0.5), cex.axis=1)
20
21segments(1,-1,1,40.20, lty=3, )
22segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
23
24mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=1, font=2)
25
26text(-1,42, "Factors", cex=1.4, font=2, pos=4)
27t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
28text(-1,rowseq, t1h, cex=1.3, pos=4, font=2)
29t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
30text(-0.98,rowseq, t1, cex=1.3, pos=4)
31
32text(4.6,42, "Adjusted Odds Ratio (95% CI)", cex=1.4, font=2, pos=4)
33t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR,big.mark=","), '')
34text(6, rowseq, t2, cex=1.3, pos=2)
35
36text(6,42, "P-value", cex=1.4, font=2, pos=4)
37t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
38text(6,rowseq, t4, cex=1.3, pos=4)
39ANSWER
Answered 2022-Jan-29 at 09:56You could play with flexible and different cex and adjust with the png parameters. This looks already better. For line-by-line gray shading we can simply use abline with modulo 2.
1mydf <- data.frame(
2 SubgroupH=c('Age',NA,NA,NA,NA,'Marital or Union Status',NA,NA, NA, 'Place of Residence', NA, NA, 'Education', NA, NA, NA, NA,'Occupation', NA, NA, NA, NA, 'Wealth', NA, NA, NA, NA, NA, 'Reading newspaper or magazine', NA, NA, NA, 'Frequency of watching television', NA, NA, NA, 'Frequency of listening radio', NA, NA, NA ),
3 Subgroup=c(NA,'15-19','20-29','30-39','40-49', NA, 'Currently Married or Union', 'Never Married or Union','Formally Married or Union', NA, 'Rural', 'Urban', NA, 'Higher', 'Secondary', 'Primary', 'No eduction', NA, 'Not working', 'Professional/technical/managerial/services', 'Agriculture', 'Skilled/unskilled manual', NA, 'Poorest', 'Poorer', 'Middle','Richer', 'Richest', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week'),
4 AdjustedOR=c(NA,1,'2.76 (2.49-3.07)','3.68 (3.26-4.15)','4.61 (4.06-5.23)',NA,1,'1.03 (0.94-1.13)', '1.26 (1.04-1.54)', NA, 1,'1.12 (1.04-1.21)', NA, 1, '1.42 (1.30-1.56)', '2.09 (1.85-2.36)', '2.20 (1.93-2.49)', NA, 1, '1.39 (1.25-1.54)', '1.37 (1.24-1.51)', '1.55 (1.39-1.72)', NA, 1, '0.91 (0.84-0.99)', '0.77 (0.71-0.84)', '0.72 (0.65-0.79)', '0.61 (0.54-0.69)', NA, 1, '1 (0.91-1.10)', '0.92 (0.85-0.998)', NA, 1, '1.19 (1.07-1.31)', '1.29 (1.19-1.40)', NA, 1, '1.19 (1.09-1.30)', '1.13 (1.05-1.21)'),
5 OddsRatio=c(NA,1,2.76,3.68,4.61, NA,1,1.03, 1.26, NA, 1,1.12, NA, 1, 1.42, 2.09, 2.20, NA, 1, 1.39, 1.37, 1.55, NA, 1, 0.91, 0.77, 0.78, 0.61 , NA, 1, 1,0.92, NA, 1,1.19,1.29, NA, 1, 1.19, 1.13),
6 ORLower=c(NA,NA,2.49,3.26,4.06,NA,NA,0.94, 1.04, NA, NA,1.04, NA, NA, 1.30,1.85, 1.93, NA, NA,1.25, 1.24, 1.39, NA, NA, 0.84, 0.71, 0.65, 0.54, NA, NA, 0.91, 0.85, NA, NA, 1.07, 1.19, NA, NA,1.09, 1.05),
7 ORUpper=c(NA,NA,3.07,4.15,5.23,NA,NA,1.13, 1.54, NA, NA,1.21, NA, NA, 1.56, 2.36, 2.49, NA, NA, 1.54, 1.51,1.72, NA, NA, 0.99, 0.84, 0.79, 0.69, NA, NA, 1.10, 0.998, NA, NA, 1.31, 1.40, NA, NA, 1.30,1.21),
8 Pvalue=c(NA,NA,'< 0.001','< 0.001','< 0.001', NA,NA, 0.518, 0.021, NA, NA, 0.003, NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, 0.029, '< 0.001','< 0.001','< 0.001', NA, NA, 0.993, 0.045, NA, NA, '< 0.001','< 0.001',NA, NA, '< 0.001', 0.002),
9 stringsAsFactors=FALSE )
10
11#png('temp.png', width=8, height=4, units='in', res=400)
12
13rowseq <- seq(nrow(mydf),1)
14par(mai=c(0.7,0,0,0))
15plot(mydf$OddsRatio, rowseq, pch=15,
16 xlim=c(-0.8,6.2), ylim=c(0,42),
17 xlab='', ylab='', yaxt='n', xaxt='n',
18 bty='n')
19axis(1, seq(0.5, 5,by=0.5), cex.axis=1)
20
21segments(1,-1,1,40.20, lty=3, )
22segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
23
24mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=1, font=2)
25
26text(-1,42, "Factors", cex=1.4, font=2, pos=4)
27t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
28text(-1,rowseq, t1h, cex=1.3, pos=4, font=2)
29t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
30text(-0.98,rowseq, t1, cex=1.3, pos=4)
31
32text(4.6,42, "Adjusted Odds Ratio (95% CI)", cex=1.4, font=2, pos=4)
33t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR,big.mark=","), '')
34text(6, rowseq, t2, cex=1.3, pos=2)
35
36text(6,42, "P-value", cex=1.4, font=2, pos=4)
37t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
38text(6,rowseq, t4, cex=1.3, pos=4)
39cex11 <- 1
40cex12 <- 1.2
41cex42 <- cex41 <- cex23 <- cex22 <- cex21 <- 1.3
42
43
44png('temp.png', width=23, height=12, units='in', res=400)
45
46par(mai=c(0.7, 0, 0, 0))
47
48rowseq <- seq(nrow(mydf), 1)
49plot(mydf$OddsRatio, rowseq, xlim=c(-0.8, 6.2), ylim=c(0, 42),
50 xlab='', ylab='', yaxt='n', xaxt='n', bty='n')
51abline(h=rowseq[rowseq %% 2 != 0], lwd=25, col='grey90') ## grey shading
52points(mydf$OddsRatio, rowseq, pch=15)
53axis(1, seq(0.5, 5, by=0.5), cex.axis=cex11)
54segments(1, -1, 1, 40.20, lty=3, )
55segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
56mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=cex12, font=2)
57text(-1, 42, "Factors", cex=cex21, font=2, pos=4)
58t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
59text(-1, rowseq, t1h, cex=cex22, pos=4, font=2)
60t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
61text(-0.98, rowseq, t1, cex=cex23, pos=4)
62text(4.6, 42, "Adjusted Odds Ratio (95% CI)", cex=cex41, font=2, pos=4)
63t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
64text(6, rowseq, t2, cex=cex42, pos=2)
65text(6, 42, "P-value", cex=cex41, font=2, pos=4)
66t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
67text(6, rowseq, t4, cex=cex41, pos=4)
68
69dev.off()
70
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
Data:
1mydf <- data.frame(
2 SubgroupH=c('Age',NA,NA,NA,NA,'Marital or Union Status',NA,NA, NA, 'Place of Residence', NA, NA, 'Education', NA, NA, NA, NA,'Occupation', NA, NA, NA, NA, 'Wealth', NA, NA, NA, NA, NA, 'Reading newspaper or magazine', NA, NA, NA, 'Frequency of watching television', NA, NA, NA, 'Frequency of listening radio', NA, NA, NA ),
3 Subgroup=c(NA,'15-19','20-29','30-39','40-49', NA, 'Currently Married or Union', 'Never Married or Union','Formally Married or Union', NA, 'Rural', 'Urban', NA, 'Higher', 'Secondary', 'Primary', 'No eduction', NA, 'Not working', 'Professional/technical/managerial/services', 'Agriculture', 'Skilled/unskilled manual', NA, 'Poorest', 'Poorer', 'Middle','Richer', 'Richest', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week', NA, 'Not at all', 'Less than once a week', 'At least once a week'),
4 AdjustedOR=c(NA,1,'2.76 (2.49-3.07)','3.68 (3.26-4.15)','4.61 (4.06-5.23)',NA,1,'1.03 (0.94-1.13)', '1.26 (1.04-1.54)', NA, 1,'1.12 (1.04-1.21)', NA, 1, '1.42 (1.30-1.56)', '2.09 (1.85-2.36)', '2.20 (1.93-2.49)', NA, 1, '1.39 (1.25-1.54)', '1.37 (1.24-1.51)', '1.55 (1.39-1.72)', NA, 1, '0.91 (0.84-0.99)', '0.77 (0.71-0.84)', '0.72 (0.65-0.79)', '0.61 (0.54-0.69)', NA, 1, '1 (0.91-1.10)', '0.92 (0.85-0.998)', NA, 1, '1.19 (1.07-1.31)', '1.29 (1.19-1.40)', NA, 1, '1.19 (1.09-1.30)', '1.13 (1.05-1.21)'),
5 OddsRatio=c(NA,1,2.76,3.68,4.61, NA,1,1.03, 1.26, NA, 1,1.12, NA, 1, 1.42, 2.09, 2.20, NA, 1, 1.39, 1.37, 1.55, NA, 1, 0.91, 0.77, 0.78, 0.61 , NA, 1, 1,0.92, NA, 1,1.19,1.29, NA, 1, 1.19, 1.13),
6 ORLower=c(NA,NA,2.49,3.26,4.06,NA,NA,0.94, 1.04, NA, NA,1.04, NA, NA, 1.30,1.85, 1.93, NA, NA,1.25, 1.24, 1.39, NA, NA, 0.84, 0.71, 0.65, 0.54, NA, NA, 0.91, 0.85, NA, NA, 1.07, 1.19, NA, NA,1.09, 1.05),
7 ORUpper=c(NA,NA,3.07,4.15,5.23,NA,NA,1.13, 1.54, NA, NA,1.21, NA, NA, 1.56, 2.36, 2.49, NA, NA, 1.54, 1.51,1.72, NA, NA, 0.99, 0.84, 0.79, 0.69, NA, NA, 1.10, 0.998, NA, NA, 1.31, 1.40, NA, NA, 1.30,1.21),
8 Pvalue=c(NA,NA,'< 0.001','< 0.001','< 0.001', NA,NA, 0.518, 0.021, NA, NA, 0.003, NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, '< 0.001', '< 0.001', '< 0.001', NA, NA, 0.029, '< 0.001','< 0.001','< 0.001', NA, NA, 0.993, 0.045, NA, NA, '< 0.001','< 0.001',NA, NA, '< 0.001', 0.002),
9 stringsAsFactors=FALSE )
10
11#png('temp.png', width=8, height=4, units='in', res=400)
12
13rowseq <- seq(nrow(mydf),1)
14par(mai=c(0.7,0,0,0))
15plot(mydf$OddsRatio, rowseq, pch=15,
16 xlim=c(-0.8,6.2), ylim=c(0,42),
17 xlab='', ylab='', yaxt='n', xaxt='n',
18 bty='n')
19axis(1, seq(0.5, 5,by=0.5), cex.axis=1)
20
21segments(1,-1,1,40.20, lty=3, )
22segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
23
24mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=1, font=2)
25
26text(-1,42, "Factors", cex=1.4, font=2, pos=4)
27t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
28text(-1,rowseq, t1h, cex=1.3, pos=4, font=2)
29t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
30text(-0.98,rowseq, t1, cex=1.3, pos=4)
31
32text(4.6,42, "Adjusted Odds Ratio (95% CI)", cex=1.4, font=2, pos=4)
33t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR,big.mark=","), '')
34text(6, rowseq, t2, cex=1.3, pos=2)
35
36text(6,42, "P-value", cex=1.4, font=2, pos=4)
37t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
38text(6,rowseq, t4, cex=1.3, pos=4)
39cex11 <- 1
40cex12 <- 1.2
41cex42 <- cex41 <- cex23 <- cex22 <- cex21 <- 1.3
42
43
44png('temp.png', width=23, height=12, units='in', res=400)
45
46par(mai=c(0.7, 0, 0, 0))
47
48rowseq <- seq(nrow(mydf), 1)
49plot(mydf$OddsRatio, rowseq, xlim=c(-0.8, 6.2), ylim=c(0, 42),
50 xlab='', ylab='', yaxt='n', xaxt='n', bty='n')
51abline(h=rowseq[rowseq %% 2 != 0], lwd=25, col='grey90') ## grey shading
52points(mydf$OddsRatio, rowseq, pch=15)
53axis(1, seq(0.5, 5, by=0.5), cex.axis=cex11)
54segments(1, -1, 1, 40.20, lty=3, )
55segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
56mtext('Adjusted Odds Ratio (95% CI)', 1, line=2, at=1.2, cex=cex12, font=2)
57text(-1, 42, "Factors", cex=cex21, font=2, pos=4)
58t1h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
59text(-1, rowseq, t1h, cex=cex22, pos=4, font=2)
60t1 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
61text(-0.98, rowseq, t1, cex=cex23, pos=4)
62text(4.6, 42, "Adjusted Odds Ratio (95% CI)", cex=cex41, font=2, pos=4)
63t2 <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
64text(6, rowseq, t2, cex=cex42, pos=2)
65text(6, 42, "P-value", cex=cex41, font=2, pos=4)
66t4 <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
67text(6, rowseq, t4, cex=cex41, pos=4)
68
69dev.off()
70## parameters
71rowseq <- rev(seq_len(dim(mydf)[1]))
72rg <- range(mydf[c('ORLower', 'ORUpper')], na.rm=TRUE)
73
74t2h <- ifelse(!is.na(mydf$SubgroupH), mydf$SubgroupH, '')
75t2 <- ifelse(!is.na(mydf$Subgroup), mydf$Subgroup, '')
76t4or <- ifelse(!is.na(mydf$AdjustedOR), format(mydf$AdjustedOR, big.mark=", "), '')
77t4p <- ifelse(!is.na(mydf$Pvalue), mydf$Pvalue, '')
78
79cexh1 <- 1.3
80cexh2 <- 1.2
81cext <- 1.1
82## plot
83png('temp.png', width=18, height=12, units='in', res=400)
84
85op <- par(mar=c(5, 18.5, 4, 15)+.1)
86
87plot(mydf$OddsRatio, rowseq, type='n', xlim=rg, axes=FALSE, xlab='', ylab='')
88## content
89abline(h=rowseq[rowseq %% 2 == 0], lwd=20, col='grey95', xpd=TRUE) ## grey shades
90points(mydf$OddsRatio, rowseq, pch=15)
91segments(1, 0, 1, max(rowseq)*1.025, lty=3)
92segments(mydf$ORLower, rowseq, mydf$ORUpper, rowseq)
93## margin 1
94axis(1, seq(0.5, 5, by=0.5), cex.axis=cex11)
95mtext('Adjusted Odds Ratio (95% CI)', 1, line=2.5, at=1.2, cex=cexh1, font=2)
96## margin 2
97mtext("Factors", 2, 17.5, at=max(rowseq)*1.03, las=2, adj=0, font=2, cex=cexh1)
98mtext(t2h, 2, 17.5, at=rowseq, las=2, adj=0, font=2, cex=cexh2)
99mtext(t2, 2, 17, at=rowseq, las=2, adj=0, cex=cext)
100## margin 4
101mtext("Adjusted Odds Ratio (95% CI)", 4, 7, at=max(rowseq)*1.03, las=2, adj=1,
102 font=2, cex=cexh1)
103mtext(t4or, 4, 7, at=rowseq, las=2, adj=1, cex=cext)
104mtext("P-value", 4, 12, at=max(rowseq)*1.03, las=2, adj=1, font=2, cex=cexh1)
105mtext(t4p, 4, 12, at=rowseq, las=2, adj=1, cex=cext)
106
107par(op)
108
109dev.off()
110mydf <- structure(list(SubgroupH = c("Age", NA, NA, NA, NA, "Marital or Union Status",
111NA, NA, NA, "Place of Residence", NA, NA, "Education", NA, NA,
112NA, NA, "Occupation", NA, NA, NA, NA, "Wealth", NA, NA, NA, NA,
113NA, "Reading newspaper or magazine", NA, NA, NA, "Frequency of watching television",
114NA, NA, NA, "Frequency of listening radio", NA, NA, NA), Subgroup = c(NA,
115"15-19", "20-29", "30-39", "40-49", NA, "Currently Married or Union",
116"Never Married or Union", "Formally Married or Union", NA, "Rural",
117"Urban", NA, "Higher", "Secondary", "Primary", "No eduction",
118NA, "Not working", "Professional/technical/managerial/services",
119"Agriculture", "Skilled/unskilled manual", NA, "Poorest", "Poorer",
120"Middle", "Richer", "Richest", NA, "Not at all", "Less than once a week",
121"At least once a week", NA, "Not at all", "Less than once a week",
122"At least once a week", NA, "Not at all", "Less than once a week",
123"At least once a week"), AdjustedOR = c(NA, "1", "2.76 (2.49-3.07)",
124"3.68 (3.26-4.15)", "4.61 (4.06-5.23)", NA, "1", "1.03 (0.94-1.13)",
125"1.26 (1.04-1.54)", NA, "1", "1.12 (1.04-1.21)", NA, "1", "1.42 (1.30-1.56)",
126"2.09 (1.85-2.36)", "2.20 (1.93-2.49)", NA, "1", "1.39 (1.25-1.54)",
127"1.37 (1.24-1.51)", "1.55 (1.39-1.72)", NA, "1", "0.91 (0.84-0.99)",
128"0.77 (0.71-0.84)", "0.72 (0.65-0.79)", "0.61 (0.54-0.69)", NA,
129"1", "1 (0.91-1.10)", "0.92 (0.85-0.998)", NA, "1", "1.19 (1.07-1.31)",
130"1.29 (1.19-1.40)", NA, "1", "1.19 (1.09-1.30)", "1.13 (1.05-1.21)"
131), OddsRatio = c(NA, 1, 2.76, 3.68, 4.61, NA, 1, 1.03, 1.26,
132NA, 1, 1.12, NA, 1, 1.42, 2.09, 2.2, NA, 1, 1.39, 1.37, 1.55,
133NA, 1, 0.91, 0.77, 0.78, 0.61, NA, 1, 1, 0.92, NA, 1, 1.19, 1.29,
134NA, 1, 1.19, 1.13), ORLower = c(NA, NA, 2.49, 3.26, 4.06, NA,
135NA, 0.94, 1.04, NA, NA, 1.04, NA, NA, 1.3, 1.85, 1.93, NA, NA,
1361.25, 1.24, 1.39, NA, NA, 0.84, 0.71, 0.65, 0.54, NA, NA, 0.91,
1370.85, NA, NA, 1.07, 1.19, NA, NA, 1.09, 1.05), ORUpper = c(NA,
138NA, 3.07, 4.15, 5.23, NA, NA, 1.13, 1.54, NA, NA, 1.21, NA, NA,
1391.56, 2.36, 2.49, NA, NA, 1.54, 1.51, 1.72, NA, NA, 0.99, 0.84,
1400.79, 0.69, NA, NA, 1.1, 0.998, NA, NA, 1.31, 1.4, NA, NA, 1.3,
1411.21), Pvalue = c(NA, NA, "< 0.001", "< 0.001", "< 0.001", NA,
142NA, "0.518", "0.021", NA, NA, "0.003", NA, NA, "< 0.001", "< 0.001",
143"< 0.001", NA, NA, "< 0.001", "< 0.001", "< 0.001", NA, NA, "0.029",
144"< 0.001", "< 0.001", "< 0.001", NA, NA, "0.993", "0.045", NA,
145NA, "< 0.001", "< 0.001", NA, NA, "< 0.001", "0.002")), class = "data.frame", row.names = c(NA,
146-40L))
147QUESTION
MySQL SQL Performance need some improvement
Asked 2022-Jan-13 at 01:35I have worked my way around many challenges with MySQL, and i think right now i am able to build everything that i need, to get something to work. But now, for a pretty huge SQL statement that returns a lot of data, i need to work on MySQL performance for the first time.
I was hoping someone here could help me find out why the following statement is so incredibly slow. It takes over 3 minutes to collect 740 results out of different tables. The biggest table beeing the "reports" table, consisting of somewhere over 20.000 entries at the moment.
I can also educate myself if someone could just point me in the right direction. I don't even know where to search for answers for my current problem.
Okay, so here is the statement that i am talking about. Maybe, if someone has enough experience with SQL performance, something just right away jumps at them. I would be happy for any kind of feedback. I'll elaborate on the statement right after the code itself:
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46So what i have here is the following: reports (R_*) - This is the main table that is queried. I need some of it's data, but it's also the filter, since i only need results between specific timestamps.
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68userreports (UR_*) - Delivers some data that is calculated from the sourcedata in reports
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86customerreports (CR_*) - Same as userreports, but with calculated data from the customers perspective
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109users (U_*) - Obviously delivers Data to the user that created the report, e.g. name,...
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users` (
110 `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111 `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112 `U_PWInitial` tinyint(1) NOT NULL,
113 `U_FailedAttempts` int(1) NOT NULL,
114 `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115 `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116 `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117 `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118 `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119 `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120 `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121 `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122 `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123 `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124 `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125 `U_Birthdate` date NOT NULL,
126 `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127 `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128 `U_Severelydisabled` tinyint(1) NOT NULL,
129 `U_Severelydisabledspecify` int(3) NOT NULL,
130 `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131 `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132 `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133 `U_CLID` tinyint(1) NOT NULL,
134 `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135 `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136 `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137 `U_INID` int(11) DEFAULT NULL,
138 `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139 `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140 `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141 `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142 `U_Entry` date NOT NULL,
143 `U_TEntry` date NOT NULL,
144 `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145 `U_Hourscarryover` decimal(20, 2) NOT NULL,
146 `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147 `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148 `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149 `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150 `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151 `U_DeleteDate` date DEFAULT NULL,
152 PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154customers (C_*) - Same as users, but for the data of the customer that the user worked on
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users` (
110 `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111 `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112 `U_PWInitial` tinyint(1) NOT NULL,
113 `U_FailedAttempts` int(1) NOT NULL,
114 `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115 `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116 `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117 `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118 `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119 `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120 `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121 `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122 `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123 `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124 `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125 `U_Birthdate` date NOT NULL,
126 `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127 `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128 `U_Severelydisabled` tinyint(1) NOT NULL,
129 `U_Severelydisabledspecify` int(3) NOT NULL,
130 `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131 `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132 `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133 `U_CLID` tinyint(1) NOT NULL,
134 `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135 `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136 `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137 `U_INID` int(11) DEFAULT NULL,
138 `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139 `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140 `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141 `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142 `U_Entry` date NOT NULL,
143 `U_TEntry` date NOT NULL,
144 `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145 `U_Hourscarryover` decimal(20, 2) NOT NULL,
146 `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147 `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148 `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149 `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150 `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151 `U_DeleteDate` date DEFAULT NULL,
152 PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154CREATE TABLE `customers` (
155 `C_ID` int(10) NOT NULL AUTO_INCREMENT,
156 `C_MID` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
157 `C_Active` tinyint(1) NOT NULL,
158 `C_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci,
159 `C_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
160 `C_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
161 `C_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
162 `C_Birthdate` date NOT NULL,
163 `C_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
164 `C_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
165 `C_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
166 `C_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167 `C_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
168 `C_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
169 `C_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
170 `C_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
171 `C_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
172 `C_Insurancenumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
173 `C_INID` int(11) DEFAULT NULL,
174 `C_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
175 `C_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
176 `C_Contact1` text CHARACTER SET utf8 COLLATE utf8_general_ci,
177 `C_Contact2` text CHARACTER SET utf8 COLLATE utf8_general_ci,
178 `C_ContactChoice` int(1) DEFAULT 0,
179 `C_DeleteDate` date DEFAULT NULL,
180 `C_DeactivationDate` date DEFAULT NULL,
181 `C_CreationDate` date DEFAULT NULL,
182 `C_DeceasedDate` date DEFAULT NULL,
183 PRIMARY KEY (`C_ID`) USING BTREE
184) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
185integration (I_*) - Provides data on whether or not the report is already integrated (and can no longer be changed)
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users` (
110 `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111 `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112 `U_PWInitial` tinyint(1) NOT NULL,
113 `U_FailedAttempts` int(1) NOT NULL,
114 `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115 `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116 `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117 `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118 `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119 `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120 `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121 `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122 `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123 `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124 `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125 `U_Birthdate` date NOT NULL,
126 `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127 `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128 `U_Severelydisabled` tinyint(1) NOT NULL,
129 `U_Severelydisabledspecify` int(3) NOT NULL,
130 `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131 `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132 `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133 `U_CLID` tinyint(1) NOT NULL,
134 `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135 `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136 `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137 `U_INID` int(11) DEFAULT NULL,
138 `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139 `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140 `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141 `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142 `U_Entry` date NOT NULL,
143 `U_TEntry` date NOT NULL,
144 `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145 `U_Hourscarryover` decimal(20, 2) NOT NULL,
146 `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147 `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148 `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149 `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150 `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151 `U_DeleteDate` date DEFAULT NULL,
152 PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154CREATE TABLE `customers` (
155 `C_ID` int(10) NOT NULL AUTO_INCREMENT,
156 `C_MID` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
157 `C_Active` tinyint(1) NOT NULL,
158 `C_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci,
159 `C_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
160 `C_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
161 `C_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
162 `C_Birthdate` date NOT NULL,
163 `C_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
164 `C_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
165 `C_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
166 `C_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167 `C_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
168 `C_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
169 `C_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
170 `C_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
171 `C_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
172 `C_Insurancenumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
173 `C_INID` int(11) DEFAULT NULL,
174 `C_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
175 `C_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
176 `C_Contact1` text CHARACTER SET utf8 COLLATE utf8_general_ci,
177 `C_Contact2` text CHARACTER SET utf8 COLLATE utf8_general_ci,
178 `C_ContactChoice` int(1) DEFAULT 0,
179 `C_DeleteDate` date DEFAULT NULL,
180 `C_DeactivationDate` date DEFAULT NULL,
181 `C_CreationDate` date DEFAULT NULL,
182 `C_DeceasedDate` date DEFAULT NULL,
183 PRIMARY KEY (`C_ID`) USING BTREE
184) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
185CREATE TABLE `integration` (
186 `I_ID` int(11) NOT NULL AUTO_INCREMENT,
187 `I_UID` int(11) NOT NULL,
188 `I_LastIntegration` date NOT NULL DEFAULT '1970-01-01',
189 `I_SumFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
190 `I_OldHolidays` int(5) NOT NULL DEFAULT 0,
191 PRIMARY KEY (`I_ID`) USING BTREE
192) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
193customerterms (CT_*) - In this case only provides if the specified customer needs to sign the report
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users` (
110 `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111 `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112 `U_PWInitial` tinyint(1) NOT NULL,
113 `U_FailedAttempts` int(1) NOT NULL,
114 `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115 `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116 `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117 `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118 `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119 `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120 `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121 `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122 `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123 `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124 `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125 `U_Birthdate` date NOT NULL,
126 `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127 `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128 `U_Severelydisabled` tinyint(1) NOT NULL,
129 `U_Severelydisabledspecify` int(3) NOT NULL,
130 `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131 `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132 `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133 `U_CLID` tinyint(1) NOT NULL,
134 `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135 `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136 `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137 `U_INID` int(11) DEFAULT NULL,
138 `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139 `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140 `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141 `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142 `U_Entry` date NOT NULL,
143 `U_TEntry` date NOT NULL,
144 `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145 `U_Hourscarryover` decimal(20, 2) NOT NULL,
146 `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147 `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148 `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149 `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150 `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151 `U_DeleteDate` date DEFAULT NULL,
152 PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154CREATE TABLE `customers` (
155 `C_ID` int(10) NOT NULL AUTO_INCREMENT,
156 `C_MID` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
157 `C_Active` tinyint(1) NOT NULL,
158 `C_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci,
159 `C_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
160 `C_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
161 `C_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
162 `C_Birthdate` date NOT NULL,
163 `C_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
164 `C_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
165 `C_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
166 `C_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167 `C_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
168 `C_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
169 `C_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
170 `C_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
171 `C_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
172 `C_Insurancenumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
173 `C_INID` int(11) DEFAULT NULL,
174 `C_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
175 `C_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
176 `C_Contact1` text CHARACTER SET utf8 COLLATE utf8_general_ci,
177 `C_Contact2` text CHARACTER SET utf8 COLLATE utf8_general_ci,
178 `C_ContactChoice` int(1) DEFAULT 0,
179 `C_DeleteDate` date DEFAULT NULL,
180 `C_DeactivationDate` date DEFAULT NULL,
181 `C_CreationDate` date DEFAULT NULL,
182 `C_DeceasedDate` date DEFAULT NULL,
183 PRIMARY KEY (`C_ID`) USING BTREE
184) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
185CREATE TABLE `integration` (
186 `I_ID` int(11) NOT NULL AUTO_INCREMENT,
187 `I_UID` int(11) NOT NULL,
188 `I_LastIntegration` date NOT NULL DEFAULT '1970-01-01',
189 `I_SumFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
190 `I_OldHolidays` int(5) NOT NULL DEFAULT 0,
191 PRIMARY KEY (`I_ID`) USING BTREE
192) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
193CREATE TABLE `customerterms` (
194 `CT_ID` int(50) NOT NULL AUTO_INCREMENT,
195 `CT_CID` int(10) NOT NULL,
196 `CT_From` date NOT NULL,
197 `CT_To` date NOT NULL,
198 `CT_Hourly` decimal(20, 2) NOT NULL,
199 `CT_FixedTravelCompensation` decimal(20, 2) NOT NULL,
200 `CT_PerKMCompensationBase` decimal(20, 2) NOT NULL,
201 `CT_PerKMCompensationAdditional` decimal(20, 2) NOT NULL,
202 `CT_MaxTravelCompensationReport` decimal(20, 2) DEFAULT NULL,
203 `CT_MaxTravelCompensationMonthly` decimal(20, 2) DEFAULT NULL,
204 `CT_FixedSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
205 `CT_PercentageSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
206 `CT_FixedSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
207 `CT_PercentageSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
208 `CT_FixedHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
209 `CT_PercentageHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
210 `CT_SigReq` int(1) NOT NULL,
211 `CT_NighttimeFrom` time(0) NOT NULL DEFAULT '00:00:00',
212 `CT_NighttimeTo` time(0) NOT NULL DEFAULT '00:00:00',
213 `CT_FixedNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
214 `CT_PercentageNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
215 `CT_StackingSurcharge` tinyint(1) NOT NULL DEFAULT 0,
216 `CT_MinimumTime` int(11) NOT NULL DEFAULT 1,
217 `CT_TimeIncrement` int(11) NOT NULL DEFAULT 1,
218 PRIMARY KEY (`CT_ID`) USING BTREE
219) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
220The server is running MySQL 5.7, has 4 processors at 4,6Ghz, and 16GB of RAM available.
Since this is a hobby project, that i am supporting small care-businesses with, to allow them easier management of their daily tasks, i can change everything here. Code, Database Layout, you name it. As long as the poor people in the office don't have to wait for 5 minutes, just to sometimes even only get a timeout...
I'll add the result of EXPLAIN as image, since i can't get it to look good otherwise...
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users` (
110 `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111 `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112 `U_PWInitial` tinyint(1) NOT NULL,
113 `U_FailedAttempts` int(1) NOT NULL,
114 `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115 `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116 `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117 `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118 `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119 `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120 `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121 `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122 `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123 `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124 `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125 `U_Birthdate` date NOT NULL,
126 `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127 `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128 `U_Severelydisabled` tinyint(1) NOT NULL,
129 `U_Severelydisabledspecify` int(3) NOT NULL,
130 `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131 `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132 `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133 `U_CLID` tinyint(1) NOT NULL,
134 `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135 `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136 `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137 `U_INID` int(11) DEFAULT NULL,
138 `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139 `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140 `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141 `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142 `U_Entry` date NOT NULL,
143 `U_TEntry` date NOT NULL,
144 `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145 `U_Hourscarryover` decimal(20, 2) NOT NULL,
146 `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147 `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148 `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149 `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150 `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151 `U_DeleteDate` date DEFAULT NULL,
152 PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154CREATE TABLE `customers` (
155 `C_ID` int(10) NOT NULL AUTO_INCREMENT,
156 `C_MID` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
157 `C_Active` tinyint(1) NOT NULL,
158 `C_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci,
159 `C_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
160 `C_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
161 `C_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
162 `C_Birthdate` date NOT NULL,
163 `C_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
164 `C_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
165 `C_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
166 `C_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167 `C_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
168 `C_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
169 `C_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
170 `C_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
171 `C_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
172 `C_Insurancenumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
173 `C_INID` int(11) DEFAULT NULL,
174 `C_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
175 `C_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
176 `C_Contact1` text CHARACTER SET utf8 COLLATE utf8_general_ci,
177 `C_Contact2` text CHARACTER SET utf8 COLLATE utf8_general_ci,
178 `C_ContactChoice` int(1) DEFAULT 0,
179 `C_DeleteDate` date DEFAULT NULL,
180 `C_DeactivationDate` date DEFAULT NULL,
181 `C_CreationDate` date DEFAULT NULL,
182 `C_DeceasedDate` date DEFAULT NULL,
183 PRIMARY KEY (`C_ID`) USING BTREE
184) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
185CREATE TABLE `integration` (
186 `I_ID` int(11) NOT NULL AUTO_INCREMENT,
187 `I_UID` int(11) NOT NULL,
188 `I_LastIntegration` date NOT NULL DEFAULT '1970-01-01',
189 `I_SumFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
190 `I_OldHolidays` int(5) NOT NULL DEFAULT 0,
191 PRIMARY KEY (`I_ID`) USING BTREE
192) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
193CREATE TABLE `customerterms` (
194 `CT_ID` int(50) NOT NULL AUTO_INCREMENT,
195 `CT_CID` int(10) NOT NULL,
196 `CT_From` date NOT NULL,
197 `CT_To` date NOT NULL,
198 `CT_Hourly` decimal(20, 2) NOT NULL,
199 `CT_FixedTravelCompensation` decimal(20, 2) NOT NULL,
200 `CT_PerKMCompensationBase` decimal(20, 2) NOT NULL,
201 `CT_PerKMCompensationAdditional` decimal(20, 2) NOT NULL,
202 `CT_MaxTravelCompensationReport` decimal(20, 2) DEFAULT NULL,
203 `CT_MaxTravelCompensationMonthly` decimal(20, 2) DEFAULT NULL,
204 `CT_FixedSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
205 `CT_PercentageSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
206 `CT_FixedSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
207 `CT_PercentageSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
208 `CT_FixedHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
209 `CT_PercentageHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
210 `CT_SigReq` int(1) NOT NULL,
211 `CT_NighttimeFrom` time(0) NOT NULL DEFAULT '00:00:00',
212 `CT_NighttimeTo` time(0) NOT NULL DEFAULT '00:00:00',
213 `CT_FixedNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
214 `CT_PercentageNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
215 `CT_StackingSurcharge` tinyint(1) NOT NULL DEFAULT 0,
216 `CT_MinimumTime` int(11) NOT NULL DEFAULT 1,
217 `CT_TimeIncrement` int(11) NOT NULL DEFAULT 1,
218 PRIMARY KEY (`CT_ID`) USING BTREE
219) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
220+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
221| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
222+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
223| 1 | SIMPLE | reports | NULL | ALL | PRIMARY,R_From,R_To | NULL | NULL | NULL | 22249 | 29.76 | Using where; Using temporary; Using filesort |
224| 1 | SIMPLE | userreports | NULL | ALL | NULL | NULL | NULL | NULL | 21359 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
225| 1 | SIMPLE | users | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dbs671769.reports.R_UID | 1 | 100.00 | NULL |
226| 1 | SIMPLE | customers | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dbs671769.reports.R_CID | 1 | 100.00 | NULL |
227| 1 | SIMPLE | customerterms | NULL | ALL | NULL | NULL | NULL | NULL | 1429 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
228| 1 | SIMPLE | integration | NULL | ALL | NULL | NULL | NULL | NULL | 1134 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
229| 1 | SIMPLE | customerreports | NULL | ALL | NULL | NULL | NULL | NULL | 9078 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
230+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
231Is there any way to consolidate all this data faster, but as reliable?
Thanks a lot in advance, for any help or idea on this.
ANSWER
Answered 2022-Jan-13 at 01:03Let's start by adding an index for each of the foreign keys used in your query -
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users` (
110 `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111 `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112 `U_PWInitial` tinyint(1) NOT NULL,
113 `U_FailedAttempts` int(1) NOT NULL,
114 `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115 `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116 `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117 `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118 `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119 `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120 `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121 `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122 `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123 `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124 `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125 `U_Birthdate` date NOT NULL,
126 `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127 `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128 `U_Severelydisabled` tinyint(1) NOT NULL,
129 `U_Severelydisabledspecify` int(3) NOT NULL,
130 `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131 `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132 `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133 `U_CLID` tinyint(1) NOT NULL,
134 `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135 `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136 `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137 `U_INID` int(11) DEFAULT NULL,
138 `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139 `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140 `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141 `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142 `U_Entry` date NOT NULL,
143 `U_TEntry` date NOT NULL,
144 `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145 `U_Hourscarryover` decimal(20, 2) NOT NULL,
146 `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147 `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148 `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149 `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150 `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151 `U_DeleteDate` date DEFAULT NULL,
152 PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154CREATE TABLE `customers` (
155 `C_ID` int(10) NOT NULL AUTO_INCREMENT,
156 `C_MID` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
157 `C_Active` tinyint(1) NOT NULL,
158 `C_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci,
159 `C_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
160 `C_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
161 `C_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
162 `C_Birthdate` date NOT NULL,
163 `C_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
164 `C_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
165 `C_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
166 `C_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167 `C_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
168 `C_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
169 `C_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
170 `C_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
171 `C_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
172 `C_Insurancenumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
173 `C_INID` int(11) DEFAULT NULL,
174 `C_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
175 `C_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
176 `C_Contact1` text CHARACTER SET utf8 COLLATE utf8_general_ci,
177 `C_Contact2` text CHARACTER SET utf8 COLLATE utf8_general_ci,
178 `C_ContactChoice` int(1) DEFAULT 0,
179 `C_DeleteDate` date DEFAULT NULL,
180 `C_DeactivationDate` date DEFAULT NULL,
181 `C_CreationDate` date DEFAULT NULL,
182 `C_DeceasedDate` date DEFAULT NULL,
183 PRIMARY KEY (`C_ID`) USING BTREE
184) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
185CREATE TABLE `integration` (
186 `I_ID` int(11) NOT NULL AUTO_INCREMENT,
187 `I_UID` int(11) NOT NULL,
188 `I_LastIntegration` date NOT NULL DEFAULT '1970-01-01',
189 `I_SumFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
190 `I_OldHolidays` int(5) NOT NULL DEFAULT 0,
191 PRIMARY KEY (`I_ID`) USING BTREE
192) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
193CREATE TABLE `customerterms` (
194 `CT_ID` int(50) NOT NULL AUTO_INCREMENT,
195 `CT_CID` int(10) NOT NULL,
196 `CT_From` date NOT NULL,
197 `CT_To` date NOT NULL,
198 `CT_Hourly` decimal(20, 2) NOT NULL,
199 `CT_FixedTravelCompensation` decimal(20, 2) NOT NULL,
200 `CT_PerKMCompensationBase` decimal(20, 2) NOT NULL,
201 `CT_PerKMCompensationAdditional` decimal(20, 2) NOT NULL,
202 `CT_MaxTravelCompensationReport` decimal(20, 2) DEFAULT NULL,
203 `CT_MaxTravelCompensationMonthly` decimal(20, 2) DEFAULT NULL,
204 `CT_FixedSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
205 `CT_PercentageSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
206 `CT_FixedSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
207 `CT_PercentageSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
208 `CT_FixedHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
209 `CT_PercentageHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
210 `CT_SigReq` int(1) NOT NULL,
211 `CT_NighttimeFrom` time(0) NOT NULL DEFAULT '00:00:00',
212 `CT_NighttimeTo` time(0) NOT NULL DEFAULT '00:00:00',
213 `CT_FixedNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
214 `CT_PercentageNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
215 `CT_StackingSurcharge` tinyint(1) NOT NULL DEFAULT 0,
216 `CT_MinimumTime` int(11) NOT NULL DEFAULT 1,
217 `CT_TimeIncrement` int(11) NOT NULL DEFAULT 1,
218 PRIMARY KEY (`CT_ID`) USING BTREE
219) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
220+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
221| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
222+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
223| 1 | SIMPLE | reports | NULL | ALL | PRIMARY,R_From,R_To | NULL | NULL | NULL | 22249 | 29.76 | Using where; Using temporary; Using filesort |
224| 1 | SIMPLE | userreports | NULL | ALL | NULL | NULL | NULL | NULL | 21359 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
225| 1 | SIMPLE | users | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dbs671769.reports.R_UID | 1 | 100.00 | NULL |
226| 1 | SIMPLE | customers | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dbs671769.reports.R_CID | 1 | 100.00 | NULL |
227| 1 | SIMPLE | customerterms | NULL | ALL | NULL | NULL | NULL | NULL | 1429 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
228| 1 | SIMPLE | integration | NULL | ALL | NULL | NULL | NULL | NULL | 1134 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
229| 1 | SIMPLE | customerreports | NULL | ALL | NULL | NULL | NULL | NULL | 9078 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
230+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
231ALTER TABLE `userreports`
232 ADD INDEX `FK_UR_RID` (`UR_RID`);
233
234ALTER TABLE `customerterms`
235 ADD INDEX `FK_CT_CID` (`CT_CID`);
236
237ALTER TABLE `integration`
238 ADD INDEX `FK_I_UID` (`I_UID`);
239
240ALTER TABLE `customerreports`
241 ADD INDEX `FK_CR_RID` (`CR_RID`);
242Please add these indices and then add the updated EXPLAIN output plus the result of the following query to your question.
1SELECT
2 R_ID,
3 R_From,
4 R_To,
5 SUM(UR_TotalTime) AS UR_TotalTime,
6 R_Reported,
7 U_ID,
8 U_Lastname,
9 U_Firstname,
10 C_ID,
11 C_Lastname,
12 C_Firstname,
13 R_Breaks,
14 MAX(CR_BID) AS CR_BID,
15 R_Type,
16 R_Distance,
17 R_AdditionalDistance,
18 R_Activities,
19 R_Description,
20 R_Signature,
21 CT_SigReq,
22 MAX(I_LastIntegration) AS I_LastIntegration
23FROM
24 reports
25 LEFT JOIN
26 userreports ON R_ID = UR_RID
27 LEFT JOIN
28 users ON R_UID = U_ID
29 LEFT JOIN
30 customers ON R_CID = C_ID
31 LEFT JOIN
32 customerterms ON CT_CID = R_CID
33 LEFT JOIN
34 integration ON R_UID = I_UID
35 LEFT JOIN
36 customerreports ON R_ID = CR_RID
37WHERE
38 (CAST(R_From AS DATE) BETWEEN CT_From AND CT_To
39 OR R_CID = 0)
40 AND ((R_From BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
41 OR (R_To BETWEEN '2021-02-01 00.00.00' AND '2021-02-28 23.59.59')
42 OR (R_From <= '2021-02-01 00.00.00'
43 AND R_To >= '2021-02-28 23.59.59'))
44GROUP BY R_ID
45ORDER BY R_From ASC
46CREATE TABLE `reports` (
47 `R_ID` int(100) NOT NULL AUTO_INCREMENT,
48 `R_Type` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
49 `R_UID` int(6) NOT NULL,
50 `R_CID` int(10) NOT NULL,
51 `R_From` datetime(0) NOT NULL,
52 `R_To` datetime(0) NOT NULL,
53 `R_Traveltime` int(11) NOT NULL,
54 `R_Breaks` int(11) NOT NULL DEFAULT 0,
55 `R_PayoutFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
56 `R_Distance` int(11) NOT NULL DEFAULT 0,
57 `R_AdditionalDistance` int(11) NOT NULL DEFAULT 0,
58 `R_Activities` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
59 `R_Description` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
60 `R_Signature` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0',
61 `R_SignatureDate` datetime(0) DEFAULT NULL,
62 `R_Reported` datetime(0) NOT NULL,
63 `R_Status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'New',
64 `R_EditedBy` int(11) DEFAULT NULL,
65 `R_EditedDateTime` datetime(0) DEFAULT NULL,
66 PRIMARY KEY (`R_ID`) USING BTREE
67) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
68CREATE TABLE `userreports` (
69 `UR_ID` int(11) NOT NULL AUTO_INCREMENT,
70 `UR_RID` int(100) NOT NULL,
71 `UR_UID` int(6) NOT NULL,
72 `UR_Date` date NOT NULL,
73 `UR_From` time(0) NOT NULL,
74 `UR_To` time(0) NOT NULL,
75 `UR_ReportedTime` decimal(20, 5) DEFAULT NULL,
76 `UR_ReportedTravel` decimal(20, 5) NOT NULL,
77 `UR_ReportedBreaks` decimal(20, 5) DEFAULT NULL,
78 `UR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
79 `UR_TotalTime` decimal(20, 5) DEFAULT NULL,
80 `UR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
81 `UR_Distance` decimal(20, 2) DEFAULT NULL,
82 `UR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
83 `UR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
84 PRIMARY KEY (`UR_ID`) USING BTREE
85) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
86CREATE TABLE `customerreports` (
87 `CR_ID` int(11) NOT NULL AUTO_INCREMENT,
88 `CR_RID` int(100) NOT NULL,
89 `CR_CID` int(6) NOT NULL,
90 `CR_Date` date NOT NULL,
91 `CR_From` time(0) NOT NULL,
92 `CR_To` time(0) NOT NULL,
93 `CR_ReportedTime` decimal(20, 2) DEFAULT NULL,
94 `CR_ReportedBreaks` decimal(20, 2) DEFAULT NULL,
95 `CR_Hourly` decimal(20, 2) DEFAULT NULL,
96 `CR_Salary` decimal(20, 2) DEFAULT NULL,
97 `CR_TotalPercentageSurcharge` decimal(20, 2) DEFAULT NULL,
98 `CR_TotalFixedSurcharge` decimal(20, 2) DEFAULT NULL,
99 `CR_TotalTime` decimal(20, 2) DEFAULT NULL,
100 `CR_TotalSalary` decimal(20, 2) DEFAULT NULL,
101 `CR_FixedSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
102 `CR_PercentageSurchargeTypes` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
103 `CR_Distance` decimal(20, 2) DEFAULT NULL,
104 `CR_AdditionalDistance` decimal(20, 2) DEFAULT NULL,
105 `CR_TravelCompensation` decimal(20, 2) DEFAULT NULL,
106 `CR_BID` int(11) NOT NULL DEFAULT 0,
107 PRIMARY KEY (`CR_ID`) USING BTREE
108) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
109CREATE TABLE `users` (
110 `U_ID` int(6) NOT NULL AUTO_INCREMENT,
111 `U_PW` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
112 `U_PWInitial` tinyint(1) NOT NULL,
113 `U_FailedAttempts` int(1) NOT NULL,
114 `U_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
115 `U_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
116 `U_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117 `U_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
118 `U_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
119 `U_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
120 `U_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
121 `U_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
122 `U_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
123 `U_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
124 `U_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
125 `U_Birthdate` date NOT NULL,
126 `U_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
127 `U_Maritalstatus` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
128 `U_Severelydisabled` tinyint(1) NOT NULL,
129 `U_Severelydisabledspecify` int(3) NOT NULL,
130 `U_Citizenship` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
131 `U_Education` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
132 `U_Vocationaltraining` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
133 `U_CLID` tinyint(1) NOT NULL,
134 `U_CLSpecify` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
135 `U_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
136 `U_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
137 `U_INID` int(11) DEFAULT NULL,
138 `U_Insurancenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
139 `U_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
140 `U_Taxidentificationnumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
141 `U_Confession` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
142 `U_Entry` date NOT NULL,
143 `U_TEntry` date NOT NULL,
144 `U_Exit` date NOT NULL DEFAULT '9999-12-31',
145 `U_Hourscarryover` decimal(20, 2) NOT NULL,
146 `U_TotalHolidayCarryover` int(11) NOT NULL DEFAULT 0,
147 `U_UsedHolidayCarryover` int(11) NOT NULL DEFAULT 0,
148 `U_SIN` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
149 `U_RVBDone` tinyint(1) NOT NULL DEFAULT 0,
150 `U_ClosedMonth` date NOT NULL DEFAULT '1970-01-01',
151 `U_DeleteDate` date DEFAULT NULL,
152 PRIMARY KEY (`U_ID`) USING BTREE
153) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
154CREATE TABLE `customers` (
155 `C_ID` int(10) NOT NULL AUTO_INCREMENT,
156 `C_MID` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
157 `C_Active` tinyint(1) NOT NULL,
158 `C_Email` text CHARACTER SET utf8 COLLATE utf8_general_ci,
159 `C_Title` text CHARACTER SET utf8 COLLATE utf8_general_ci,
160 `C_Firstname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
161 `C_Lastname` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
162 `C_Birthdate` date NOT NULL,
163 `C_ETC` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
164 `C_Street` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
165 `C_Housenumber` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
166 `C_Code` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
167 `C_City` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
168 `C_Phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
169 `C_Mobile` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
170 `C_IBAN` varchar(27) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
171 `C_BIC` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
172 `C_Insurancenumber` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
173 `C_INID` int(11) DEFAULT NULL,
174 `C_Insurancetype` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
175 `C_Sex` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
176 `C_Contact1` text CHARACTER SET utf8 COLLATE utf8_general_ci,
177 `C_Contact2` text CHARACTER SET utf8 COLLATE utf8_general_ci,
178 `C_ContactChoice` int(1) DEFAULT 0,
179 `C_DeleteDate` date DEFAULT NULL,
180 `C_DeactivationDate` date DEFAULT NULL,
181 `C_CreationDate` date DEFAULT NULL,
182 `C_DeceasedDate` date DEFAULT NULL,
183 PRIMARY KEY (`C_ID`) USING BTREE
184) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
185CREATE TABLE `integration` (
186 `I_ID` int(11) NOT NULL AUTO_INCREMENT,
187 `I_UID` int(11) NOT NULL,
188 `I_LastIntegration` date NOT NULL DEFAULT '1970-01-01',
189 `I_SumFlextime` decimal(20, 2) NOT NULL DEFAULT 0.00,
190 `I_OldHolidays` int(5) NOT NULL DEFAULT 0,
191 PRIMARY KEY (`I_ID`) USING BTREE
192) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
193CREATE TABLE `customerterms` (
194 `CT_ID` int(50) NOT NULL AUTO_INCREMENT,
195 `CT_CID` int(10) NOT NULL,
196 `CT_From` date NOT NULL,
197 `CT_To` date NOT NULL,
198 `CT_Hourly` decimal(20, 2) NOT NULL,
199 `CT_FixedTravelCompensation` decimal(20, 2) NOT NULL,
200 `CT_PerKMCompensationBase` decimal(20, 2) NOT NULL,
201 `CT_PerKMCompensationAdditional` decimal(20, 2) NOT NULL,
202 `CT_MaxTravelCompensationReport` decimal(20, 2) DEFAULT NULL,
203 `CT_MaxTravelCompensationMonthly` decimal(20, 2) DEFAULT NULL,
204 `CT_FixedSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
205 `CT_PercentageSaturdaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
206 `CT_FixedSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
207 `CT_PercentageSundaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
208 `CT_FixedHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
209 `CT_PercentageHolidaySurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
210 `CT_SigReq` int(1) NOT NULL,
211 `CT_NighttimeFrom` time(0) NOT NULL DEFAULT '00:00:00',
212 `CT_NighttimeTo` time(0) NOT NULL DEFAULT '00:00:00',
213 `CT_FixedNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 0.00,
214 `CT_PercentageNighttimeSurcharge` decimal(20, 2) NOT NULL DEFAULT 1.00,
215 `CT_StackingSurcharge` tinyint(1) NOT NULL DEFAULT 0,
216 `CT_MinimumTime` int(11) NOT NULL DEFAULT 1,
217 `CT_TimeIncrement` int(11) NOT NULL DEFAULT 1,
218 PRIMARY KEY (`CT_ID`) USING BTREE
219) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
220+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
221| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
222+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
223| 1 | SIMPLE | reports | NULL | ALL | PRIMARY,R_From,R_To | NULL | NULL | NULL | 22249 | 29.76 | Using where; Using temporary; Using filesort |
224| 1 | SIMPLE | userreports | NULL | ALL | NULL | NULL | NULL | NULL | 21359 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
225| 1 | SIMPLE | users | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dbs671769.reports.R_UID | 1 | 100.00 | NULL |
226| 1 | SIMPLE | customers | NULL | eq_ref | PRIMARY | PRIMARY | 4 | dbs671769.reports.R_CID | 1 | 100.00 | NULL |
227| 1 | SIMPLE | customerterms | NULL | ALL | NULL | NULL | NULL | NULL | 1429 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
228| 1 | SIMPLE | integration | NULL | ALL | NULL | NULL | NULL | NULL | 1134 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
229| 1 | SIMPLE | customerreports | NULL | ALL | NULL | NULL | NULL | NULL | 9078 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
230+─────+──────────────+──────────────────+─────────────+─────────+──────────────────────+──────────+──────────+──────────────────────────+───────+───────────+─────────────────────────────────────────────────────+
231ALTER TABLE `userreports`
232 ADD INDEX `FK_UR_RID` (`UR_RID`);
233
234ALTER TABLE `customerterms`
235 ADD INDEX `FK_CT_CID` (`CT_CID`);
236
237ALTER TABLE `integration`
238 ADD INDEX `FK_I_UID` (`I_UID`);
239
240ALTER TABLE `customerreports`
241 ADD INDEX `FK_CR_RID` (`CR_RID`);
242-- this just retrieves some basic stats about size of each table used in your query
243SELECT TABLE_NAME, ENGINE, VERSION, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, INDEX_LENGTH
244FROM information_schema.TABLES
245WHERE TABLE_SCHEMA = 'dbs671769'
246AND TABLE_NAME IN('customerreports', 'customers', 'customerterms', 'integration', 'reports', 'userreports', 'users');
247QUESTION
Grabbing certain data from one object to another object
Asked 2022-Jan-08 at 16:38My goal is to grab certain values from database into curated_database, however I am basically stuck at adding multiple items into an object.
1var curated_database = {};
2
3var database = {
4 0: [{name: 'Micheal'},
5 {age: 45},
6 {education: 'BA'},
7 {income: 245000},
8 {occupation: 'director'}],
9 1: [{name: 'John'},
10 {age: 23},
11 {education: 'BA'},
12 {income: 60000},
13 {occupation: 'manager'}],
14 2: [{name: 'Judith'},
15 {age: 45},
16 {education: 'PhD'},
17 {income: 140000},
18 {occupation: 'professor'}],
19 3: [{name: 'Gill'},
20 {age: 28},
21 {education: 'MS'},
22 {income: 98000},
23 {occupation: 'scientist'}],
24 4: [{name: 'Dave'},
25 {age: 17},
26 {education: 'HS'},
27 {income: 30000},
28 {occupation: 'retail associate'}]
29};
30Goal is to grab similar certain information from the larger object
1var curated_database = {};
2
3var database = {
4 0: [{name: 'Micheal'},
5 {age: 45},
6 {education: 'BA'},
7 {income: 245000},
8 {occupation: 'director'}],
9 1: [{name: 'John'},
10 {age: 23},
11 {education: 'BA'},
12 {income: 60000},
13 {occupation: 'manager'}],
14 2: [{name: 'Judith'},
15 {age: 45},
16 {education: 'PhD'},
17 {income: 140000},
18 {occupation: 'professor'}],
19 3: [{name: 'Gill'},
20 {age: 28},
21 {education: 'MS'},
22 {income: 98000},
23 {occupation: 'scientist'}],
24 4: [{name: 'Dave'},
25 {age: 17},
26 {education: 'HS'},
27 {income: 30000},
28 {occupation: 'retail associate'}]
29};
30
31curated_database = {
320 : ['Micheal',245000,'director'],
331: ['John',245000,'manager'],
342: ['Judith',140000,'professor'],
353: ['Gill',98000,'scientist'],
364: ['Dave',30000,'retail associate']
37};
38My attempt
1var curated_database = {};
2
3var database = {
4 0: [{name: 'Micheal'},
5 {age: 45},
6 {education: 'BA'},
7 {income: 245000},
8 {occupation: 'director'}],
9 1: [{name: 'John'},
10 {age: 23},
11 {education: 'BA'},
12 {income: 60000},
13 {occupation: 'manager'}],
14 2: [{name: 'Judith'},
15 {age: 45},
16 {education: 'PhD'},
17 {income: 140000},
18 {occupation: 'professor'}],
19 3: [{name: 'Gill'},
20 {age: 28},
21 {education: 'MS'},
22 {income: 98000},
23 {occupation: 'scientist'}],
24 4: [{name: 'Dave'},
25 {age: 17},
26 {education: 'HS'},
27 {income: 30000},
28 {occupation: 'retail associate'}]
29};
30
31curated_database = {
320 : ['Micheal',245000,'director'],
331: ['John',245000,'manager'],
342: ['Judith',140000,'professor'],
353: ['Gill',98000,'scientist'],
364: ['Dave',30000,'retail associate']
37};
38for(data in database){
39 desired_contents = [0,3,4]
40 for(contents in desired_contents){
41 console.log(database[data][desired_contents[contents]]);
42 }
43 var k = database[data][0];
44 if (!currated_database[k.key]) {
45 currated_database[k.key] = [];
46 }
47 currated_database[k.key].push(k.val);
48}
49ANSWER
Answered 2022-Jan-08 at 16:35you can achieve it this way:
1var curated_database = {};
2
3var database = {
4 0: [{name: 'Micheal'},
5 {age: 45},
6 {education: 'BA'},
7 {income: 245000},
8 {occupation: 'director'}],
9 1: [{name: 'John'},
10 {age: 23},
11 {education: 'BA'},
12 {income: 60000},
13 {occupation: 'manager'}],
14 2: [{name: 'Judith'},
15 {age: 45},
16 {education: 'PhD'},
17 {income: 140000},
18 {occupation: 'professor'}],
19 3: [{name: 'Gill'},
20 {age: 28},
21 {education: 'MS'},
22 {income: 98000},
23 {occupation: 'scientist'}],
24 4: [{name: 'Dave'},
25 {age: 17},
26 {education: 'HS'},
27 {income: 30000},
28 {occupation: 'retail associate'}]
29};
30
31curated_database = {
320 : ['Micheal',245000,'director'],
331: ['John',245000,'manager'],
342: ['Judith',140000,'professor'],
353: ['Gill',98000,'scientist'],
364: ['Dave',30000,'retail associate']
37};
38for(data in database){
39 desired_contents = [0,3,4]
40 for(contents in desired_contents){
41 console.log(database[data][desired_contents[contents]]);
42 }
43 var k = database[data][0];
44 if (!currated_database[k.key]) {
45 currated_database[k.key] = [];
46 }
47 currated_database[k.key].push(k.val);
48}
49var curated_database = {};
50
51var database = {
52 0: [{name: 'Micheal'},
53 {age: 45},
54 {education: 'BA'},
55 {income: 245000},
56 {occupation: 'director'}],
57 1: [{name: 'John'},
58 {age: 23},
59 {education: 'BA'},
60 {income: 60000},
61 {occupation: 'manager'}],
62 2: [{name: 'Judith'},
63 {age: 45},
64 {education: 'PhD'},
65 {income: 140000},
66 {occupation: 'professor'}],
67 3: [{name: 'Gill'},
68 {age: 28},
69 {education: 'MS'},
70 {income: 98000},
71 {occupation: 'scientist'}],
72 4: [{name: 'Dave'},
73 {age: 17},
74 {education: 'HS'},
75 {income: 30000},
76 {occupation: 'retail associate'}]
77};
78
79
80
81var indexToExtract = new Set([0,3,4]);
82
83 var wantedLst = Object.values(database)
84 .map(lst => lst.filter((ob, idx) => indexToExtract.has(idx)))
85 .map((lst, idx) => curated_database[idx] = lst.map(ob => Object.values(ob)[0]))
86
87 console.log(curated_database);QUESTION
Manually construct factor from integer vector
Asked 2021-Dec-25 at 18:11I'm trying to understand how various objects in R are composed of atomic and generic vectors.
One can construct a data.frame out of a list by manually setting the attributes names, row.names, and class, see here.
I wonder how this might work for factors, which are internally represented as integer vectors. The solution I came up with is the following:
1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6But for some reason this still looks different than a properly constructed factor:
1> f <- 1:3
2> class(f) <- "factor"
3> levels(f) <- c("low", "medium", "high")
4Warning message:
5In str.default(val) : 'object' does not have valid levels()
6> str(unclass(f))
7 int [1:3] 1 2 3
8 - attr(*, "levels")= chr [1:3] "low" "medium" "high"
9> str(unclass(factor(c("low", "medium", "high"))))