Sql количество рабочих дней.
Праздники не учитываем, считаем, что в неделе 5 рабочих дней.
Ну, узнать количество рабочих дней между двумя данными днями недели легко, потом добавляешь к этому 5*количество полных недель.
Ну, узнать количество рабочих дней между двумя данными днями недели легкоЧто-то непонятно, как это сделать, при этом сделать нужно в общем случае, т.е 1 колонка, например 1980-xx-xx и т.д а 2-я 1990-xx-xx
например 1980-xx-xx и т.д а 2-я 1990-xx-xxПро високосные года не забудь.
Про високосные года не забудь.С этим то как раз проблем нет, функция DATEDIFF(type,date1,date2) вычисляет разницу между 2 датами в любых единицах(годах, месяцах, днях, часах и т.д)
Recipe 8.3. Determining the Number of Business Days Between Two Dates
Problem
Given two dates, you want to find how many "working" days are between them, including the two dates themselves. For example, if January 10th is a Tuesday and January 11th is a Monday, then the number of working days between these two dates is two, as both days are typical work days. For this recipe, "business days" is defined as any day that is not Saturday or Sunday.
Solution
The solution examples find the number of business days between the HIREDATEs of BLAKE and JONES. To determine the number of business days between two dates, you can use a pivot table to return a row for each day between the two dates (including the start and end dates). Having done that, finding the number of business days is simply counting the dates returned that are not Saturday or Sunday.
If you want to exclude holidays as well, you can create a HOLIDAYS table. Then include a simple NOT IN predicate to exclude days listed in HOLIDAYS from the solution.
DB2
Use the pivot table T500 to generate the required number of rows (representing days) between the two dates. Then count each day that is not a weekend. Use the DAYNAME function to return the weekday name of each date. For example:
1 select sum(case when dayname(jones_hd+t500.id day -1 day)
2 in ( 'Saturday','Sunday' )
3 then 0 else 1
4 end) as days
5 from (
6 select max(case when ename = 'BLAKE'
7 then hiredate
8 end) as blake_hd,
9 max(case when ename = 'JONES'
10 then hiredate
11 end) as jones_hd
12 from emp
13 where ename in ( 'BLAKE','JONES' )
14 ) x,
15 t500
16 where t500.id <= blake_hd-jones_hd+1
MySQL
Use the pivot table T500 to generate the required number of rows (days) between the two dates. Then count each day that is not a weekend. Use the DATE_ADD function to add days to each date. Use the DATE_FORMAT function to obtain the weekday name of each date:
1 select sum(case when date_format(
2 date_add(jones_hd,
3 interval t500.id-1 DAY'%a')
4 in ( 'Sat','Sun' )
5 then 0 else 1
6 end) as days
7 from (
8 select max(case when ename = 'BLAKE'
9 then hiredate
10 end) as blake_hd,
11 max(case when ename = 'JONES'
12 then hiredate
13 end) as jones_hd
14 from emp
15 where ename in ( 'BLAKE','JONES' )
16 ) x,
17 t500
18 where t500.id <= datediff(blake_hd,jones_hd)+1
Oracle
Use the pivot table T500 to generate the required number of rows (days) between the two dates, and then count each day that is not a weekend. Use the TO_CHAR function to obtain the weekday name of each date:
1 select sum(case when to_char(jones_hd+t500.id-1,'DY')
2 in ( 'SAT','SUN' )
3 then 0 else 1
4 end) as days
5 from (
6 select max(case when ename = 'BLAKE'
7 then hiredate
8 end) as blake_hd,
9 max(case when ename = 'JONES'
10 then hiredate
11 end) as jones_hd
12 from emp
13 where ename in ( 'BLAKE','JONES' )
14 ) x,
15 t500
16 where t500.id <= blake_hd-jones_hd+1
PostgreSQL
Use the pivot table T500 to generate the required number of rows (days) between the two dates. Then count each day that is not a weekend. Use the TO_CHAR function to obtain the weekday name of each date:
1 select sum(case when trim(to_char(jones_hd+t500.id-1,'DAY'
2 in ( 'SATURDAY','SUNDAY' )
3 then 0 else 1
4 end) as days
5 from (
6 select max(case when ename = 'BLAKE'
7 then hiredate
8 end) as blake_hd,
9 max(case when ename = 'JONES'
10 then hiredate
11 end) as jones_hd
12 from emp
13 where ename in ( 'BLAKE','JONES' )
14 ) x,
15 t500
16 where t500.id <= blake_hd-jones_hd+1
SQL Server
Use the pivot table T500 to generate the required number of rows (days) between the two dates, and then count each day that is not a weekend. Use the DATENAME function to obtain the weekday name of each date:
1 select sum(case when datename(dw,jones_hd+t500.id-1)
2 in ( 'SATURDAY','SUNDAY' )
3 then 0 else 1
4 end) as days
5 from (
6 select max(case when ename = 'BLAKE'
7 then hiredate
8 end) as blake_hd,
9 max(case when ename = 'JONES'
10 then hiredate
11 end) as jones_hd
12 from emp
13 where ename in ( 'BLAKE','JONES' )
14 ) x,
15 t500
16 where t500.id <= datediff(day,jones_hd-blake_hd)+1
Discussion
While each RDBMS requires the use of different built-in functions to determine the name of a day, the overall solution approach is the same for each. The solution can be broken into two steps:
1. Return the days between the start date and end date (inclusive).
2. Count how many days (i.e., rows) there are, excluding weekends.
Inline view X performs step 1. If you examine inline view X, you'll notice the use of the aggregate function MAX, which the recipe uses to remove NULLs. If the use of MAX is unclear, the following output might help you understand. The output shows the results from inline view X without MAX:
select case when ename = 'BLAKE'
then hiredate
end as blake_hd,
case when ename = 'JONES'
then hiredate
end as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
BLAKE_HD JONES_HD
----------- -----------
02-APR-1981
01-MAY-1981
Without MAX, two rows are returned. By using MAX you return only one row instead of two, and the NULLs are eliminated:
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
BLAKE_HD JONES_HD
----------- -----------
01-MAY-1981 02-APR-1981
The number of days (inclusive) between the two dates here is 30. Now that the two dates are in one row, the next step is to generate one row for each of those 30 days. To return the 30 days (rows use table T500. Since each value for ID in table T500 is simply 1 greater than the one before it, add each row returned by T500 to the earlier of the two dates (JONES_HD) to generate consecutive days starting from JONES_HD up to and including BLAKE_HD. The result of this addition is shown below (using Oracle syntax):
select x.*, t500.*, jone
В общем, качайте SQL Cookbook по ссылке
Thanks, хорошая, наверное, книжка.
Ну так вот - берёшь день недели для первой даты, день недели для второй даты (количество рабочих дней берёшь по модулю 5 - то есть, прибавляешь 5, если отрицательно смотришь на разницу между датами в неделях и множишь на 5.
Оставить комментарий
amani
Как посчитать количество рабочих дней между 2 датами? Никто не знает?