Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --- Zaktualizuj bazę by zlecenia, które już się zakończyły nie były wykonywane przez kierowców ------------------------
- /*
- 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.
- */
- --- Archiwizacja zakończonych zleceń
- INSERT INTO [DBO].[ZleceniaArchiwalne]
- SELECT nr_zlecenia, liczba_osob, waga_towaruWtonach, Data_rozpoczecia, Data_zakonczenia FROM [DBO].[Zlecenia]
- WHERE Data_zakonczenia < GETDATE()
- GO
- DELETE FROM [DBO].[Zlecenia]
- WHERE Data_zakonczenia < GETDATE()
- GO
- --- aktualizacja [DBO].[Kierowcy] o zakończone zlecenia
- UPDATE [DBO].[Kierowcy]
- SET nr_rejestracyjny = NULL
- WHERE nr_zlecenia IS NULL
- GO
- SELECT nr_rejestracyjny
- INTO #MyTempTable
- FROM [DBO].[Samochody]
- EXCEPT
- SELECT nr_rejestracyjny
- FROM [DBO].[Kierowcy]
- GO
- --- Aktualizacja [DBO].[Samochody] i [DBO].[Przyczepy] o zwolnione przyczepy z powodu zakończonych zleceń
- UPDATE [DBO].[Samochody]
- SET nr_rej_przyczepy = NULL
- FROM #MyTempTable
- JOIN [DBO].[Samochody]
- ON #MyTempTable.nr_rejestracyjny = [DBO].[Samochody].nr_rejestracyjny
- WHERE #MyTempTable.nr_rejestracyjny = [DBO].[Samochody].nr_rejestracyjny
- GO
- UPDATE [DBO].[Samochody]
- SET kategoria_pojazdu = 'B'
- WHERE kategoria_pojazdu = 'BE' AND nr_rej_przyczepy IS NULL
- GO
- UPDATE [DBO].[Samochody]
- SET kategoria_pojazdu = 'C'
- WHERE kategoria_pojazdu = 'CE' AND nr_rej_przyczepy IS NULL
- GO
- UPDATE [DBO].[Samochody]
- SET kategoria_pojazdu = 'D'
- WHERE kategoria_pojazdu = 'DE' AND nr_rej_przyczepy IS NULL
- GO
- DROP TABLE #MyTempTable
- GO
- --- wyświetlenie zaktualizowanej bazy
- SELECT Pracownicy.nazwisko, Kierowcy.id, Kierowcy.numer_telefonu, Kierowcy.kat_prawo_jazdy, Kierowcy.nr_rejestracyjny, Kierowcy.nr_zlecenia
- FROM Kierowcy
- LEFT JOIN Pracownicy
- ON Pracownicy.id = Kierowcy.id
- SELECT * FROM ZleceniaArchiwalne
- SELECT * FROM Zlecenia
- GO
- --- Zaktualizuj bazę by nowe, zlecenie trafiło bezpośrednio do wolnego pracownika -----------------------------------
- /*
- 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.
- */
- --- Komendy aktualizujące [DBO].[Zlecenia] o wymaganą kategorię prawa jazdy kierowcy do realizacji zamówienia (na wypadek pojawienia się nowych zamówień w bazie)
- UPDATE [DBO].[Zlecenia]
- SET kat_prawo_jazdy = 'B'
- WHERE waga_towaruWtonach <= 1.5 AND liczba_osob <= 2
- GO
- UPDATE [DBO].[Zlecenia]
- SET kat_prawo_jazdy = 'B'
- WHERE waga_towaruWtonach = 0 AND liczba_osob > 2 AND liczba_osob <= 8
- GO
- UPDATE [DBO].[Zlecenia]
- SET kat_prawo_jazdy = 'BE'
- WHERE waga_towaruWtonach <= 1 AND waga_towaruWtonach != 0 AND liczba_osob > 2 AND liczba_osob <= 8
- GO
- UPDATE [DBO].[Zlecenia]
- SET kat_prawo_jazdy = 'BE'
- WHERE waga_towaruWtonach > 1.5 AND waga_towaruWtonach <= 2.5
- GO
- UPDATE [DbO].[Zlecenia]
- SET kat_prawo_jazdy = 'C'
- WHERE waga_towaruWtonach > 2.5 AND waga_towaruWtonach <= 6 AND liczba_osob <= 2
- GO
- UPDATE [DBO].[Zlecenia]
- SET kat_prawo_jazdy = 'CE'
- WHERE waga_towaruWtonach > 6 AND liczba_osob <= 2
- GO
- UPDATE [DBO].[Zlecenia]
- SET kat_prawo_jazdy = 'D'
- WHERE waga_towaruWtonach = 0 AND liczba_osob < 49 AND liczba_osob > 8
- GO
- UPDATE [DBO].[Zlecenia]
- SET kat_prawo_jazdy = 'DE'
- WHERE waga_towaruWtonach <= 1 AND waga_towaruWtonach != 0 AND liczba_osob < 49 AND liczba_osob > 8
- GO
- --- tworzenie po jednej parze samochód + przyczepa w tabeli [DBO].[Samochody] na wypadek pojawienia się zlecenia z kategorią BE, CE, DE
- CREATE TABLE TempTable1 (nr_rejestracyjny char(7) NULL, ladownoscWtonach decimal(5,3) NULL)
- GO
- INSERT INTO [DBO].[TempTable1] (nr_rejestracyjny)
- SELECT nr_rejestracyjny
- FROM [DBO].[Przyczepy]
- EXCEPT
- SELECT nr_rej_przyczepy
- FROM [DBO].[Samochody]
- UPDATE [DBO].[TempTable1]
- SET [DBO].[TempTable1].ladownoscWtonach = [DBO].[Przyczepy].ladownoscWtonach
- FROM [DBO].[Przyczepy]
- JOIN [DBO].[TempTable1]
- ON [DBO].[TempTable1].nr_rejestracyjny = [DBO].[Przyczepy].nr_rejestracyjny
- WHERE [DBO].[TempTable1].nr_rejestracyjny = [DBO].[Przyczepy].nr_rejestracyjny
- GO
- ALTER TABLE [DBO].[TempTable1]
- ADD Kategoria char(2) NULL
- GO
- UPDATE TOP (1) [DBO].[TempTable1]
- SET Kategoria = 'B'
- WHERE ladownoscWtonach = 1
- GO
- UPDATE TOP (1) [DBO].[TempTable1]
- SET Kategoria = 'C'
- WHERE ladownoscWtonach = 8
- GO
- UPDATE TOP (1) [DBO].[TempTable1]
- SET Kategoria = 'D'
- WHERE ladownoscWtonach = 1
- AND
- Kategoria IS NULL
- GO
- SET ROWCOUNT 1
- UPDATE [DBO].[Samochody]
- SET [DBO].[Samochody].nr_rej_przyczepy = [DBO].[TempTable1].nr_rejestracyjny,
- [DBO].[Samochody].kategoria_pojazdu = 'BE'
- FROM [DBO].[TempTable1]
- JOIN [DBO].[Samochody]
- ON [DBO].[Samochody].kategoria_pojazdu = [DBO].[TempTable1].kategoria
- WHERE [DBO].[Samochody].kategoria_pojazdu = 'B'
- GO
- UPDATE [DBO].[Samochody]
- SET [DBO].[Samochody].nr_rej_przyczepy = [DBO].[TempTable1].nr_rejestracyjny,
- [DBO].[Samochody].kategoria_pojazdu = 'CE'
- FROM [DBO].[TempTable1]
- JOIN [DBO].[Samochody]
- ON [DBO].[Samochody].kategoria_pojazdu = [DBO].[TempTable1].kategoria
- WHERE [DBO].[Samochody].kategoria_pojazdu = 'C'
- GO
- UPDATE [DBO].[Samochody]
- SET [DBO].[Samochody].nr_rej_przyczepy = [DBO].[TempTable1].nr_rejestracyjny,
- [DBO].[Samochody].kategoria_pojazdu = 'DE'
- FROM [DBO].[TempTable1]
- JOIN [DBO].[Samochody]
- ON [DBO].[Samochody].kategoria_pojazdu = [DBO].[TempTable1].kategoria
- WHERE [DBO].[Samochody].kategoria_pojazdu = 'D'
- GO
- SET ROWCOUNT 0
- DROP TABLE [DBO].[TempTable1]
- GO
- --- dobieranie kierowcy do zlecenia poprzez dodanie nowego (nie będącego jeszcze sparowanego z kierowcą) w tabeli [DBO].[Kierowcy]
- CREATE TABLE [DBO].[TempTable1] (kat_prawo_jazdy char(2), nr_zlecenia char(6))
- GO
- INSERT INTO [DBO].[TempTable1] (kat_prawo_jazdy, nr_zlecenia)
- SELECT kat_prawo_jazdy, nr_zlecenia
- FROM [DBO].[Zlecenia]
- EXCEPT
- SELECT kat_prawo_jazdy,
- nr_zlecenia
- FROM [DBO].[Kierowcy]
- GO
- SET ROWCOUNT 1
- UPDATE [DBO].[Kierowcy]
- SET [DBO].[Kierowcy].nr_zlecenia = [DBO].[TempTable1].nr_zlecenia
- FROM [DBO].[TempTable1]
- INNER JOIN [DBO].[Kierowcy]
- ON [DBO].[Kierowcy].kat_prawo_jazdy = [DBO].[TempTable1].kat_prawo_jazdy
- WHERE [DBO].[Kierowcy].nr_zlecenia IS NULL
- SET ROWCOUNT 0
- GO
- DROP TABLE [DBO].[TempTable1]
- GO
- --- dobieranie samochodu do zlecenia poprzez dodanie nowego (nie będącego sparowanym) w tabeli [DBO].[Kierowcy]
- CREATE TABLE [DBO].[TempTable1] (kat_prawo_jazdy char(2), nr_rejestracyjny char(7))
- GO
- INSERT INTO [DBO].[TempTable1] (kat_prawo_jazdy, nr_rejestracyjny)
- SELECT kategoria_pojazdu, nr_rejestracyjny
- FROM [DBO].[Samochody]
- EXCEPT
- SELECT kat_prawo_jazdy,
- nr_rejestracyjny
- FROM [DBO].[Kierowcy]
- GO
- SET ROWCOUNT 1
- UPDATE [DBO].[Kierowcy]
- SET [DBO].[Kierowcy].nr_rejestracyjny = [DBO].[TempTable1].nr_rejestracyjny
- FROM [DBO].[TempTable1]
- INNER JOIN [DBO].[Kierowcy]
- ON [DBO].[TempTable1].kat_prawo_jazdy = [DBO].[Kierowcy].kat_prawo_jazdy
- WHERE [DBO].[Kierowcy].nr_rejestracyjny IS NULL
- SET ROWCOUNT 0
- GO
- DROP TABLE [DBO].[TempTable1]
- GO
- --- usuwanie tymczasowych par samochodów z przyczepą w tabeli [DBO].[Samochody] stworzonych na czas dodawania nowego zlecenia do kierowcy
- SELECT nr_rejestracyjny
- INTO #MyTempTable
- FROM [DBO].[Samochody]
- EXCEPT
- SELECT nr_rejestracyjny
- FROM [DBO].[Kierowcy]
- GO
- UPDATE [DBO].[Samochody]
- SET nr_rej_przyczepy = NULL
- FROM #MyTempTable
- JOIN [DBO].[Samochody]
- ON #MyTempTable.nr_rejestracyjny = [DBO].[Samochody].nr_rejestracyjny
- WHERE #MyTempTable.nr_rejestracyjny = [DBO].[Samochody].nr_rejestracyjny
- GO
- UPDATE [DBO].[Samochody]
- SET kategoria_pojazdu = 'B'
- WHERE kategoria_pojazdu = 'BE' AND nr_rej_przyczepy IS NULL
- GO
- UPDATE [DBO].[Samochody]
- SET kategoria_pojazdu = 'C'
- WHERE kategoria_pojazdu = 'CE' AND nr_rej_przyczepy IS NULL
- GO
- UPDATE [DBO].[Samochody]
- SET kategoria_pojazdu = 'D'
- WHERE kategoria_pojazdu = 'DE' AND nr_rej_przyczepy IS NULL
- GO
- DROP TABLE #MyTempTable
- GO
- --- wyświetlenie zaktualizowanej tabeli
- SELECT Pracownicy.nazwisko, Kierowcy.id, Kierowcy.numer_telefonu, Kierowcy.kat_prawo_jazdy, Kierowcy.nr_rejestracyjny, Samochody.nr_rej_przyczepy, Kierowcy.nr_zlecenia
- FROM Kierowcy
- LEFT JOIN Pracownicy
- ON Pracownicy.id = Kierowcy.id
- JOIN Samochody
- ON Samochody.nr_rejestracyjny = Kierowcy.nr_rejestracyjny
- GO
- /* UWAGA Wyniki po aktualizacji tabeli mogą wyświetlać się z lekkim opóźnieniem */
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement