FINÁLNÍ VÝPRODEJ KREDITŮ na ITnetwork je tady. Využij poslední šanci a získej až 80 % extra kreditů ZDARMA na náš interaktivní e-learning. Zjisti více:
NOVINKA: Kurz kybernetické bezpečnosti s akreditací MŠMT, nyní již od 0 Kč. Staň se žádaným profesionálem. Zjisti více:
Avatar
tomas
Člen
Avatar
tomas:28.8.2018 16:26

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

Id device_id empployee_id protocol_number transfer date
265 351 48 106 1  
266 134 48 106 1  
281 189 48 119 1  
282 372 48 191 1  
283 132 48 191 1  
284 421 48 273 1  
285 132 48 273 1  
286 132 48 273 1  
287 142 48 273 1  
288 189 48 350 0  
289 134 48 351 0  
290 372 48 356 0  
291 132 48 357 0  

Datum neuvádím, nebude potřeba. Jedná se o tabulku předaných zařízení zaměstnanci. Tohle je konkrétně výpis jednoho z nich. Sloupec transfér a hodnota 1 znamená, že zařízení bylo předáno, 0 že bylo vráceno. V tomto případě tedy převzal 9 zařízení a 4 z nich vrátil. Ve výsledné tabulce jsou ještě další sloupce, ale pro výběr jsou nepodstatné. Nic méně jsou v té proceduře vidět.

Zkusil jsem: moje procedura

create table #table (
                id      int,
                device_id       int,
                employee_id     int,
                date    datetime,
                state   varchar(40),
                protocol_number int,
                transfer        bit,
                name    varchar(30),
                serial_number   varchar(20),
                inventory_number        varchar(6));

        insert into #table (id, device_id, employee_id, date, state, protocol_number, transfer, name, serial_number, inventory_number)

        SELECT
       [Assigned_device].[id]
      ,[Assigned_device].[device_id]
      ,[Assigned_device].[employee_id]
          ,[Assigned_device].[date]
          ,[Assigned_device].[state]
          ,[Assigned_device].[protocol_number]
          ,[Assigned_device].[transfer]
          ,[Device].[name]
          ,Device.serial_number
          ,Device.inventory_number

        FROM [Device]
        LEFT Join Assigned_device on device.id = Assigned_device.device_id
        Where Assigned_device.employee_id = 48

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

        drop table #table

Chci docílit: Já bych potřeboval vytvořit dotaz, který by mě vrátil všechny aktuáně zapůjčené zařízení. V tomto případě má procedura mě přestává fungovat. Protože má jedno stejné zažízení předanné víckrát. Konkrétně jde o deviceID 132. původně měl tuto věci třikrát, ale jednu vrátil(ID řádku 294). Má procedura ale z výsledku vyhodí tohle ID všechno. Výsledek mého dotazu by měla v tomto případě být tabulka s pěti řádky zařízení, které stále má.

V prvním kroku vytvářím dočasnou tabulku s propojením a kompletní informací, v tom dalším dělám dva výběry z dočasné tabulky a ty porovnávám a vracím výsledek. Pokud je tam jedno ID jen jednou předané a vrácenné tak to funguje dobře, ale pokud je tam jedno zařízení dvakrát a vícekrát, tak to nefunguje.

 
Odpovědět
28.8.2018 16:26
Avatar
Jakub Švasta
Lektor
Avatar
Jakub Švasta:28.8.2018 20:39

Možná existuje lepší řešení, ale z první bych na to šel třeba takhle: zeptal bych se, kolik zařízení musí zbýt zaměstnanci půjčených (počet půjčení mínus počet vrácení pro dané zařízení), a do dočasné tabulky uložil právě tolik nejnovějších záznamů o výpůjčce. A takhle bych projel všechny zařízení cyklem (cursorem). Budu to psát pro MS-SQL, jinde se syntaxe může mírně lišit:

-- predpokladam, ze @EmployeeId bere procedura jako parametr

CREATE TABLE #Results
(
        id INT,
        device_id INT,
        employee_id INT,
        [date] DATETIME,
        [state] VARCHAR(40),
        protocol_number INT,
        [transfer] BIT,
        [name] VARCHAR(30),
        serial_number VARCHAR(20),
        inventory_number VARCHAR(6)
)

DECLARE @DeviceId INT

DECLARE C CURSOR READ_ONLY LOCAL FOR
SELECT DISTINCT device_id FROM Assigned_device WHERE employee_id = @EmployeeId
OPEN C
FETCH C INTO @DeviceId
WHILE (@@FETCH_STATUS = 0)
BEGIN

        DECLARE @DeviceCount INT
        SELECT @DeviceCount =
                (SELECT COUNT(1) FROM Assigned_device WHERE device_id = @DeviceId AND employee_id = @EmployeeId AND [transfer] = 1)
                - (SELECT COUNT(1) FROM Assigned_device WHERE device_id = @DeviceId AND employee_id = @EmployeeId AND [transfer] = 0)

        INSERT INTO #Results (id, device_id, employee_id, [date], [state], protocol_number, [transfer], [name], serial_number, inventory_number)
        SELECT TOP @DeviceCount
                AD.id, AD.device_id, AD.employee_id, AD.[date], AD.[state], AD.protocol_number, AD.[transfer], D.[name], D.serial_number, D.inventory_number
        FROM Assigned_device AD
        LEFT OUTER JOIN Device D ON D.id = AD.device_id
        WHERE AD.device_id = @DeviceId AND AD.employee_id = @EmployeeId AND AD.[transfer] = 1
        ORDER BY AD.[date] DESC         -- radit by slo i podle ad.id DESC

FETCH C INTO @DeviceId
END CLOSE C DEALLOCATE C

SELECT * FROM #Results

DROP TABLE #Results
 
Nahoru Odpovědět
28.8.2018 20:39
Avatar
don.jarducius
Člen
Avatar
don.jarducius:28.8.2018 20:50

Ahoj,
využij v dotazu case when then else end :)
Pujčil si (1) + vrátil (-1) tzn. už nemá (součet 0)

Dotaz bych postavil nějak takhle:

select * from (select device_id, empployee_id, sum(case when transfer = 1 then 1 else -1 end) Pocet
from [Assigned_device] with(nolock)
where empployee_id = 48
group by device_id, empployee_id) as tmpData inner join Device with(nolock) on tmpData.Device_ID = Device.ID
where tmpData.pocet > 0

Ppředpokládám, podle zápisu, že se jedná o MSSQL. To with(nolock) znamená že čte bez zámků a i nedokončené transakce, je o dost rychlejší, tady by dirty read vadit neměl :)

Akceptované řešení
+20 Zkušeností
+2,50 Kč
Řešení problému
Nahoru Odpovědět
28.8.2018 20:50
Ten kdo nechce hledá důvod, ten kdo chce hledá způsob
Avatar
Peter Mlich
Člen
Avatar
Peter Mlich:29.8.2018 9:04

Podle mne to datum podstatne je. Ale ne teda pro forko.

  • muze existovat 5 pujceni a 2 vraceni nebo 2 pujceni a 5 vraceni, protoze doslo k chybe pri evidenci, at uz umyslne nebo omylem

Slo by resit tak, ze zarizeni i zamest. ma chip. Chipem si otevre dvere s naradim. Zarizeni projde dvernim ramem a zaeviduje zam-chip + naradi-chip, automaticky. Stale muze dojit k chybe. Ale aspon to nijak zvlast lidi neobtezuje.

Dalo by se to resit pres group by. Vyberes si 2 radky podle datumu, zarizeni + zapujceni, zarizeni + vraceni. A podle datumu urcit, zda schazi nebo je ve stavu vraceno, asi dalsim grupem. Ten dotaz jenom naznacim, zatim jsem nic takoveho neresil...

(
(
SELECT id_radek, id_zarizeni, datum
FROM tab
WHERE datum=MAX(datum)
GROUP BY id_zarizeni, stav
) -- to by melo vratit 2 radky, posledni vypujceni a vraceni pro kazde zarizeni
WHERE datum=MAX(datum)
GROUP BY id_zarizeni
) -- z toho vytridim posledni stav podle datumu
WHERE stav=0 (pujceno a nevraceno)
-- a nakonec vyberu podle stavu

Mozna to jde zjednodusit, vynechat nejaky krok.

Editováno
 
Nahoru Odpovědět
29.8.2018 9:04
Avatar
tomas
Člen
Avatar
Odpovídá na Peter Mlich
tomas:29.8.2018 10:07

Může existovat 5 půjčení a 2 vrácení, opačně ne. Nemůže vrátit něco co si nepůjčil. Jinak se jedná o MS-SQL. Zrovna zkouším vaše návrhy, tak dám vědět. Děkuji za rady. Jinak datum mám v tabulce vložený bez času a konkrétně deviceid132 byl zapůjčen dvakrát v jeden den v rámci jednoho protokolu. Čili zde nejde rozenat který byl dříve podle datumu ani času, ale jen podle id řádku v tabulce.

 
Nahoru Odpovědět
29.8.2018 10:07
Avatar
tomas
Člen
Avatar
tomas:29.8.2018 14:16

Děkuji všem, řešení od don.jarducius mě vrátí přesně to co potřebuji. Díky.

 
Nahoru Odpovědět
29.8.2018 14:16
Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zobrazeno 6 zpráv z 6.