Kwerenda porównująca rekordy po dacie i nie tylko...

Użytkowanie programu bazodanowego
panbaryla
Posty: 50
Rejestracja: pt sty 14, 2011 11:29 am

Kwerenda porównująca rekordy po dacie i nie tylko...

Post autor: panbaryla »

Poszukuję następującego rozwiązania:

Mam tabeli "Serwis" następujące rekordy
- id_serwis
- NumerSeryjny
- data_serwisu
- data_planowanego
- data_przeglądu
- data_planowanego_przeglądu

Potrzebuję tak przefiltrować rekordy "- data_planowanego" i "- data_serwisu" i tak sam "data_planowanego_przeglądu" i "data_przeglądu" aby kwerenda wyrzuciła czy data serwisu pokryła się z datą planowanego lub nie, innymi słowy czy czynność została wykonana lub nie w danym miesiącu.
Libre Office, wersja: 7.3.5.2
Debian GNU/Linux 64 bit
Jan_J
Posty: 4558
Rejestracja: pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: Kwerenda porównująca rekordy po dacie i nie tylko...

Post autor: Jan_J »

Najprostszy filtr

Kod: Zaznacz cały

select * from Serwis where data_serwisu = data_planowanego;
Z ograniczeniem do serwisów planowanych w bieżącym miesiącu

Kod: Zaznacz cały

select * from Serwis where data_serwisu = data_planowanego
and extract(year from data_planowanego) = extract(year from current_date)
and extract(month from data_planowanego) = extract(month from current_date);
Testowane na bazie wewnętrznej ODB w OOo 3.3, ale składnia, jako że zgodna standardem, będzie poprawna także w wielu bazach serwerowych.

Natomiast gdyby chcieć w każdym wierszu uzskać odpowiedź na pytanie “czy daty są zgodne”, potrzeba więcej zachodu. Obsługa wyrażeń logicznych jako wartości kolumn jest w HSQLDB niedorobiona, i zapytanie

Kod: Zaznacz cały

select NumerSeryjny, data_serwisu = data_planowanego as "zgodne" from Serwis
będzie potraktowane jako błędne, mimo że jest zgodne ze standardem.
Metoda obejścia:

Kod: Zaznacz cały

select 
    NumerSeryjny,
    case when data_serwisu = data_planowanego then 'Tak' else 'Nie' end as "zgodne" 
from Serwis
Mam nadzieję, że o to chodziło.
JJ
LO (7.6|24.2) ∙ Python (3.12|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
panbaryla
Posty: 50
Rejestracja: pt sty 14, 2011 11:29 am

Re: Kwerenda porównująca rekordy po dacie i nie tylko...

Post autor: panbaryla »

Dzięki za pomoc. Czuję, że rozwiązanie jest blisko ale to jeszcze nie to. Ideałem było by gdyby można było w danym przedziale czasowym wyrzucić te które się nie pokrywają...
Libre Office, wersja: 7.3.5.2
Debian GNU/Linux 64 bit
Jan_J
Posty: 4558
Rejestracja: pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: Kwerenda porównująca rekordy po dacie i nie tylko...

Post autor: Jan_J »

panbaryla pisze:Dzięki za pomoc. Czuję, że rozwiązanie jest blisko ale to jeszcze nie to. Ideałem było by gdyby można było w danym przedziale czasowym wyrzucić te które się nie pokrywają...
Na to chyba wystarczy zanegować pierwszą część warunku filtra, czyli

Kod: Zaznacz cały

... data_serwisu <> data_planowanego ...
JJ
LO (7.6|24.2) ∙ Python (3.12|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
panbaryla
Posty: 50
Rejestracja: pt sty 14, 2011 11:29 am

Re: Kwerenda porównująca rekordy po dacie i nie tylko...

Post autor: panbaryla »

Zrobiłem tak jak poniżej... i jako taką wiedzę mi to daję. Ale im dalej w las tym więcej drzew :)
Dobrze by było wiedzieć dlaczego "NIE" bo nie wiadomo czy serwis z planowanym nie pokrywają się w ogóle bo może został wykonany z opóźnieniem (w innym czasie niż zaplanowany) i nie ma potrzeby wszczynać alarmu...

Kod: Zaznacz cały

SELECT DISTINCT "Klienci"."Nazwa", "Maszyna"."NumerSeryjny", "Maszyna"."Opis", "Maszyna"."TypAdresMaszyna", "Maszyna"."Ulica", "Maszyna"."Numer", "Maszyna"."Miasto", "Serwis"."data_serwisu", CASE WHEN "data_serwisu" <> "data_planowanego" THEN 'Tak' ELSE 'Nie' END AS "Wykonane zgodnie z planem" FROM "Serwis", "Maszyna", "Klienci" WHERE "Serwis"."NumerSeryjny" = "Maszyna"."NumerSeryjny" AND "Maszyna"."NIP" = "Klienci"."NIP" AND "Serwis"."data_serwisu" BETWEEN :data__od AND :data_do ORDER BY "Serwis"."data_serwisu" ASC
Libre Office, wersja: 7.3.5.2
Debian GNU/Linux 64 bit
panbaryla
Posty: 50
Rejestracja: pt sty 14, 2011 11:29 am

Re: Kwerenda porównująca rekordy po dacie i nie tylko...

Post autor: panbaryla »

Odnośnie tematu powyżej...
Potrzebuję wykonać, jak dla mnie skomplikowaną kwerendę...

Wyrzeźbiłem sobie taką

Kod: Zaznacz cały

SELECT "Klienci"."Nazwa", "Maszyna"."Miasto", "Maszyna"."NumerSeryjny", "Maszyna"."Ulica", "Maszyna"."Numer", "Maszyna"."Opis", "Maszyna"."TypMaszyny", "Serwis2"."data_planowanego" FROM "Maszyna", "Klienci", "Serwis2" WHERE "Maszyna"."NIP" = "Klienci"."NIP" AND "Serwis2"."NumerSeryjny" = "Maszyna"."NumerSeryjny" AND "Serwis2"."data_planowanego" BETWEEN :data__od AND :data_do ORDER BY "Serwis2"."data_planowanego" ASC
I ona wyrzuca mi wszystkie planowane serwisy na dany miesiąc.

Jak teraz zapytać bazy aby po otrzymaniu wyników kwerendy powyżej dodatkowo sprawdził np czy dany numer/numery seryjny nie został przypisany do rekordu "data_serwisu" (czyli wykonany) w minionym przedziale czasowym lub przez ostatnie np 182 dni?
Libre Office, wersja: 7.3.5.2
Debian GNU/Linux 64 bit
panbaryla
Posty: 50
Rejestracja: pt sty 14, 2011 11:29 am

Re: Kwerenda porównująca rekordy po dacie i nie tylko...

Post autor: panbaryla »

Hehhe...potrzeba matką wynalazku :)
Robię teraz tak, że drukuję sobie dane z 1 kwerendy a z 2 kwerendy...

Kod: Zaznacz cały

SELECT "Klienci"."Nazwa", "Maszyna"."Miasto", "Maszyna"."NumerSeryjny", "Maszyna"."Ulica", "Maszyna"."Numer", "Maszyna"."Opis", "Maszyna"."TypMaszyny", "Serwis2"."data_serwisu" FROM "Maszyna", "Klienci", "Serwis2" WHERE "Maszyna"."NIP" = "Klienci"."NIP" AND "Serwis2"."NumerSeryjny" = "Maszyna"."NumerSeryjny" AND ( "Maszyna"."NumerSeryjny" = '006124' AND "Serwis2"."data_serwisu" BETWEEN :data__od AND :data_do OR "Maszyna"."NumerSeryjny" = '006125' ) ORDER BY "Serwis2"."data_serwisu" ASC
...sprawdzam numery które mnie interesują.

Domyślam się, że całą tą operację z porównywaniem poszczególnych rekordów w 2 różnych kwerendach można jakoś zgrabnie napisać ale niestety nie wiem jak? :D
Libre Office, wersja: 7.3.5.2
Debian GNU/Linux 64 bit
panbaryla
Posty: 50
Rejestracja: pt sty 14, 2011 11:29 am

Re: Kwerenda porównująca rekordy po dacie i nie tylko...

Post autor: panbaryla »

Nie wie czy to zgodne z regulaminem takie wykopaliska ale... :)
Baza od kilku lat hula na PostgreSQL 9.3 dlatego mogę swobodnie wywoływać polecenia SQL.

Struktura tabeli "Serwis" :
- id_serwis
- NumerSeryjny
- data_serwisu
- data_planowanego
- data_przeglądu
- data_planowanego_przeglądu

Chodzi mi o kwerendę która do "data_serwisu" doda interwał 180 dni i wypluje go jako "data_planowanego" Czyli "powie mi ile np serwisów wypada w danym miesiącu w przyszłości (przy założeniu, że serwis wykonywany jest co 180 dni)

Kod: Zaznacz cały

select * from "Serwis2" where "data_serwisu" DATE_ADD("data_serwisu",INTERVAL 180 DAY) AS data planowanego;
Ale niestety coś knocę...
Libre Office, wersja: 7.3.5.2
Debian GNU/Linux 64 bit
Jan_J
Posty: 4558
Rejestracja: pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: Kwerenda porównująca rekordy po dacie i nie tylko...

Post autor: Jan_J »

Chodzi mi o kwerendę która do "data_serwisu" doda interwał 180 dni i wypluje go jako "data_planowanego" Czyli "powie mi ile np serwisów wypada w danym miesiącu w przyszłości (przy założeniu, że serwis wykonywany jest co 180 dni)

Kod: Zaznacz cały Rozszerz widok
select * from "Serwis2" where "data_serwisu" DATE_ADD("data_serwisu",INTERVAL 180 DAY) AS data planowanego;
Doda w odpowiedzi (SELECT), zmieni w istniejących rekordach bazy (UPDATE) czy dopisze jako nowe rekordy (INSERT)?

Dla przysłania w odpowiedzi coś w rodzaju

Kod: Zaznacz cały

select *, DATE_ADD("data_serwisu", INTERVAL 180 DAY) AS "data następnego" 
from "Serwis2" where WARUNEK 
przy czym warunek po WHERE posłuży tylko do ograniczenia zakresu wierszy, np. do tych z bieżącego miesiąca.
JJ
LO (7.6|24.2) ∙ Python (3.12|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
panbaryla
Posty: 50
Rejestracja: pt sty 14, 2011 11:29 am

Re: Kwerenda porównująca rekordy po dacie i nie tylko...

Post autor: panbaryla »

O co chodzi, ten zabieg ma mi służyć kontroli serwisów w przyszłości tak aby liczba planowanych była wiadoma i na tej podstawie można było planować np: zaopatrzenie w materiały potrzebne do ich wykonania czy zmianę ich daty w przypadku, jeżeli jest ich za dużo w danym miesiącu a za mało w innym.
Chodzi o kwerendę z obecnych danych której wynik i tak jest obrabiany potem w calcu. Tu najbardziej sensowne byłoby zmiana w istniejących rekordach bazy (UPDATE). W tej chwili te pola są puste.
Dodatkowo nie wiem jak spowodować aby np w polu formularza miał opcję wyboru np co 180 dni od daty wykonania (czy jakaś inna zdefiniowana)
Libre Office, wersja: 7.3.5.2
Debian GNU/Linux 64 bit
panbaryla
Posty: 50
Rejestracja: pt sty 14, 2011 11:29 am

Re: Kwerenda porównująca rekordy po dacie i nie tylko...

Post autor: panbaryla »

Wstępnie, wyrzeźbiłem coś takiego:

Kod: Zaznacz cały

SELECT "data_serwisu" + INTERVAL '180 days' as "data planowanego" FROM "Serwis2" 
Jak teraz pogrupować "data planowanego" wszystkie rekordy z poszczególnych miesięcy? Tak abym otrzymał wynik, że w tym, a w tym miesiącu wszystkich planowanych jest X serwisów?
Libre Office, wersja: 7.3.5.2
Debian GNU/Linux 64 bit
Jan_J
Posty: 4558
Rejestracja: pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: Kwerenda porównująca rekordy po dacie i nie tylko...

Post autor: Jan_J »

SELECT
numer_miesiąca, funkcja_zliczająca
FROM Serwis2
GROUP BY numer_miesiąca

czyli w Twoim przypadku

Kod: Zaznacz cały

SELECT 
    EXTRACT(MONTH FROM "data_serwisu" + INTERVAL '180 days') as "miesiąc planowanego",
    COUNT(1)
FROM "Serwis2" 
GROUP BY EXTRACT(MONTH FROM "data_serwisu" + INTERVAL '180 days')
ORDER BY "miesiąc planowanego"
Powtórzenie klauzuli wybieranej i grupującej nie boli, bo zapewne zostanie wychwycone przez optymalizator. A jeśli chcemy go uniknąć, to można się posłużyć perspektywą wyznaczającą daty przeglądów (coś jak Twoje poprzednie zapytanie), i grupowanie prowadzić w zapytaniu do tej perspektywy. Ew. to samo w locie klauzulą WITH, ale nie każdy system ją zinterpretuje.

Ops!, tym sposobem rozróżnimy miesiące roku, ale nie miesiące na osi czasu. Trzeba by wyodrębniać pary rok, miesiąc i wg nich grupować. Zarówno grupowanie jak porządkowanie da się przeprowadzać wg złożonego kryterium

group by a, b
order by a, b

w tym przypadku

Kod: Zaznacz cały

SELECT 
    EXTRACT(YEAR FROM "data_serwisu" + INTERVAL '180 days') as "rok planowanego",
    EXTRACT(MONTH FROM "data_serwisu" + INTERVAL '180 days') as "miesiąc planowanego",
    COUNT(1)
FROM "Serwis2" 
GROUP BY EXTRACT(YEAR FROM "data_serwisu" + INTERVAL '180 days'),
                EXTRACT(MONTH FROM "data_serwisu" + INTERVAL '180 days'),
ORDER BY "rok planowanego", "miesiąc planowanego"
albo ze wskazanym wyżej użyciem perspektywy

Kod: Zaznacz cały

CREATE VIEW przeglady AS
SELECT
    item,
    EXTRACT(YEAR FROM "data_serwisu" + INTERVAL '180 days') AS "rok planowanego",
    EXTRACT(MONTH FROM "data_serwisu" + INTERVAL '180 days') AS "miesiąc planowanego"
FROM "Serwis2" 
byłoby prościej, bo

Kod: Zaznacz cały

SELECT 
   "rok planowanego",    "miesiąc planowanego",  COUNT(1) AS "liczba przeglądów"
FROM przeglady
GROUP BY "rok planowanego", "miesiąc planowanego"
ORDER BY "rok planowanego", "miesiąc planowanego"
Tak się da użyć SQL w porządnych systemach. Tylko czy Base to strawi?
JJ
LO (7.6|24.2) ∙ Python (3.12|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
panbaryla
Posty: 50
Rejestracja: pt sty 14, 2011 11:29 am

Re: Kwerenda porównująca rekordy po dacie i nie tylko...

Post autor: panbaryla »

Wow! To jest wędka, kłaniam się nisko za pomoc! :D
Libre Office, wersja: 7.3.5.2
Debian GNU/Linux 64 bit
panbaryla
Posty: 50
Rejestracja: pt sty 14, 2011 11:29 am

Re: Kwerenda porównująca rekordy po dacie i nie tylko...

Post autor: panbaryla »

Kod: Zaznacz cały

SELECT 
    EXTRACT(YEAR FROM "data_serwisu" + INTERVAL '180 days') as "rok planowanego",
    EXTRACT(MONTH FROM "data_serwisu" + INTERVAL '180 days') as "miesiąc planowanego",
    COUNT(1)
FROM "Serwis2" 
GROUP BY EXTRACT(YEAR FROM "data_serwisu" + INTERVAL '180 days'),
                EXTRACT(MONTH FROM "data_serwisu" + INTERVAL '180 days'),
ORDER BY "rok planowanego", "miesiąc planowanego"
Wywołana w pgadminie w okienku kwerend wypluwa mi taki błąd

Kod: Zaznacz cały

ERROR:  syntax error at or near "ORDER"
LINE 8: ORDER BY "rok planowanego", "miesiąc planowanego" END
        ^

********** Błąd **********

ERROR: syntax error at or near "ORDER"
Stan SQL: 42601
Znak: 347
Libre Office, wersja: 7.3.5.2
Debian GNU/Linux 64 bit
Jan_J
Posty: 4558
Rejestracja: pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: Kwerenda porównująca rekordy po dacie i nie tylko...

Post autor: Jan_J »

przecinek przed ORDER sugeruje trzecią składową wyrażenia grupującego.
JJ
LO (7.6|24.2) ∙ Python (3.12|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
ODPOWIEDZ