Wyciągnięcie pewnej listy klientów

2015-08-14T09:20:11+02:00

Rejestracja: dni
Ostatnio: dni

0

Witam serdecznie,

Próbuję wyciągnąć z bazy danych programu Subiekt (SQL Server) listę klientów, którzy: w ciągu ostatnich 4 miesięcy nie zrobili żadnego zakupu (nie ma wystawionego dokumentu na niego), a przed 4 miesiącami wykonali ich co najmniej 3 (są wystawione przynajmniej 3 dokumenty na niego licząc od 4 miesięcy wstecz)

Stworzyłem coś takiego, ale wydaje mi się że się gubię, gdyż nie bardzo wiem jak wyciągnąć te dokumenty dla konkretnych dat:

Kopiuj

SELECT kh__Kontrahent.kh_Symbol, dok__Dokument.* FROM kh__Kontrahent 
	LEFT JOIN dok__Dokument ON kh__Kontrahent.kh_Id = dok__Dokument.dok_OdbiorcaId
		WHERE dok__Dokument.dok_Typ = 16
			AND (SELECT COUNT(kh__Kontrahent.kh_Id) FROM kh__Kontrahent 
				 LEFT JOIN dok__Dokument ON kh__Kontrahent.kh_Id = dok__Dokument.dok_OdbiorcaId 
				 WHERE dok__Dokument.dok_Typ = 16
				 AND (dok__Dokument.dok_DataWyst+10) < GETDATE()) > 0;

2015-08-14T09:38:49+02:00

Rejestracja: dni
Ostatnio: dni

0

Wywnioskowałem, że nie będę potrzebował chyba w ogóle tych podzapytań.

Kopiuj

SELECT kh__Kontrahent.kh_Symbol, dok__Dokument.* FROM kh__Kontrahent 
	LEFT JOIN dok__Dokument ON kh__Kontrahent.kh_Id = dok__Dokument.dok_OdbiorcaId
		WHERE dok__Dokument.dok_Typ = 16
			AND COUNT((dok__Dokument.dok_DataWyst+20) > GETDATE()) > 0;

Wg mnie to powinno wyciągnąć mi wszystkie zamówienia i kontrahentów z nimi związanych, którzy zrobili przynajmniej 1 zamówienie w ciągu ostatnich 20 dni. Jednak SQL mi krzyczy: Msg 102, Level 15, State 1, Line 4 Incorrect syntax near '>'.

Kopiuj


Czyżbym nie mógł użyć COUNT w WHERE?

abrakadaber

2015-08-14T09:53:20+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 6610

0

znajdujesz listę kontrahentów, którzy mają min. 3 dokumenty nie później niż 4 miesiące temu (wskazówka WHERE dok__Dokument.dok_DataWyst < GETDATE() - 4miesiace HAVING count(*) > 2)
znajdujesz kontrahentów, którzy w ostatnich 4 miesiącach nie mają dokumentów (wskazówka WHERE dok__Dokument.dok_DataWyst >= GETDATE() - 4miesiace HAVING count(*) = 0)
część wspólna 1 i 2 i masz

2015-08-14T10:05:31+02:00

Rejestracja: dni
Ostatnio: dni

0

Wielkie dzięki!

znajdujesz listę kontrahentów, którzy mają min. 3 dokumenty nie wcześniej niż 4 miesiące temu (wskazówka WHERE dok__Dokument.dok_DataWyst < GETDATE() - 4miesiace HAVING count(*) > 2)

Działa świetnie.

znajdujesz kontrahentów, którzy w ostatnich 4 miesiącach nie mają dokumentów (wskazówka WHERE dok__Dokument.dok_DataWyst >= GETDATE() - 4miesiace HAVING count(*) = 0)

Zrobiłem coś takiego:

Kopiuj

SELECT kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst FROM kh__Kontrahent 
	LEFT JOIN dok__Dokument ON kh__Kontrahent.kh_Id = dok__Dokument.dok_OdbiorcaId
		WHERE dok__Dokument.dok_Typ = 16 -- ZK
			AND dok__Dokument.dok_DataWyst >= GETDATE()-20
		GROUP BY kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst
		HAVING COUNT(*) = 0;		-- Zwraca kontrahentów, którzy w ostatnich `X` dniach nie wykonali żadnego zakupu

Jednak nie wyświetla mi żadnych klientów, a powinno, gdyż mam takich, którzy w przeciągu ostatnich 20 dni nie wykonali żadnego zakupu

2015-08-14T10:13:48+02:00

Rejestracja: dni
Ostatnio: dni

0

No i to by się zgadzało w sumie, że nie wyświetla mi w ogóle nic, ponieważ LEFT JOIN nic nie złączy, ponieważ taki kontrahent nie ma żadnego dokumentu dla takiego warunku. Jak w takim razie wyciągnąć to?

Necronus

2015-08-14T10:23:25+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 39

0

Nie rozumiesz działania zapytania

Krwawy Szczur napisał(a):

Wielkie dzięki!

Kopiuj

SELECT kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst FROM kh__Kontrahent 
	LEFT JOIN dok__Dokument ON kh__Kontrahent.kh_Id = dok__Dokument.dok_OdbiorcaId
		WHERE dok__Dokument.dok_Typ = 16 -- ZK
			AND dok__Dokument.dok_DataWyst >= GETDATE()-20  --  tutaj prosisz baze zeby ci zwróciła wszystkie dokumenty którę były zrobione za ostatnie 20 dni
		GROUP BY kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst
		HAVING COUNT(*) = 0 -- a tutaj chcesz wyswietlić wszystko co nie istnieje bezsens;

Spróbuj tak

Kopiuj

SELECT kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst FROM kh__Kontrahent 
	LEFT JOIN dok__Dokument ON kh__Kontrahent.kh_Id = dok__Dokument.dok_OdbiorcaId
		WHERE dok__Dokument.dok_Typ = 16 -- ZK
			AND dok__Dokument.dok_DataWyst <= GETDATE()-20  -
		GROUP BY kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst
		HAVING COUNT(*) > 2 ;

Tutaj bedziesz mieć kontrahentów które przed 20 dniami zrobili zakup, czyli były wystawione conajmnie 3 dokumenty dokument

2015-08-14T10:26:46+02:00

Rejestracja: dni
Ostatnio: dni

0

Necronus napisał(a):

Nie rozumiesz działania zapytania

Krwawy Szczur napisał(a):

Wielkie dzięki!

Kopiuj

SELECT kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst FROM kh__Kontrahent 
	LEFT JOIN dok__Dokument ON kh__Kontrahent.kh_Id = dok__Dokument.dok_OdbiorcaId
		WHERE dok__Dokument.dok_Typ = 16 -- ZK
			AND dok__Dokument.dok_DataWyst >= GETDATE()-20  --  tutaj prosisz baze zeby ci zwróciła wszystkie dokumenty którę były zrobione za ostatnie 20 dni
		GROUP BY kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst
		HAVING COUNT(*) = 0 -- a tutaj chcesz wyswietlić wszystko co nie istnieje bezsens;

Spróbuj tak

Kopiuj

SELECT kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst FROM kh__Kontrahent 
	LEFT JOIN dok__Dokument ON kh__Kontrahent.kh_Id = dok__Dokument.dok_OdbiorcaId
		WHERE dok__Dokument.dok_Typ = 16 -- ZK
			AND dok__Dokument.dok_DataWyst <= GETDATE()-20  -
		GROUP BY kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst
		HAVING COUNT(*) > 2 ;

Owszem owszem, ten kod, który podałeś to jest to co podał abrakadaber jako punkt 1 i tak jak napisałem działa bardzo dobrze (wygląda dokładnie tak jak podałeś). Natomiast mam problem z tym drugim zapytaniem, które powinno mi zwrócić kontrahentów, którzy przez ostatnie X dni nie zrealizowali żadnego zamówienia.

Necronus

2015-08-14T10:31:23+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 39

0

Spróbuj ten select

Kopiuj

SELECT kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst FROM kh__Kontrahent 
    LEFT JOIN dok__Dokument ON kh__Kontrahent.kh_Id = dok__Dokument.dok_OdbiorcaId
        WHERE dok__Dokument.dok_Typ = 16 -- ZK
            AND dok__Dokument.dok_DataWyst <= GETDATE()-20  
            AND KH__KONTRAHENT.KH_ID NOT IN (SELECT DOK__DOKUMENT.DOK_ODBIORCAID FROM DOK__DOKUMENT WHERE dok__Dokument.dok_DataWyst >= GETDATE()-20 )
        GROUP BY kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst
        HAVING COUNT(*) > 2 ;

2015-08-14T10:48:47+02:00

Rejestracja: dni
Ostatnio: dni

0

Necronus napisał(a):

Spróbuj ten select

Kopiuj

SELECT kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst FROM kh__Kontrahent 
    LEFT JOIN dok__Dokument ON kh__Kontrahent.kh_Id = dok__Dokument.dok_OdbiorcaId
        WHERE dok__Dokument.dok_Typ = 16 -- ZK
            AND dok__Dokument.dok_DataWyst <= GETDATE()-20  
            AND KH__KONTRAHENT.KH_ID NOT IN (SELECT DOK__DOKUMENT.DOK_ODBIORCAID FROM DOK__DOKUMENT WHERE dok__Dokument.dok_DataWyst >= GETDATE()-20 )
        GROUP BY kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst
        HAVING COUNT(*) > 2 ;

Dalej coś dziwne wyniki zwraca.

Analizując te zapytanie, to wnioskuję że przy zapytaniu:

Kopiuj

SELECT kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst FROM kh__Kontrahent 
    LEFT JOIN dok__Dokument ON kh__Kontrahent.kh_Id = dok__Dokument.dok_OdbiorcaId
        WHERE dok__Dokument.dok_Typ = 16 -- ZK
            AND KH__KONTRAHENT.KH_ID NOT IN (SELECT DOK__DOKUMENT.DOK_ODBIORCAID FROM DOK__DOKUMENT WHERE dok__Dokument.dok_DataWyst >= GETDATE() - 20 )
        GROUP BY kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst
        HAVING COUNT(*) > 0;

Powinno mi zwrócić kontrahentów, którzy nie zrealizowali zamówienia przez ostatnie 20 dni. A otrzymuję wszystkich kontrahentów, którzy zrealizowali zamówienie...

hipekk

2015-08-14T10:57:12+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 1863

0

Czemu nie posłuchasz @abrakadaber'a i nie zrobisz tak jak napisał ?
Napisz oddzielnie te dwa zapytania (pierwsze już masz - drugie jest prawie że identyczne) i ~~je odejmij~~ znajdź ich część wspólną :)

2015-08-14T10:59:02+02:00

Rejestracja: dni
Ostatnio: dni

0

hipekk napisał(a):

Czemu nie posłuchasz @abrakadaber'a i nie zrobisz ta jak napisał :) ?
Napisz oddzielnie te dwa zapytania (pierwsze już masz - drugie jest prawie że identyczne) i je odejmij :)

No stworzyłem właśnie tak jak napisał:
1.

Kopiuj

SELECT kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst FROM kh__Kontrahent 
	LEFT JOIN dok__Dokument ON kh__Kontrahent.kh_Id = dok__Dokument.dok_OdbiorcaId
		WHERE dok__Dokument.dok_Typ = 16 -- ZK
			AND dok__Dokument.dok_DataWyst < GETDATE() - 20
		GROUP BY kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst
		HAVING COUNT(*) > 2;

Kopiuj

SELECT kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst FROM kh__Kontrahent 
	LEFT JOIN dok__Dokument ON kh__Kontrahent.kh_Id = dok__Dokument.dok_OdbiorcaId
		WHERE dok__Dokument.dok_Typ = 16 -- ZK
			AND dok__Dokument.dok_DataWyst >= GETDATE() - 20
		GROUP BY kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst
		HAVING COUNT(*) = 0;

I tak jak napisałem, zapytanie nr 2 zwraca mi zawsze 0 rekordów.

Necronus

2015-08-14T11:00:16+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 39

0

Zamien left Join na Join w zapytaniu które ci podalem. Jak dalej nie pojdzie wypisz tutaj przykładowe wpisy w obu tablicach.
Bo bez danych to trochę ciężko

2015-08-14T11:09:27+02:00

Rejestracja: dni
Ostatnio: dni

0

Necronus napisał(a):

Zamien left Join na Join w zapytaniu które ci podalem. Jak dalej nie pojdzie wypisz tutaj przykładowe wpisy w obu tablicach.
Bo bez danych to trochę ciężko

Po zamianie LEFT JOIN na JOIN dalej jest źle.

Na testowej bazie mam kontrahentów:
//TEST1
TEST2
ZOO//

Dokumenty wystawione są następujące:
//2015-07-28 dla TEST1 (8 dokumentów)
2015-07-15 dla TEST1 (5 dokumentów)
2015-07-15 dla ZOO (4 dokumenty)//

W związku z powyższym powinno mi wyświetlić użytkownika ZOO jako osobę, która kupowała coś wcześniej niż 20 dni temu, a nie kupowała przez ostatnie 20 dni.

Zapytanie:

Kopiuj

SELECT kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst FROM kh__Kontrahent 
    JOIN dok__Dokument ON kh__Kontrahent.kh_Id = dok__Dokument.dok_OdbiorcaId
        WHERE dok__Dokument.dok_Typ = 16 -- ZK
            AND dok__Dokument.dok_DataWyst <= GETDATE() - 20  
            AND KH__KONTRAHENT.KH_ID NOT IN (SELECT DOK__DOKUMENT.DOK_ODBIORCAID FROM DOK__DOKUMENT WHERE dok__Dokument.dok_DataWyst >= GETDATE() - 20)
        GROUP BY kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst
        HAVING COUNT(*) > 2;

zwraca 0 rekordów.

hipekk

2015-08-14T11:12:11+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 1863

0

Zmień COUNT(*) na COUNT(dok_id).

2015-08-14T11:12:49+02:00

Rejestracja: dni
Ostatnio: dni

0

Przepraszam, wszystko działa dobrze! Teraz przeoczyłem pewien fakt, moja głupota. Zapytanie:

Kopiuj

SELECT kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst FROM kh__Kontrahent 
    JOIN dok__Dokument ON kh__Kontrahent.kh_Id = dok__Dokument.dok_OdbiorcaId
        WHERE dok__Dokument.dok_Typ = 16 -- ZK
            AND dok__Dokument.dok_DataWyst <= GETDATE() - 20  
            AND KH__KONTRAHENT.KH_ID NOT IN (SELECT DOK__DOKUMENT.DOK_ODBIORCAID FROM DOK__DOKUMENT WHERE dok__Dokument.dok_DataWyst >= GETDATE() - 20)
        GROUP BY kh__Kontrahent.kh_Symbol, dok__Dokument.dok_DataWyst
        HAVING COUNT(*) > 2;

działa idealnie :)

Necronus

2015-08-14T11:22:28+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 39

0

Krwawy Szczur napisał(a):

Przepraszam, wszystko działa dobrze! Teraz przeoczyłem pewien fakt, moja głupota.

Obstawiam że typ dokumentu był zły? :)

abrakadaber

2015-08-14T11:33:02+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 6610

0

ech

zrobili zakupy wcześniej niż x dni temu

Kopiuj

SELECT 
  k.kh_Symbol
FROM 
  kh__Kontrahent k
LEFT JOIN dok__Dokument d 
  ON k.kh_Id = d.dok_OdbiorcaId 
  AND d.dok_Typ = 16 --WAŻNE musisz warunek dać w JOIN bo NAJPIERW musisz zawęzić listę dokumentów do tych sprzed x dni
  AND d.dok_DataWyst < GETDATE()-20
GROUP BY 
  k.kh_Symbol
HAVING 
  Count(d.dok_DataWyst) > 2 --nie COUNT(*) tylko COUNT(cokolwiek z tabeli dokumentów) bo liczysz ilość dokumentów a nie czegokolwiek

nie zrobili żadnych zakupów w ciągu ostatnich x dni

Kopiuj

SELECT 
  k.kh_Symbol
FROM 
  kh__Kontrahent k
LEFT JOIN dok__Dokument d 
  ON k.kh_Id = d.dok_OdbiorcaId 
  AND d.dok_Typ = 16 --WAŻNE musisz warunek dać w JOIN bo NAJPIERW musisz zawęzić listę dokumentów do tych sprzed x dni
  AND d.dok_DataWyst >= GETDATE()-20
GROUP BY 
  k.kh_Symbol
HAVING 
  Count(d.dok_DataWyst) = 0 --nie COUNT(*) tylko COUNT(cokolwiek z tabeli dokumentów) bo liczysz ilość dokumentów a nie czegokolwiek

alternatywnie można

Kopiuj

SELECT 
  k.kh_Symbol
FROM 
  kh__Kontrahent k
LEFT JOIN dok__Dokument d 
  ON k.kh_Id = d.dok_OdbiorcaId 
  AND d.dok_Typ = 16 --WAŻNE musisz warunek dać w JOIN bo NAJPIERW musisz zawęzić listę dokumentów do tych sprzed x dni
  AND d.dok_DataWyst >= GETDATE()-20
WHERE
  d.dok_OdbiorcaId IS NULL --cokolwiek z tabeli dokumentów is null

wynik

Kopiuj

SELECT
  a.kh_symbol 
FROM
  (SELECT 
    k.kh_Symbol
  FROM 
    kh__Kontrahent k
  LEFT JOIN dok__Dokument d 
    ON k.kh_Id = d.dok_OdbiorcaId 
    AND d.dok_Typ = 16 
    AND d.dok_DataWyst < GETDATE()-20
  GROUP BY 
    k.kh_Symbol
  HAVING 
    Count(d.dok_DataWyst) > 2) a
JOIN (
  SELECT 
    k.kh_Symbol
  FROM 
    kh__Kontrahent k
  LEFT JOIN dok__Dokument d 
    ON k.kh_Id = d.dok_OdbiorcaId 
    AND d.dok_Typ = 16 --WAŻNE musisz warunek dać w JOIN bo NAJPIERW musisz zawęzić listę dokumentów do tych sprzed x dni
    AND d.dok_DataWyst >= GETDATE()-20
  WHERE
    d.dok_OdbiorcaId IS NULL) b 
  ON a.kh_Symbol = b.kh_Symbol

Pewnie dało by się bez podzapytań ale nie chce mi się myśleć :p

Właśnie widzę, że całkowicie niepotrzebnie jest łączone z kontrahentami w pierwszym zapytaniu

hipekk

2015-08-14T11:52:40+02:00

Rejestracja: dni
Ostatnio: dni
Postów: 1863

0

Warto dołączyć jeszcze tabelę adr__Ewid, więc uzupełniając zapytanie @abrakadaber powstanie np. coś takiego :

Kopiuj

SELECT
  a.kh_symbol [SYMBOL KH], adr.adr_Nazwa [NAZWA KH]
FROM
  (SELECT 
    k.kh_Symbol, k.kh_Id
  FROM 
    kh__Kontrahent k
  LEFT JOIN dok__Dokument d 
    ON k.kh_Id = d.dok_OdbiorcaId 
    AND d.dok_Typ = 16 
    AND d.dok_DataWyst < GETDATE()-20
    GROUP BY 
    k.kh_Symbol, k.kh_Id
  HAVING 
    COUNT(d.dok_DataWyst) > 2
  ) a
JOIN (
  SELECT 
    k.kh_Symbol
  FROM 
    kh__Kontrahent k
  LEFT JOIN dok__Dokument d 
    ON k.kh_Id = d.dok_OdbiorcaId 
    AND d.dok_Typ = 16 --WAŻNE musisz warunek dać w JOIN bo NAJPIERW musisz zawęzić listę dokumentów do tych sprzed x dni
    AND d.dok_DataWyst >= GETDATE()-20
  WHERE
    d.dok_OdbiorcaId IS NULL) b     
  ON a.kh_Symbol = b.kh_Symbol
LEFT JOIN adr__Ewid adr ON a.kh_Id=adr.adr_IdObiektu and adr_TypAdresu = 1

2015-08-14T12:18:44+02:00

Rejestracja: dni
Ostatnio: dni

0

Świetnie, bardzo Wam dziękuję dokładnie o to mi chodziło :) Jeszcze wiele nauki przede mną :)