GalookCZ

creating tables

Mar 21st, 2021 (edited)
1,132
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /* Table Ockovani History */
  2.  
  3. CREATE TABLE vo_w
  4. SELECT
  5.     ockovaci_mista.misto_id,
  6.     ockovaci_mista.misto_nazev,
  7.     ockovaci_mista.nrpzs_kod,
  8.     ockovani.vekova_skupina,
  9.    
  10.     COUNT(ockovani.datum) as total,
  11.  COUNT(CASE WHEN ockovani.vakcina = "Comirnaty" THEN 1 ELSE null END) as pfizer,
  12.  COUNT(CASE WHEN ockovani.vakcina = "COVID-19 Vaccine Moderna" THEN 1 ELSE null END) as moderna,
  13.  COUNT(CASE WHEN ockovani.vakcina = "Comirnaty" OR ockovani.vakcina = "COVID-19 Vaccine Moderna" THEN 1 ELSE null END) as mrna,
  14.  COUNT(CASE WHEN ockovani.vakcina = "VAXZEVRIA" THEN 1 ELSE null END) as astrazeneca
  15.  
  16.  FROM `ockovani`
  17.  
  18.  JOIN ockovaci_mista ON ockovaci_mista.nrpzs_kod = ockovani.misto_kod
  19.  WHERE ockovaci_mista.status = 1 AND ockovani.datum > ((SELECT MAX(datum) FROM ockovani) - INTERVAL 11 DAY)
  20.  
  21. GROUP BY ockovaci_mista.misto_id, ockovaci_mista.misto_nazev, ockovaci_mista.nrpzs_kod, ockovani.vekova_skupina
  22.  
  23.  
  24.  
  25. /* Table Registrace History */
  26.  
  27.  
  28. CREATE TABLE vr_w
  29.  
  30. SELECT
  31.  
  32. misto_id,
  33. misto_nazev,
  34. vekova_skupina,
  35. povolani,
  36. COUNT(*) as total,
  37. COUNT(CASE WHEN rezervace = 0 THEN 1 ELSE null END) as pocet_registrace,
  38. COUNT(CASE WHEN rezervace = 1 THEN 1 ELSE null END) as pocet_rezervace,
  39. COUNT(CASE WHEN rezervace = 1 THEN 1 ELSE null END) / COUNT(*) as pomer_rezervace_total,
  40. AVG(datum_rezervace - datum) as average_wait_time
  41.  
  42.  
  43. FROM `registrace`
  44. WHERE datum > ((SELECT MAX(datum) FROM registrace) - INTERVAL 11 DAY)
  45. GROUP BY misto_id, misto_nazev, vekova_skupina, povolani
  46.  
  47.  
  48.  
RAW Paste Data