Výpočet počtu dnů zápůjček (SQL dotaz) rubrika: Databáze: SQL

8 rmaslo
položil/-a 23.2.2019
 
upravil/-a 23.2.2019

Princip: Máme záznamy o zapůjčování a vracení položek na zakázce, takže se nám na zakázce mění počet. Chceme spočítat kolik položek bylo na zakázce půjčeno minulý měsíc. A to po obdobích určených jednotlivými doklady.

Konkrétně mějme tabulku s hlavičkami vydávaných zápůjček či vratek:

CREATE TABLE `m_pujcovani` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`zakazka_id` int(11) NOT NULL,
`smer` int(11) NOT NULL,
PRIMARY KEY (`id`), KEY `zakazka_id` (`zakazka_id`), KEY `smer` (`smer`)
)

Kde směr (-1, 1) určuje zda se jedná o zápůjčku nebo vratku.

K hlavičce je tabulka rozpisu zapůjčovaných či vracených položek:

CREATE TABLE `m_pujcovani_rozpis` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pujcovani_id` int(11) NOT NULL,
`sklad_id` int(11) NOT NULL,
`ks` int(11) NOT NULL,
`datum` date NOT NULL,
PRIMARY KEY (`id`), KEY `sklad_id` (`sklad_id`), KEY `pujcovani_id` (`pujcovani_id`), KEY `datum` (`datum`)
)

Kde, pujcovani_id je odkaz na hlavičku, sklad_id je odkaz do katalogu zboží a datum je datum zapůjčení.

Nahrajme testovací data:

INSERT INTO m_pujcovani (id, zakazka_id, smer) VALUES
(916, 124, -1),
(917, 124, 1),
(918, 124, 1),
(919, 124, -1),
(920, 124, 1)
 
INSERT INTO m_pujcovani_rozpis (id, pujcovani_id, sklad_id, ks, datum) VALUES
(14657,  916,  20,  15,  '2018-12-10'),
(14658,  916,  30,  15,  '2018-12-10'),
(14659,  917,  30,  5,   '2018-12-28'),
(14660,  918,  20,  3,   '2019-01-10'),
(14661,  918,  30,  2,   '2019-01-10'),
(14662,  919,  20,  3,   '2019-01-15'),
(14663,  920,  20,  10,  '2019-01-30')

Cíl: Teď v únoru, bychom chtěli určit jaké počty kusů byly na této testovací zakázce vypůjčeny v lednu a to po jednotlivých půjčovaných obdobích (daných doklady). Pro kontrolu chci i vidět z kterých dokladů ty jednotlivé počty vychází. Samozřejmě do počtů chci zahrnout i ty věci co zůstaly zapůjčené z prosince. Výsledkem by tedy mělo být něco jako:

sklad_id  datum_od    datum_do    pocet_dnu  den_ks  obd_ks  doklady
20        2019-01-01  2019-01-09     9          15    135    916
20        2019-01-10  2019-01-14     5          12     60    916,918
20        2019-01-15  2019-01-29    15          15    225    916,918,919
20        2019-01-30  2019-01-31     2           5     10    916,918,919,920
 
30        2019-01-01  2019-01-09     9          10     90    916,917
30        2019-01-10  2019-01-31    22           8    176    916,917,918

První řádek tedy říká, že pro zboží s id 20 mám prvních 9 dnů půjčeno 15ks a je to dáno dokladem (hlavička id) 916, pak přišel doklad 918 a mám dalších 5 dnu po 12ti kusech, atd...
Zboží s id 30 začíná 10ti kusy protože na 916 bylo půjčeno 15 a na 917 vráceno 5. ...

odkaz
4 plelovsky
odpověděl/-a 26.2.2019

S MySql nedělám, tak řešení pro MSSQL, s využitím funkcí LAG() A SUM() OVER ():

DECLARE @datum_od DATE = '20190101';
DECLARE @datum_do DATE = '20190131';
 
SELECT
      *
    , DATEDIFF(day, datum_od, datum_do) + 1 AS pocet_dnu
    , den_ks * (DATEDIFF(day, datum_od, datum_do) + 1) AS obd_ks
FROM (
    SELECT
         zakazka_id
       , sklad_id
       , CASE WHEN datum_od < @datum_od THEN @datum_od ELSE datum_od END AS datum_od
       , CASE WHEN datum_do > @datum_do THEN @datum_do ELSE datum_do END AS datum_do
       , den_ks
    FROM (
       SELECT
            p.zakazka_id
          , pr.sklad_id
          , pr.datum AS datum_od
          , ISNULL(DATEADD(day, -1, LAG(pr.datum) OVER (PARTITION BY p.zakazka_id, pr.sklad_id ORDER BY pr.datum DESC)), @datum_do) AS datum_do
          , -SUM(pr.ks * p.smer) OVER (PARTITION BY p.zakazka_id, pr.sklad_id ORDER BY pr.datum) AS den_ks
       FROM
          m_pujcovani_rozpis AS pr
          INNER JOIN m_pujcovani AS p ON p.id = pr.pujcovani_id
    ) AS q2
    WHERE
       NOT (datum_od > @datum_do OR datum_do < @datum_od)
) AS q1
ORDER BY
    zakazka_id, sklad_id, datum_od
;

Adekvátní funkce by měly být i v MySql.
Není zde spojování čísel dokladů - MSSQL má funkci STRING_AGG až od verze 2017.

Komentáře

  • rmaslo : Dík to vypadá jako nakopnutí správným směrem. Klíč je ta LAG funkce. Mám tu 10.1.37-MariaDB zkusím ať mi to admin zvedne, protože MariaDB jí má až od 10.2.2. Pokud by to nešlo tak na stackoverflow vidím nějaký příklad jak jí nahradit přes proměnné, to už bych snad měl dát dohromady :-) . — 26.2.2019

Pro zobrazení všech 4 odpovědí se prosím přihlaste:

Rychlé přihlášení přes sociální sítě:

Nebo se přihlaste jménem a heslem:

Zadejte prosím svou e-mailovou adresu.
Zadejte své heslo.

Aktuální otázky v rubrice Databáze: SQL

Otázky uživatele rmaslo