Diskuze: Složitejší SQL select

tomas

Člen

tomas:20.7.2017 13:47

Ahoj všem, mám následující tabulku:

| ID    |devID  |empID  |protocol       |transfer       |date           |
| 1     |53     |118    |133            |1              |2015-08-04     |
| 2     |96     |118    |133            |1              |2015-08-04     |
| 3     |132    |118    |133            |1              |2015-08-04     |
| 4     |368    |118    |134            |1              |2015-08-05     |
| 5     |368    |118    |216            |0              |2015-12-18     |
| 6     |53     |118    |216            |0              |2015-12-18     |
| 7     |96     |118    |216            |0              |2015-12-18     |
| 9     |132    |118    |216            |0              |2015-12-18     |
|10     |138    |118    |295            |1              |2016-09-13     |

Popis:
devID = ID zařízení
empID = id zamestnance
protocol = je číslo protokolu
trasnfer = 1 je předání zamestnanci, 0= vracení
datum = je jasný

Jedná se o tabulku evidence zařízení a toho kdo má dané zařízení přidělený. Z tabulky je možné vyčíst, že protokol číslo 133 je protokol o předání zařízení ID 53,96 a132 zaměstnanci číslo 118 dne 4.8.2015. Protokol číslo 134 pak uvádí, že stejný zaměstnanec dostal zařízení číslo 368 dne 8.5.2015. Protokol číslo 216 je protokol o vrácení zařízení(transfér = 0) kde zamestnanec vrací zařízení číslo 368,53,96 a 132. Takže aktuálně má zamestnanec v držení pouze zařízení číslo 138 dle protokolu číslo 295 ze dne 13.9.2016.

požadavek:
Já se snažím napsat dotaz, pomocí kterého mě vrátí seznam pouze těch zařízení, které má aktuálně půjčený. Je potřeba ale brát v úvahu, že jedno stejné zařízení může mět stejný zamestnanec předané vícekrát a vícekrát ho bude mět vrácený. Napadá někoho jak tohle řešit? Nevím jak mám při selectu ošetřit, aby vyloučil to zařízení, které je již vrácené :-(

Odpovědět

20.7.2017 13:47

dez1nd

Člen

dez1nd:20.7.2017 13:59

Napadá mě experimentovat s SELECT DISTINCT 'devID' WHERE 'transfer' = 1 ORDER BY 'date'

Editováno 20.7.2017 14:00

Nahoru Odpovědět

20.7.2017 13:59

Oliver Tušla

Člen

Oliver Tušla:20.7.2017 16:20

Ahoj, neumím SQL pořádně, takže je ten dotaz asi blbě, ale snad z toho pochopíš princip

SELECT DISTINCT devID FROM (

        SELECT DISTINCT devID FROM itn one WHERE transfer = 1 ORDER BY date DESC

        UNION

        SELECT DISTINCT devID FROM itn zero WHERE transfer = 0 ORDER BY date DESC
) dohromady WHERE transfer = 1 ORDER BY date DESC

Kdybys to z toho nepochopil, tak rozepíšu postup.

Pak sem prosím napiš správný dotaz.

Editováno 20.7.2017 16:22

Nahoru Odpovědět

20.7.2017 16:20

Oliver Tušla

Člen

Oliver Tušla:20.7.2017 17:02

V kroku 2 dostaneš tabulku, ve které jsou jen nejaktuálnější záznamy o předání zaměstnanci.
V kroku 3 dostaneš tabulku, ve které jsou jen nejaktuálnější záznamy o vrácení zařízení.

V kroku 4 je spojíš a dostaneš nejaktuálnější záznam o zařízení. Z toho vybereš jen ty, které má zaměstnanec u sebe.

Nahoru Odpovědět

20.7.2017 17:02

tomas

Člen

tomas:21.7.2017 11:04

Ahoj, zkoušel jsem, ale nepodařilo se mě to sestavit. Ten dotaz v závorce mě samotný fungoval, ale když jsem to dal do toho kompletního dotazu, tak to hazelo chybu. No ale hledal jsem hledal a našel jsem jiný způsob. Mrkněte na níže uvedený dotaz, zdá se, že to funguje tak jak potřebuju, ale můžu narazit na jiné problémy.

select * from #table as list1
where not exists (select id from #table as list2 where list2.devID = list1.devID AND list2.date > list1.date) AND transfer = 1

Nahoru Odpovědět

21.7.2017 11:04

plelovsky

Člen

plelovsky:21.7.2017 11:35

Potřebuješ získat kombinaci <devId, empId>, kde transfer = 1 a neexistuje stejná kombinace s vyšším datem, kde transfer = 0.
Následující select řeší i to, kdyby stejné zařízení stejným zaměstnancem bylo půjčené i vrácené ve stejný den:

SELECT
        vypujcka.*
FROM
        EvidenceZarizeni AS vypujcka
        LEFT OUTER JOIN EvidenceZarizeni AS vratka ON
                vratka.transfer = 0
                AND
                vratka.devID = vypujcka.devID
                AND
                vratka.empID = vypujcka.empID
                AND
                vratka.date >= vypujcka.date
WHERE
        vypujcka.transfer = 1
        AND
        vratka.ID IS NULL
;

Pokud potřebuješ např. jen seznam aktuálně půjčených zařízení, lze select upravit (např. SELECT DISTINCT vypujcka.devID ...)

Neuvádíš jaká je to DB, tento select je pro MS SQL.

Problém by byl, kdyby zaměstnanec zařízení vrátil a pak ve stejný den znovu půjčil.
To by šlo řešit např. tím, že číslo protokolu nové výpůjčky by pravděpodobně bylo vyšší než číslo protokolu vrácení.

BTW tabulka nesplňuje 2. NF - atributy transfer, date jsou závislé na atributu protocol.

Nahoru Odpovědět

21.7.2017 11:35

Peter Trcka

Člen

Peter Trcka:25.7.2017 20:26

Ahoj

podľa mna mas tu tabulku navrhnutu zle. prve udaje empid a zamid zapisujes zbytocne lebo zamestnanec si nemoze pozicat 2x nieco co este nevratil, naviac z tohto navrhu nie je jasne co tabulka reprezentuje. DB tabulka by mala zohladnovat ucel na co ju potrebujes v tomto pripade evidencia pozicania a vratenia. stlpce
id
devid
empid

su v poriadku.

zo zadania je zrejme ze pri pozicani su rozne protokoly 133 134, ale nie je zrejme ci existuje viac protokolov vratenia ( vratene bez poskodenia, vratene poskodene, vratene po termine atd...)
budem predpokladat ze protokol ako predania tak odovzdania moze mat viac stavov.

ja by som si tabulku navrhol takto:
id
devid
empid
protocol_pozicania
date_pocicania
protocol_vratenia
date_vratenia

riadok kde nie su vyplnene udaje o vrateni(hodnoty su null) je zariadenie stale pozicane. pri vrateni len dopises datum a typ protocolu.

SELECT * FROM evidence WHERE date_vratenia IS NULL

potom jednoduchym selectom vies zistit ci zamestnanec uz dane zariadene pozicane ma, atd. atd.

neviem ci uz nie je neskoro tento moj komentar, ale myslim ze tato jednoducha zmena struktury v jednej tabulke ti dost ulahci zivot.

Nahoru Odpovědět

25.7.2017 20:26

tomas

Člen

tomas:26.7.2017 9:13

Tak jen doplním, že se jedná o půjčování HW, db je MS SQL a není možné, aby se jedno devID půjčilo dvakrát. Jinak původně jsem měl v tabulce ještě další dva sloupce s datum_vrácení a stav_vrácení. Ale na radu jednoho programátora, který mě řekl, že pokud tam mám stav(transfér) tak je jasný, jestli se jedná o datum půjčení nebo vrácení a stejně tak u toho stavu. Tak jsem ty sloubce odstranil, to jsem ale nevěděl co mě čeká :-) Když jsem je tam měl, tak jsem si to selektoval jak píšeš a fungovalo mě to spolehlivě.
K tomu číslu protokolu, když má někdo půjčený třeba notebook, monitor, telefon, batoh, tak nemusí vrátit vše současně, ale jen něco. Číslo protokolu mě značí, které zařízení je součástí vracení. například pokud bude vracet notebook a telefon, přibudou do tabulky dva řádky se stejným číslem protokolu. To co uvádíš, na protokolu 133 si zaměstnanec(118) převzal zařízení 53, 96, 132 a pak druhý den na dalším protokolu 134 si převzal zařízení číslo 368, které zase vrací na protokolu 216 plus ostatní zařízení.

Nahoru Odpovědět

26.7.2017 9:13

Michal Štěpánek

Člen

Michal Štěpánek:26.7.2017 10:24

Asi bych to řešil vytvořením více tabulek.

tabulka HW
tabulka uživatelů
vazební tabulka půjčování

ve vazební tabulce by bylo ID_HW, ID_User, Datum_půjčení, Datum_Vrácení
při půjčení vytvoříš ve vazební tabulce nový záznam, při vrácení jen záznam doplníš o datum vrácení...
Případně bych dal do tabulky HW nějaký "bool" sloupec, kde by bylo uvedeno jestli je HW k dispozici, nebo je půjčeno (true/false)

Editováno 26.7.2017 10:25

Nahoru Odpovědět

26.7.2017 10:24

Nikdy neříkej nahlas, že to nejde. Vždycky se totiž najde blbec, který to neví a udělá to...

tomas

Člen

tomas:26.7.2017 12:00

:-D , tak takle přesně jsem to měl. Že jsem ho poslouchal. v tabulce HW mám bool který mě říká jestli je HW k půjčení nebo ne.

Nahoru Odpovědět

26.7.2017 12:00

plelovsky

Člen

plelovsky:26.7.2017 12:21

Chybí tam ty protokoly. Když už by se měla měnit struktura, tak:
Místo Datum_půjčení, Datum_Vrácení bude protocolPredani, protocolVraceni (u dosud nevrácených zařízení bude NULL)
Protokol bude mít atributy empID, date, transfer (1 = předání, 0 = vrácení)
Dále tam bude seznam zařízení na protokolu s atributy protocol, devID
(snažím se zachovat použité názvy)

Nahoru Odpovědět

26.7.2017 12:21

Michal Štěpánek

Člen

Michal Štěpánek:26.7.2017 13:41

Pokud nutně nepotřebuje evidovat souhrnně půjčování a vracení, ale stačí mu evidovat výpůjčky jednotlivých HW, pak jsou ty protokoly zbytečné...

Nahoru Odpovědět

26.7.2017 13:41

Nikdy neříkej nahlas, že to nejde. Vždycky se totiž najde blbec, který to neví a udělá to...

Dušan Mačkay

Člen

Dušan Mačkay:8.9.2017 14:16

SELECT
predane.devID
,predane.empID
,predane.pocet - ISNULL(vratene.pocet, 0) stav
FROM (SELECT
devID
,empID
,COUNT() pocet
FROM [Dusik].[dbo].[tabulka1]
WHERE transfer = 1
GROUP BY devID
,empID) AS predane
LEFT JOIN (SELECT
devID
,empID
,COUNT() pocet
FROM [Dusik].[dbo].[tabulka1]
WHERE transfer = 0
GROUP BY devID
,empID) vratene
ON predane.devID = vratene.devID
AND predane.empID = vratene.empID
WHERE predane.pocet - ISNULL(vratene.pocet, 0) <> 0

Nahoru Odpovědět

8.9.2017 14:16

David Hynek

Tvůrce

David Hynek:8.9.2017 19:15

Ty dve nebo tri tabulky jsou lepsi. Uz jen proto, ze muzes vytvorit historii pujcovani. Ktere zarizeni bylo kolikrat pujceny. Nebo i stav, do kdy ma byt pujcene, nebo jak dlouho blo pujcene. A u tabulky zarizeni pak muzes evidovat, jake je k disposici, nebo historii udrzby. Samozrejme zalezi na tom jak az moc to chces mit prehledny. Ale vetsi prehlednost je vzdy lepsi. Ikdyz ji treba zatim nevyuzijes.

Nahoru Odpovědět

8.9.2017 19:15

Čím víc vím, tím víc věcí nevím.