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

Użytkowanie programu bazodanowego

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

Postprzez panbaryla » Pt gru 02, 2011 10:58 am

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.
LibreOffice Wersja: 5.3.4.2
Build ID: f82d347ccc0be322489bf7da61d7e4ad13fe2ff3
Wątki CPU: 1; Wersja OS: Linux 4.9; UI Render: domyślny; VCL: gtk2; Układ silnika:nowy;
Ustawienia regionalne: pl-PL (pl_PL.UTF-8); Calc: group
Debian Jessie 64 bit
panbaryla
 
Posty: 44
Dołączył(a): Pt sty 14, 2011 11:29 am

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

Postprzez Jan_J » Pt gru 02, 2011 3:13 pm

Najprostszy filtr
Kod: Zaznacz cały   Rozszerz widokZwiń widok
select * from Serwis where data_serwisu = data_planowanego;

Z ograniczeniem do serwisów planowanych w bieżącym miesiącu
Kod: Zaznacz cały   Rozszerz widokZwiń widok
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   Rozszerz widokZwiń widok
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   Rozszerz widokZwiń widok
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 5.4 ∙ AOO 4.1.4 ∙ Python (3.6|2.7) ∙ Unicode 10 ∙ LATEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Fedora|CentOS|SUSE)
Jan_J
 
Posty: 3441
Dołączył(a): Pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

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

Postprzez panbaryla » Wt gru 06, 2011 8:23 pm

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ą...
LibreOffice Wersja: 5.3.4.2
Build ID: f82d347ccc0be322489bf7da61d7e4ad13fe2ff3
Wątki CPU: 1; Wersja OS: Linux 4.9; UI Render: domyślny; VCL: gtk2; Układ silnika:nowy;
Ustawienia regionalne: pl-PL (pl_PL.UTF-8); Calc: group
Debian Jessie 64 bit
panbaryla
 
Posty: 44
Dołączył(a): Pt sty 14, 2011 11:29 am

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

Postprzez Jan_J » Wt gru 06, 2011 8:36 pm

panbaryla napisał(a):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   Rozszerz widokZwiń widok
... data_serwisu <> data_planowanego ...
JJ
LO 5.4 ∙ AOO 4.1.4 ∙ Python (3.6|2.7) ∙ Unicode 10 ∙ LATEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Fedora|CentOS|SUSE)
Jan_J
 
Posty: 3441
Dołączył(a): Pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

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

Postprzez panbaryla » Wt gru 06, 2011 9:03 pm

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   Rozszerz widokZwiń widok
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
LibreOffice Wersja: 5.3.4.2
Build ID: f82d347ccc0be322489bf7da61d7e4ad13fe2ff3
Wątki CPU: 1; Wersja OS: Linux 4.9; UI Render: domyślny; VCL: gtk2; Układ silnika:nowy;
Ustawienia regionalne: pl-PL (pl_PL.UTF-8); Calc: group
Debian Jessie 64 bit
panbaryla
 
Posty: 44
Dołączył(a): Pt sty 14, 2011 11:29 am

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

Postprzez panbaryla » Pn wrz 03, 2012 1:35 pm

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

Wyrzeźbiłem sobie taką
Kod: Zaznacz cały   Rozszerz widokZwiń widok
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?
LibreOffice Wersja: 5.3.4.2
Build ID: f82d347ccc0be322489bf7da61d7e4ad13fe2ff3
Wątki CPU: 1; Wersja OS: Linux 4.9; UI Render: domyślny; VCL: gtk2; Układ silnika:nowy;
Ustawienia regionalne: pl-PL (pl_PL.UTF-8); Calc: group
Debian Jessie 64 bit
panbaryla
 
Posty: 44
Dołączył(a): Pt sty 14, 2011 11:29 am

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

Postprzez panbaryla » Wt wrz 04, 2012 1:39 pm

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

Kod: Zaznacz cały   Rozszerz widokZwiń widok
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
LibreOffice Wersja: 5.3.4.2
Build ID: f82d347ccc0be322489bf7da61d7e4ad13fe2ff3
Wątki CPU: 1; Wersja OS: Linux 4.9; UI Render: domyślny; VCL: gtk2; Układ silnika:nowy;
Ustawienia regionalne: pl-PL (pl_PL.UTF-8); Calc: group
Debian Jessie 64 bit
panbaryla
 
Posty: 44
Dołączył(a): Pt sty 14, 2011 11:29 am

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

Postprzez panbaryla » Śr cze 28, 2017 11:54 am

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   Rozszerz widokZwiń widok
select * from "Serwis2" where "data_serwisu" DATE_ADD("data_serwisu",INTERVAL 180 DAY) AS data planowanego;


Ale niestety coś knocę...
LibreOffice Wersja: 5.3.4.2
Build ID: f82d347ccc0be322489bf7da61d7e4ad13fe2ff3
Wątki CPU: 1; Wersja OS: Linux 4.9; UI Render: domyślny; VCL: gtk2; Układ silnika:nowy;
Ustawienia regionalne: pl-PL (pl_PL.UTF-8); Calc: group
Debian Jessie 64 bit
panbaryla
 
Posty: 44
Dołączył(a): Pt sty 14, 2011 11:29 am

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

Postprzez Jan_J » Śr cze 28, 2017 3:39 pm

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   Rozszerz widokZwiń widok
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 5.4 ∙ AOO 4.1.4 ∙ Python (3.6|2.7) ∙ Unicode 10 ∙ LATEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Fedora|CentOS|SUSE)
Jan_J
 
Posty: 3441
Dołączył(a): Pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

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

Postprzez panbaryla » Śr cze 28, 2017 6:54 pm

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)
LibreOffice Wersja: 5.3.4.2
Build ID: f82d347ccc0be322489bf7da61d7e4ad13fe2ff3
Wątki CPU: 1; Wersja OS: Linux 4.9; UI Render: domyślny; VCL: gtk2; Układ silnika:nowy;
Ustawienia regionalne: pl-PL (pl_PL.UTF-8); Calc: group
Debian Jessie 64 bit
panbaryla
 
Posty: 44
Dołączył(a): Pt sty 14, 2011 11:29 am

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

Postprzez panbaryla » Cz paź 05, 2017 2:48 pm

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

Kod: Zaznacz cały   Rozszerz widokZwiń widok
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?
LibreOffice Wersja: 5.3.4.2
Build ID: f82d347ccc0be322489bf7da61d7e4ad13fe2ff3
Wątki CPU: 1; Wersja OS: Linux 4.9; UI Render: domyślny; VCL: gtk2; Układ silnika:nowy;
Ustawienia regionalne: pl-PL (pl_PL.UTF-8); Calc: group
Debian Jessie 64 bit
panbaryla
 
Posty: 44
Dołączył(a): Pt sty 14, 2011 11:29 am

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

Postprzez Jan_J » Cz paź 05, 2017 10:09 pm

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

czyli w Twoim przypadku
Kod: Zaznacz cały   Rozszerz widokZwiń widok
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   Rozszerz widokZwiń widok
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   Rozszerz widokZwiń widok
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   Rozszerz widokZwiń widok
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 5.4 ∙ AOO 4.1.4 ∙ Python (3.6|2.7) ∙ Unicode 10 ∙ LATEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Fedora|CentOS|SUSE)
Jan_J
 
Posty: 3441
Dołączył(a): Pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

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

Postprzez panbaryla » Pt paź 06, 2017 9:50 am

Wow! To jest wędka, kłaniam się nisko za pomoc! :D
LibreOffice Wersja: 5.3.4.2
Build ID: f82d347ccc0be322489bf7da61d7e4ad13fe2ff3
Wątki CPU: 1; Wersja OS: Linux 4.9; UI Render: domyślny; VCL: gtk2; Układ silnika:nowy;
Ustawienia regionalne: pl-PL (pl_PL.UTF-8); Calc: group
Debian Jessie 64 bit
panbaryla
 
Posty: 44
Dołączył(a): Pt sty 14, 2011 11:29 am

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

Postprzez panbaryla » Pt paź 06, 2017 4:19 pm

Kod: Zaznacz cały   Rozszerz widokZwiń widok
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   Rozszerz widokZwiń widok
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
LibreOffice Wersja: 5.3.4.2
Build ID: f82d347ccc0be322489bf7da61d7e4ad13fe2ff3
Wątki CPU: 1; Wersja OS: Linux 4.9; UI Render: domyślny; VCL: gtk2; Układ silnika:nowy;
Ustawienia regionalne: pl-PL (pl_PL.UTF-8); Calc: group
Debian Jessie 64 bit
panbaryla
 
Posty: 44
Dołączył(a): Pt sty 14, 2011 11:29 am

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

Postprzez Jan_J » Pt paź 06, 2017 7:48 pm

przecinek przed ORDER sugeruje trzecią składową wyrażenia grupującego.
JJ
LO 5.4 ∙ AOO 4.1.4 ∙ Python (3.6|2.7) ∙ Unicode 10 ∙ LATEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Fedora|CentOS|SUSE)
Jan_J
 
Posty: 3441
Dołączył(a): Pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław


Powrót do Base

Kto przegląda forum

Użytkownicy przeglądający ten dział: Brak zidentyfikowanych użytkowników i 2 gości