Advertisement
Guest User

Untitled

a guest
Feb 12th, 2019
84
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 9.06 KB | None | 0 0
  1.  
  2. --- Zaktualizuj bazę by zlecenia, które już się zakończyły nie były wykonywane przez kierowców ------------------------
  3.  
  4. /*
  5. Komenda archiwizuje zlecenia z datą zakończenia późniejszą niż dzisiejsza do tabeli [DBO].[ZleceniaArchiwalne], a następnie usuwa zlecenia z listy [DBO].[Kierowcy], których data zakończenia jest późniejsza niż aktualna data, usuwa również pojazdy z listy [DBO].[Kierowcy] w przypadku, gdy kierowca nie ma przypisanego, z listy [DBO].[Samochody] usuwa przyczepy, gdy pojazd nie ma przypisanego kierowcy i zmienia kategorię pojazdu bez przyczepy z BE,CE,DE na B,C,D.
  6. */
  7. --- Archiwizacja zakończonych zleceń
  8. INSERT INTO [DBO].[ZleceniaArchiwalne]
  9. SELECT nr_zlecenia, liczba_osob, waga_towaruWtonach, Data_rozpoczecia, Data_zakonczenia FROM [DBO].[Zlecenia]
  10. WHERE Data_zakonczenia < GETDATE()
  11. GO
  12.  
  13. DELETE FROM [DBO].[Zlecenia]
  14. WHERE Data_zakonczenia < GETDATE()
  15. GO
  16. --- aktualizacja [DBO].[Kierowcy] o zakończone zlecenia
  17. UPDATE [DBO].[Kierowcy]
  18. SET nr_rejestracyjny = NULL
  19. WHERE nr_zlecenia IS NULL
  20. GO
  21.  
  22. SELECT nr_rejestracyjny
  23. INTO #MyTempTable
  24. FROM [DBO].[Samochody]
  25. EXCEPT
  26. SELECT nr_rejestracyjny
  27. FROM [DBO].[Kierowcy]
  28. GO
  29.  --- Aktualizacja [DBO].[Samochody] i [DBO].[Przyczepy] o zwolnione przyczepy z powodu zakończonych zleceń
  30. UPDATE [DBO].[Samochody]
  31. SET nr_rej_przyczepy = NULL
  32. FROM #MyTempTable
  33. JOIN [DBO].[Samochody]
  34. ON #MyTempTable.nr_rejestracyjny = [DBO].[Samochody].nr_rejestracyjny
  35. WHERE #MyTempTable.nr_rejestracyjny = [DBO].[Samochody].nr_rejestracyjny
  36. GO
  37.  
  38. UPDATE [DBO].[Samochody]
  39. SET kategoria_pojazdu = 'B'
  40. WHERE kategoria_pojazdu = 'BE' AND nr_rej_przyczepy IS NULL
  41. GO
  42.  
  43. UPDATE [DBO].[Samochody]
  44. SET kategoria_pojazdu = 'C'
  45. WHERE kategoria_pojazdu = 'CE' AND nr_rej_przyczepy IS NULL
  46. GO
  47.  
  48. UPDATE [DBO].[Samochody]
  49. SET kategoria_pojazdu = 'D'
  50. WHERE kategoria_pojazdu = 'DE' AND nr_rej_przyczepy IS NULL
  51. GO
  52.  
  53. DROP TABLE #MyTempTable
  54. GO
  55.  
  56. --- wyświetlenie zaktualizowanej bazy
  57. SELECT Pracownicy.nazwisko, Kierowcy.id, Kierowcy.numer_telefonu, Kierowcy.kat_prawo_jazdy, Kierowcy.nr_rejestracyjny, Kierowcy.nr_zlecenia
  58. FROM Kierowcy
  59. LEFT JOIN Pracownicy
  60. ON Pracownicy.id = Kierowcy.id
  61.  
  62. SELECT * FROM ZleceniaArchiwalne
  63.  
  64. SELECT * FROM Zlecenia
  65.  
  66. GO
  67.  
  68. --- Zaktualizuj bazę by nowe, zlecenie trafiło bezpośrednio do wolnego pracownika -----------------------------------
  69. /*
  70. Zestaw komend odpowiadający za parowanie wolnego pracownika z nie przypisanym zleceniem, a następnie dobraniem do niego wolnego samochodu i (jeżeli wymaga tego zlecenie) odpowiedniej przyczepy. Komenda polega na zaktualizowaniu nowych zamówień o wymaganą kategorię prawa jazdy kierowcy, stworzeniu pary samochód i przyczepa w każdej kategorii (B+E, C+E, D+E), dopasowaniu wolnego zlecenia do wolnego kierowcy, dopasowaniu do kierowcy wolnego samochodu i (jeżeli wymaga tego zlecenie) przyczepy i na zakończenie operacji usunięciu tymczasowych par wolny samochód + wolna przyczepa.
  71. */
  72.  
  73. --- Komendy aktualizujące [DBO].[Zlecenia] o wymaganą kategorię prawa jazdy kierowcy do realizacji zamówienia (na wypadek pojawienia się nowych zamówień w bazie)
  74. UPDATE [DBO].[Zlecenia]
  75. SET kat_prawo_jazdy = 'B'
  76. WHERE waga_towaruWtonach <= 1.5 AND liczba_osob <= 2
  77. GO
  78.  
  79. UPDATE [DBO].[Zlecenia]
  80. SET kat_prawo_jazdy = 'B'
  81. WHERE waga_towaruWtonach = 0 AND liczba_osob > 2 AND liczba_osob <= 8
  82. GO
  83.  
  84. UPDATE [DBO].[Zlecenia]
  85. SET kat_prawo_jazdy = 'BE'
  86. WHERE waga_towaruWtonach <= 1 AND waga_towaruWtonach != 0 AND liczba_osob > 2 AND liczba_osob <= 8
  87. GO
  88.  
  89. UPDATE [DBO].[Zlecenia]
  90. SET kat_prawo_jazdy = 'BE'
  91. WHERE waga_towaruWtonach > 1.5 AND waga_towaruWtonach <= 2.5
  92. GO
  93.  
  94. UPDATE [DbO].[Zlecenia]
  95. SET kat_prawo_jazdy = 'C'
  96. WHERE waga_towaruWtonach > 2.5 AND waga_towaruWtonach <= 6 AND liczba_osob <= 2
  97. GO
  98.  
  99. UPDATE [DBO].[Zlecenia]
  100. SET kat_prawo_jazdy = 'CE'
  101. WHERE waga_towaruWtonach > 6 AND liczba_osob <= 2
  102. GO
  103.  
  104. UPDATE [DBO].[Zlecenia]
  105. SET kat_prawo_jazdy = 'D'
  106. WHERE waga_towaruWtonach = 0 AND liczba_osob < 49 AND liczba_osob > 8
  107. GO
  108.  
  109. UPDATE [DBO].[Zlecenia]
  110. SET kat_prawo_jazdy = 'DE'
  111. WHERE waga_towaruWtonach <= 1 AND waga_towaruWtonach != 0 AND liczba_osob < 49 AND liczba_osob > 8
  112. GO
  113.  
  114. --- tworzenie po jednej parze samochód + przyczepa w tabeli [DBO].[Samochody] na wypadek pojawienia się zlecenia z kategorią BE, CE, DE
  115. CREATE TABLE TempTable1 (nr_rejestracyjny char(7) NULL, ladownoscWtonach decimal(5,3) NULL)
  116. GO
  117.  
  118. INSERT INTO [DBO].[TempTable1] (nr_rejestracyjny)
  119. SELECT nr_rejestracyjny
  120. FROM [DBO].[Przyczepy]
  121. EXCEPT
  122. SELECT nr_rej_przyczepy
  123. FROM [DBO].[Samochody]
  124.  
  125. UPDATE [DBO].[TempTable1]
  126. SET [DBO].[TempTable1].ladownoscWtonach = [DBO].[Przyczepy].ladownoscWtonach
  127. FROM [DBO].[Przyczepy]
  128. JOIN [DBO].[TempTable1]
  129. ON [DBO].[TempTable1].nr_rejestracyjny = [DBO].[Przyczepy].nr_rejestracyjny
  130. WHERE [DBO].[TempTable1].nr_rejestracyjny = [DBO].[Przyczepy].nr_rejestracyjny
  131. GO
  132.  
  133. ALTER TABLE [DBO].[TempTable1]
  134. ADD Kategoria char(2) NULL
  135. GO
  136.  
  137. UPDATE TOP (1) [DBO].[TempTable1]
  138. SET Kategoria = 'B'
  139. WHERE ladownoscWtonach = 1
  140. GO
  141.  
  142. UPDATE TOP (1) [DBO].[TempTable1]
  143. SET Kategoria = 'C'
  144. WHERE ladownoscWtonach = 8
  145. GO
  146.  
  147. UPDATE TOP (1) [DBO].[TempTable1]
  148. SET Kategoria = 'D'
  149. WHERE ladownoscWtonach = 1
  150. AND
  151. Kategoria IS NULL
  152. GO
  153.  
  154. SET ROWCOUNT 1
  155.  
  156. UPDATE [DBO].[Samochody]
  157. SET [DBO].[Samochody].nr_rej_przyczepy = [DBO].[TempTable1].nr_rejestracyjny,
  158. [DBO].[Samochody].kategoria_pojazdu = 'BE'
  159. FROM [DBO].[TempTable1]
  160. JOIN [DBO].[Samochody]
  161. ON [DBO].[Samochody].kategoria_pojazdu = [DBO].[TempTable1].kategoria
  162. WHERE [DBO].[Samochody].kategoria_pojazdu = 'B'
  163. GO
  164.  
  165. UPDATE [DBO].[Samochody]
  166. SET [DBO].[Samochody].nr_rej_przyczepy = [DBO].[TempTable1].nr_rejestracyjny,
  167. [DBO].[Samochody].kategoria_pojazdu = 'CE'
  168. FROM [DBO].[TempTable1]
  169. JOIN [DBO].[Samochody]
  170. ON [DBO].[Samochody].kategoria_pojazdu = [DBO].[TempTable1].kategoria
  171. WHERE [DBO].[Samochody].kategoria_pojazdu = 'C'
  172. GO
  173.  
  174. UPDATE [DBO].[Samochody]
  175. SET [DBO].[Samochody].nr_rej_przyczepy = [DBO].[TempTable1].nr_rejestracyjny,
  176. [DBO].[Samochody].kategoria_pojazdu = 'DE'
  177. FROM [DBO].[TempTable1]
  178. JOIN [DBO].[Samochody]
  179. ON [DBO].[Samochody].kategoria_pojazdu = [DBO].[TempTable1].kategoria
  180. WHERE [DBO].[Samochody].kategoria_pojazdu = 'D'
  181. GO
  182.  
  183. SET ROWCOUNT 0
  184.  
  185. DROP TABLE [DBO].[TempTable1]
  186. GO
  187.  
  188. --- dobieranie kierowcy do zlecenia poprzez dodanie nowego (nie będącego jeszcze sparowanego z kierowcą) w tabeli [DBO].[Kierowcy]
  189. CREATE TABLE [DBO].[TempTable1] (kat_prawo_jazdy char(2), nr_zlecenia char(6))
  190. GO
  191.  
  192. INSERT INTO [DBO].[TempTable1] (kat_prawo_jazdy, nr_zlecenia)
  193. SELECT kat_prawo_jazdy, nr_zlecenia
  194. FROM [DBO].[Zlecenia]
  195. EXCEPT
  196. SELECT kat_prawo_jazdy,
  197. nr_zlecenia
  198. FROM [DBO].[Kierowcy]
  199.  
  200. GO
  201.  
  202. SET ROWCOUNT 1
  203.  
  204. UPDATE [DBO].[Kierowcy]
  205. SET [DBO].[Kierowcy].nr_zlecenia = [DBO].[TempTable1].nr_zlecenia
  206. FROM [DBO].[TempTable1]
  207. INNER JOIN [DBO].[Kierowcy]
  208. ON [DBO].[Kierowcy].kat_prawo_jazdy = [DBO].[TempTable1].kat_prawo_jazdy
  209. WHERE [DBO].[Kierowcy].nr_zlecenia IS NULL
  210.  
  211. SET ROWCOUNT 0
  212.  
  213. GO
  214.  
  215. DROP TABLE [DBO].[TempTable1]
  216.  
  217. GO
  218.  
  219. --- dobieranie samochodu do zlecenia poprzez dodanie nowego (nie będącego sparowanym) w tabeli [DBO].[Kierowcy]
  220. CREATE TABLE [DBO].[TempTable1] (kat_prawo_jazdy char(2), nr_rejestracyjny char(7))
  221. GO
  222.  
  223. INSERT INTO [DBO].[TempTable1] (kat_prawo_jazdy, nr_rejestracyjny)
  224. SELECT kategoria_pojazdu, nr_rejestracyjny
  225. FROM [DBO].[Samochody]
  226. EXCEPT
  227. SELECT kat_prawo_jazdy,
  228. nr_rejestracyjny
  229. FROM [DBO].[Kierowcy]
  230.  
  231. GO
  232.  
  233. SET ROWCOUNT 1
  234.  
  235. UPDATE [DBO].[Kierowcy]
  236. SET [DBO].[Kierowcy].nr_rejestracyjny = [DBO].[TempTable1].nr_rejestracyjny
  237. FROM [DBO].[TempTable1]
  238. INNER JOIN [DBO].[Kierowcy]
  239. ON [DBO].[TempTable1].kat_prawo_jazdy = [DBO].[Kierowcy].kat_prawo_jazdy
  240. WHERE [DBO].[Kierowcy].nr_rejestracyjny IS NULL
  241.  
  242. SET ROWCOUNT 0
  243.  
  244. GO
  245.  
  246. DROP TABLE [DBO].[TempTable1]
  247.  
  248. GO
  249.  
  250. --- usuwanie tymczasowych par samochodów z przyczepą w tabeli [DBO].[Samochody] stworzonych na czas dodawania nowego zlecenia do kierowcy
  251. SELECT nr_rejestracyjny
  252. INTO #MyTempTable
  253. FROM [DBO].[Samochody]
  254. EXCEPT
  255. SELECT nr_rejestracyjny
  256. FROM [DBO].[Kierowcy]
  257. GO
  258.  
  259. UPDATE [DBO].[Samochody]
  260. SET nr_rej_przyczepy = NULL
  261. FROM #MyTempTable
  262. JOIN [DBO].[Samochody]
  263. ON #MyTempTable.nr_rejestracyjny = [DBO].[Samochody].nr_rejestracyjny
  264. WHERE #MyTempTable.nr_rejestracyjny = [DBO].[Samochody].nr_rejestracyjny
  265. GO
  266.  
  267. UPDATE [DBO].[Samochody]
  268. SET kategoria_pojazdu = 'B'
  269. WHERE kategoria_pojazdu = 'BE' AND nr_rej_przyczepy IS NULL
  270. GO
  271.  
  272. UPDATE [DBO].[Samochody]
  273. SET kategoria_pojazdu = 'C'
  274. WHERE kategoria_pojazdu = 'CE' AND nr_rej_przyczepy IS NULL
  275. GO
  276.  
  277. UPDATE [DBO].[Samochody]
  278. SET kategoria_pojazdu = 'D'
  279. WHERE kategoria_pojazdu = 'DE' AND nr_rej_przyczepy IS NULL
  280. GO
  281.  
  282. DROP TABLE #MyTempTable
  283. GO
  284.  
  285. --- wyświetlenie zaktualizowanej tabeli
  286. SELECT Pracownicy.nazwisko, Kierowcy.id, Kierowcy.numer_telefonu, Kierowcy.kat_prawo_jazdy, Kierowcy.nr_rejestracyjny, Samochody.nr_rej_przyczepy, Kierowcy.nr_zlecenia
  287. FROM Kierowcy
  288. LEFT JOIN Pracownicy
  289. ON Pracownicy.id = Kierowcy.id
  290. JOIN Samochody
  291. ON Samochody.nr_rejestracyjny = Kierowcy.nr_rejestracyjny
  292. GO
  293.  
  294. /* UWAGA Wyniki po aktualizacji tabeli mogą wyświetlać się z lekkim opóźnieniem */
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement