Liczba dni (kolejnych / roboczych) pomiędzy dwiema datami

Kolejna notka z dziedziny baz danych.

Dzisiaj chciałbym pokazać w jaki sposób wyliczyć ilość dni (oraz ilość dni roboczych w drugim przykładzie) pomiędzy dwoma datami. Do tego celu posłużymy się naszą tabelką prac.

Struktura DB wraz z przykładowymi danymi dla omawianych zagadnień znajduje się tutaj.

Dla przypomnienia, tak wygląda tabelka, na której przeprowadzimy nasze ćwiczenia:

mysql> select * from prac;
+----+------------+------------+------------+---------+--------+-----------+
| id | nazwisko   | stanowisko | data_zatr  | wyplata | premia | id_dzialu |
+----+------------+------------+------------+---------+--------+-----------+
|  1 | Kowalski   | sprzedawca | 2005-07-13 |    1600 |    300 |        30 |
|  2 | Malinowski | menadżer   | 2005-09-13 |    2489 |      0 |        30 |
|  3 | Grabski    | analityk   | 2006-07-15 |    1458 |      0 |        20 |
|  4 | Bielecki   | księgowy   | 2006-03-12 |    1236 |      0 |        10 |
|  5 | Kurtyka    | dyrektor   | 2006-04-17 |   11369 |      0 |        10 |
|  6 | Dąbkowski  | księgowy   | 2002-04-13 |    1236 |      0 |        10 |
|  7 | Kononowicz | analityk   | 2006-04-11 |    3548 |      0 |        20 |
|  8 | Janczak    | księgowy   | 2002-01-10 |    3699 |      0 |        10 |
|  9 | Kamiński   | sprzedawca | 2007-09-07 |    1365 |    658 |        30 |
| 10 | Czechowski | analityk   | 2009-01-03 |    3547 |      0 |        20 |
| 11 | Duchnowski | sprzedawca | 2007-09-03 |    1687 |    356 |        30 |
| 12 | Różański   | sprzedawca | 2007-09-07 |    1999 |    200 |        30 |
+----+------------+------------+------------+---------+--------+-----------+
12 rows in set (0.00 sec)

1. Jak policzyć ilość kolejnych dni pomiędzy dwoma datami?

Do rozwiązania postawionego problemu przyda nam się wbudowana funkcja datediff(). W jednym zapytaniu pobieramy obie z interesujących nas dat (w tym przypadku daty zatrudnienia Kowalskiego i Malinowskiego) i podstawiamy je do wspomnianej funkcji:

mysql > SELECT ABS(DATEDIFF(kowalski_data,malinowski_data)) AS roznica FROM
(SELECT data_zatr AS kowalski_data FROM prac WHERE nazwisko = 'Kowalski') X,
(SELECT data_zatr AS malinowski_data FROM prac WHERE nazwisko = 'Malinowski') Y;

Wynik działania naszego zapytania:

+---------+
| roznica |
+---------+
|      62 |
+---------+
1 row in set (0.00 sec)

Oczywiście z funkcji ABS() korzystamy po to, aby wynik naszych obliczeń był zawsze dodatni.

2. Jak policzyć ilość roboczych dni pomiędzy dwoma datami?

Drugi przykład jest nieco trudniejszy. Aby odpowiedzieć na postawione pytanie będziemy musieli skorzystać dodatkowo z przestawnej tabeli p500. Jest to tabela o kolejnych indeksach od 1 do 500, o takiej strukturze:

mysql> select * from p500;
+-----+
|  id |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
| ... |
| 500 |
+-----+
500 rows in set (0.00 sec)

Samo zapytanie wygląda natomiast tak:

SELECT SUM(
   CASE WHEN DATE_FORMAT(
      DATE_ADD(
         kowalski_data,INTERVAL p500.id-1 DAY
      ), '%a'
   ) IN ('Sat','Sun') THEN 0 ELSE 1
   END
) AS dni

FROM
   (
      SELECT MAX(CASE WHEN nazwisko = 'Kowalski' THEN data_zatr END) AS kowalski_data,
      MAX(CASE WHEN nazwisko = 'Malinowski' THEN data_zatr END) AS malinowski_data FROM prac
   ) X,
   p500

WHERE p500.id <= DATEDIFF(malinowski_data,kowalski_data)+1

Natomiast wynik jego działania:

+------+
|  dni |
+------+
|   45 |
+------+
1 row in set (0.00 sec)

Kilka słów wyjaśnienia:
Całe postępowanie można podzielić na dwa kroki:
1) w pierwszym zwracamy listę dni dzielących obie daty – uwzględniając skrajne dni

SELECT *

FROM
   (
      SELECT MAX(CASE WHEN nazwisko = 'Kowalski' THEN data_zatr END) AS kowalski_data,
      MAX(CASE WHEN nazwisko = 'Malinowski' THEN data_zatr END) AS malinowski_data FROM prac
   ) X,
   p500

WHERE p500.id <= DATEDIFF(malinowski_data,kowalski_data)

+---------------+-----------------+----+
| kowalski_data | malinowski_data | id |
+---------------+-----------------+----+
| 2005-07-13    | 2005-09-13      |  1 |
| 2005-07-13    | 2005-09-13      |  2 |
| 2005-07-13    | 2005-09-13      |  3 |
| 2005-07-13    | 2005-09-13      |  4 |
| 2005-07-13    | 2005-09-13      |  5 |
| 2005-07-13    | 2005-09-13      |  6 |
| 2005-07-13    | 2005-09-13      |  7 |
| 2005-07-13    | 2005-09-13      |  8 |
| 2005-07-13    | 2005-09-13      |  9 |
| 2005-07-13    | 2005-09-13      | 10 |
| 2005-07-13    | 2005-09-13      | 11 |
| 2005-07-13    | 2005-09-13      | 12 |
| 2005-07-13    | 2005-09-13      | 13 |
| 2005-07-13    | 2005-09-13      | 14 |
| 2005-07-13    | 2005-09-13      | 15 |
| 2005-07-13    | 2005-09-13      | 16 |
| 2005-07-13    | 2005-09-13      | 17 |
| 2005-07-13    | 2005-09-13      | 18 |
| 2005-07-13    | 2005-09-13      | 19 |
| 2005-07-13    | 2005-09-13      | 20 |
| 2005-07-13    | 2005-09-13      | 21 |
| 2005-07-13    | 2005-09-13      | 22 |
| 2005-07-13    | 2005-09-13      | 23 |
| 2005-07-13    | 2005-09-13      | 24 |
| 2005-07-13    | 2005-09-13      | 25 |
| 2005-07-13    | 2005-09-13      | 26 |
| 2005-07-13    | 2005-09-13      | 27 |
| 2005-07-13    | 2005-09-13      | 28 |
| 2005-07-13    | 2005-09-13      | 29 |
| 2005-07-13    | 2005-09-13      | 30 |
| 2005-07-13    | 2005-09-13      | 31 |
| 2005-07-13    | 2005-09-13      | 32 |
| 2005-07-13    | 2005-09-13      | 33 |
| 2005-07-13    | 2005-09-13      | 34 |
| 2005-07-13    | 2005-09-13      | 35 |
| 2005-07-13    | 2005-09-13      | 36 |
| 2005-07-13    | 2005-09-13      | 37 |
| 2005-07-13    | 2005-09-13      | 38 |
| 2005-07-13    | 2005-09-13      | 39 |
| 2005-07-13    | 2005-09-13      | 40 |
| 2005-07-13    | 2005-09-13      | 41 |
| 2005-07-13    | 2005-09-13      | 42 |
| 2005-07-13    | 2005-09-13      | 43 |
| 2005-07-13    | 2005-09-13      | 44 |
| 2005-07-13    | 2005-09-13      | 45 |
| 2005-07-13    | 2005-09-13      | 46 |
| 2005-07-13    | 2005-09-13      | 47 |
| 2005-07-13    | 2005-09-13      | 48 |
| 2005-07-13    | 2005-09-13      | 49 |
| 2005-07-13    | 2005-09-13      | 50 |
| 2005-07-13    | 2005-09-13      | 51 |
| 2005-07-13    | 2005-09-13      | 52 |
| 2005-07-13    | 2005-09-13      | 53 |
| 2005-07-13    | 2005-09-13      | 54 |
| 2005-07-13    | 2005-09-13      | 55 |
| 2005-07-13    | 2005-09-13      | 56 |
| 2005-07-13    | 2005-09-13      | 57 |
| 2005-07-13    | 2005-09-13      | 58 |
| 2005-07-13    | 2005-09-13      | 59 |
| 2005-07-13    | 2005-09-13      | 60 |
| 2005-07-13    | 2005-09-13      | 61 |
| 2005-07-13    | 2005-09-13      | 62 |
+---------------+-----------------+----+
62 rows in set (0.00 sec)

2) w drugim dokonujemy sumowania:

SUM(
   CASE WHEN DATE_FORMAT(
      DATE_ADD(
         kowalski_data,INTERVAL p500.id-1 DAY
      ), '%a'
   ) IN ('Sat','Sun') THEN 0 ELSE 1
   END
) AS dni

które polega na tym, iż „przechodzimy” po poprzedniej liście i za każdym razem dodajemy do daty (kowalski_data) ilość dni określoną przez id-1 (dlatego -1, aby uwzględnić w naszym sumowaniu pierwszy dzień z listy), po czym sprawdzamy czy ten dzień nie wypada czasem w weekend (Sat, Sun) – jeśli tak, to nie uwzględniamy go w naszej sumie (wyrażenie CASE).

I to tyle na dzisiaj, zapraszam ponownie w bliżej nieokreślonej przyszłości.

Przykłady zaczerpnięte z książki „SQL Receptury” Athony’ego Molinaro.

dev.baczyk.net

Napisz komentarz

« Strona główna

Strony

Kategorie

Archiwum

Kanał RSS

Szukaj

dev.baczyk.net