Shulou(Shulou.com)05/31 Report--

The main content of this article is to explain the methods and steps of using SQL to count only working day data. Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "the method and steps of using SQL to count only working day data".

Title

How many day shifts did the employee "Zhang San" work on weekdays?

Test data CREATE TABLE Tmp0317 (name VARCHAR (20), working hours DATETIME, closing hours DATETIME) INSERT INTO Tmp0317 VALUE ('Zhang San', '2021-03-01 08 DATETIME 05V 03V') INSERT INTO Tmp0317 VALUE ('Zhang San', '2021-03-03-03 8 V 12') INSERT INTO Tmp0317 VALUE ('Zhang San', '2021-03-04 08 DATETIME 11') INSERT INTO Tmp0317 VALUE ('Zhang San', '2021-03-05 08V') INSERT INTO Tmp0317 VALUE ('Zhang San', '2021-03-05 08V' 1508V') INSERT INTO Tmp0317 VALUE ('Zhang San', '2021-03-09 08INSERT INTO Tmp0317 VALUE 26'') INSERT INTO Tmp0317 VALUE ('Zhang San', '2021-03-01 1823Ranger 48') INSERT INTO Tmp0317 VALUE ('Zhang San', '2021-03-100 08INSERT INTO Tmp0317 VALUE') '2021-03-11 08 INSERT INTO Tmp0317 VALUE 13') INSERT INTO Tmp0317 VALUE ('Zhang San', '2021-03-12 08 V' 1742') INSERT INTO Tmp0317 VALUE ('Zhang San', '2021-03-13 08 15 charge 37')

Analysis.

If you are required to know the number of working days, you only need to rule out weekends. Here, we will not consider the situation of forgetting to sign in. We can use the system table spt_values in SQL Server to solve the problem.

Specific solution: SELECT SUM (CASE WHEN DATEPART (WEEKDAY,DATEADD (DD,NUMBER,'2021-03-01')) IN (1Magne7) THEN 0 ELSE 1 END) AS WORKDAY FROM MASTER..SPT_VALUES JOIN Tmp0317 ON DATEADD (DAY, NUMBER, CONVERT (DATE, '2021-03-01')) = CONVERT (DATE, working hours) WHERE TYPE='P' AND NUMBER BETWEEN 0 AND DATEDIFF (DAY, '2021-03-01), DATEADD (MONTH, 1,' 2021-03-01))-1

(hint: you can swipe the code left and right)

The result is:

We can compare it with the calendar. "Zhang San" opens the record on the working day of these days:

The red box indicates the working day and the green box indicates the weekend. Zhang San has a total of 9 records. Saturday, the 13th, should have returned to the company for overtime, but we do not count as the attendance record of a normal working day, so the result is 8. 5%.

Code parsing

The above code is estimated to be a little confusing for many readers. Here we will disassemble the code first and take a look at the result of each function.

The first is the spt_values system table, which we mentioned in the previous article.

Secondly, let's take a look at the correlation conditions:

JOIN Tmp0317 ON DATEADD (DAY, NUMBER, CONVERT (DATE, '2021-03-01')) = CONVERT (DATE, working hours)

Here we separately look at the results of the two equal signs:

SELECT DATEADD (DAY, NUMBER, CONVERT (DATE, '2021-03-01') FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 0 AND DATEDIFF (DAY, '2021-03-01), DATEADD (MONTH, 1,' 2021-03-01')-1

Note: the following WHERE condition section must be added here, of which the later

DATEDIFF (DAY, '2021-03-01), DATEADD (MONTH, 1,' 2021-03-01')-1

The result is 30, and here because our NUMBER starts at 0, we subtract 1, that is, from 0 to 30, which means 31 days in March.

The query results are as follows:

There are still 16-31 records that have not been intercepted. You can try it on your own computer.

Then look at the result to the right of the equal sign:

SELECT CONVERT (DATE, working hours) FROM Tmp0317

The results are as follows:

In this way, we can get the work date we need by associating the conditions, but this is not the work date of the working day, and we finally have to make a judgment, that is, the CASE WHEN condition after SELECT.

Let's execute the code in CASE WHEN separately:

SELECT DATEADD (DD,NUMBER,'2021-03-01'), DATEPART (WEEKDAY,DATEADD (DD,NUMBER,'2021-03-01') AS WORKDAY FROM MASTER..SPT_VALUES JOIN Tmp0317 ON DATEADD (DAY, NUMBER, CONVERT (DATETIME, '2021-03-01')) = CONVERT (DATE, working hours) WHERE TYPE='P' AND NUMBER BETWEEN 0 AND DATEDIFF (DAY,' 2021-03-01, DATEADD (MONTH, 1, '2021-03-01))-1

Let's separate out the nested function DATEADD in DATEPART and take a look at the execution result:

Picture

The function of DATEPART here is mainly to return the day that is the day of the week, our system calendar is according to the American calendar, the first day of each week is Sunday, so Monday of 2021-03-01 is the second day of the week, and so on, we get each day corresponding to the day of the week.

After knowing this result, we can know that the 2nd-6th day of each week corresponds to our working day, so we can take this result IN (2meme 3rec 4je 5je 6) or NOT IN (1m 7).

Here we use the antonym taken by CASE WHEN. When it IN (1), we return 0, which means no statistics, and other results return 1, which means statistics.

That is:

SELECT DATEADD (DD,NUMBER,'2021-03-01'), DATEPART (WEEKDAY,DATEADD (DD,NUMBER,'2021-03-01'), CASE WHEN DATEPART (WEEKDAY,DATEADD (DD,NUMBER,'2021-03-01')) IN (1 ELSE 7) THEN 0 ELSE 1 END AS WORKDAY FROM MASTER..SPT_VALUES JOIN Tmp0317 ON DATEADD (DAY, NUMBER, CONVERT (DATETIME, '2021-03-01')) = CONVERT (DATE, working hours) WHERE TYPE='P' AND NUMBER BETWEEN 0 AND DATEDIFF (DAY) '2021-03-01), DATEADD (MONTH, 1,' 2021-03-01')-1

The result is:

We get our result 8 by summing the WORKDAY column with SUM.

Q: can I not use spt_values here?

A: yes, you only need to build a temporary table, and the table structure only needs one column, that is, a column of self-growing continuous integers.

At this point, I believe you have a deeper understanding of the "methods and steps of using SQL to only count working day data". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.