Witam
W pierwszej kolejności opisze kontekst.
Próbuje napisać zapytanie które ma sprawdzić wiele warunków w tabeli względem tych samych kolumn i jeśli jakaś "grupa" warunków jest spełnione to jako wynik mam otrzymać określone zapytanie SELECT. Jeśli zaś warunki nie są spełnione to wyniku nie ma.
Generalnie tabela zawiera komunikaty przychodzące z innego ERP. Każdy komunikat ma swój typ i status w danym momencie. Niestety czasami na skutek problemów w integracji między bazami przetwarzanie komunikatów zatrzymuje się. Zapytanie będzie monitorować tą tabelę. Jeśli zwróci wynik oznacza to że jest problem.
Próbowałem już dwóch różnych sposobów na napisania zapytania i w każdym utknąłem na innym etapie. Być może temat jest prosty, tylko ja sobie go za bardzo skomplikowałem.
A więc do rzeczy.
Struktura tabeli:
SELECT TOP (25)
[MESSAGE_ID]
,[MESSAGE_TYPE]
,[SENDER]
,[RECEIVER]
,[CREATED]
,[STATUS]
,[ERR_MSG]
FROM ERP_2_IN_MESSAGE_TAB
Warunki do spełnia żeby dostać wynik:
((Do przetworzenia (STATUS = 1) jest więcej niż 150 komunikatów) i (w trakcie przetwarzania (STATUS = 2) nie ma nic (ilość = 0) lub przetwarzany komunikat jest inny niż 'CUST3ZAM') i (data ostatniego poprawnie przetworzonego komunikatu (STATUS = 3) jest starsza niż 15 minut wstecz))
lub
((Do przetworzenia (STATUS = 1) nie ma nic - tutaj nie liczymy w tym statusie MESSAGE_TYPE = 'CUST5ZAK') i (data ostatniego poprawnie przetworzonego komunikatu jest starsza niż 15 minut wstecz))
lub
((W trakcie przetwarzania (STATUS = 2) jest więcej niż 150 komunikatów - tutaj nie liczymy w tym statusie MESSAGE_TYPE = 'CUST7ZAM') i (data ostatniego poprawnie przetworzonego komunikatu jest starsza niż 15 minut wstecz))
lub
(Jeżeli jest błąd (STATUS = 4) ERR_MSG = 'Nieudany import komunikatu do bazy - timeout.')
PIERWSZY SPOSÓB - podzapytania
Jako wynik zapytania po spełnieniu opisanych wyżej warunków chciałbym dostać ilości komunikatów w danym statusie z ostatnich 30 minut czyli coś takiego:
SELECT
case E2I.[STATUS]
when 1 then '1 - DO PRZETWORZENIA'
when 2 then '2 - W TRAKCIE'
when 3 then '3 - OK'
when 4 then '4 - BŁĄD'
end as 'STATUSY',
count (E2I.MESSAGE_ID) as 'ILOSC_KOMUNIKATOW'
FROM ERP_2_IN_MESSAGE_TAB as E2I with (nolock)
where
E2I.CREATED BETWEEN DATEADD(MINUTE,-30,GETDATE()) AND GETDATE()
and E2I.[STATUS] in (1,2,3,4)
and E2I.MESSAGE_TYPE != 'CUST2ZAM'
group by E2I.[STATUS]
Pomysł był taki żeby warunki ułożyć z podzapytań i ostatecznie jeśli któryś się sprawdzi to SELECT da jako wynik 1.
Dla trzech pierwszych warunków wyszedł mi taki twór:
SELECT T1 from
(SELECT T1 = CASE WHEN [S1] > 150 and ([S2] = 0 or S2C != 0) and [W2] < DATEADD(MINUTE,-30,GETDATE()) THEN 1 ELSE 0 END
FROM
(
select
COUNT(CASE WHEN IIMT2.[STATUS] = 1 THEN 1 END ) AS S1,
COUNT(CASE WHEN IIMT2.[STATUS] = 2 THEN 1 END ) AS S2,
COUNT(CASE WHEN IIMT2.[STATUS] = 2 and IIMT2.MESSAGE_TYPE = 'CUST3ZAM' THEN 1 END ) AS S2C,
(
SELECT TOP (1) IIMT1.CREATED FROM ERP_2_IN_MESSAGE_TAB as IIMT1 with (nolock)
where IIMT1.[STATUS] = 2
order by IIMT1.CREATED desc
) AS W2
FROM ERP_2_IN_MESSAGE_TAB as IIMT2 with (nolock)
where
IIMT2.CREATED BETWEEN DATEADD(MINUTE,-30,GETDATE()) AND GETDATE()
) AS Y1) AS Z1
WHERE T1 = 1
UNION
SELECT T1 from
(SELECT T1 = CASE WHEN [S1] = 0 and [W2] < DATEADD(MINUTE,-30,GETDATE()) THEN 1 ELSE 0 END
FROM
(
select
COUNT(CASE WHEN IIMT2.[STATUS] = 1 THEN 1 END ) AS S1,
(
SELECT TOP (1) IIMT1.CREATED FROM ERP_2_IN_MESSAGE_TAB as IIMT1 with (nolock)
where IIMT1.[STATUS] = 2
order by IIMT1.CREATED desc
) AS W2
FROM ERP_2_IN_MESSAGE_TAB as IIMT2 with (nolock)
where
IIMT2.CREATED BETWEEN DATEADD(MINUTE,-30,GETDATE()) AND GETDATE()
and IIMT1.MESSAGE_TYPE != 'CUST5ZAK'
) AS Y2) AS Z2
WHERE T1 = 1
UNION
SELECT T1 from
(SELECT T1 = CASE WHEN [S2] > 150 and [W2] < DATEADD(MINUTE,-30,GETDATE()) THEN 1 ELSE 0 END
FROM
(
select
COUNT(CASE WHEN IIMT2.[STATUS] = 2 THEN 1 END ) AS S2,
(
SELECT TOP (1) IIMT1.CREATED FROM ERP_2_IN_MESSAGE_TAB as IIMT1 with (nolock)
where IIMT1.[STATUS] = 2
order by IIMT1.CREATED desc
) AS W2
FROM ERP_2_IN_MESSAGE_TAB as IIMT2 with (nolock)
where
IIMT2.CREATED BETWEEN DATEADD(MINUTE,-30,GETDATE()) AND GETDATE()
and IIMT1.MESSAGE_TYPE != 'CUST7ZAM'
) AS Y3) AS Z3
WHERE T1 = 1
Zapytanie działa, ale nie mam pomysłu jak połączyć to w całość z pierwszym selectem gdzie dostaje podsumowanie ilości komunikatów za ostanie 30 minut. Myślałem o czymś w stylu że jeśli drugie zapytanie da 1 to wykona się pierwszy select z podsumowaniem. Jeśli nie ma wyników nic się nie dalej nie wykonuje.
DRUGI SPOSÓB - filtrowanie po grupach
Teoretycznie proste zapytanie dające mi podsumowanie pogrupowane po statusach, rodzajach komunikatu i ilościach oraz dacie ostatniego przetworzonego komunikatu, więc myślałem że warunki zawarte w HAVING załatwią sprawę.
Zacząłem testować na prostych warunkach i działa niestety tylko gdy pomiędzy warunkami jest OR, a nie potrzebny mi AND.
W przykładzie poniżej chciałem zobaczyć podsumowanie gdy ilość komunikatów do przetworzenia jest równa lub większa niż 1 i ilość poprawnie przetworzonych komunikatów jest równa lub większa niż 10 i typ poprawnie przetworzonych komunikatów to 'CUST9ZAM'. Tak jak pisałem wyżej gdy w HAVING jest OR między warunkami co oczywiste dostaje wynik gdy oba warunki zaistnieją, ale w przypadku gdy OR zamienię na AND i warunki są spełnione nie mam wyników.
SELECT
case IIMT.[STATUS]
when 1 then '1 - DO PRZETWORZENIA'
when 2 then '2 - W TRAKCIE'
when 3 then '3 - OK'
when 4 then '4 - BLAD'
end as 'STATUSY',
count (IIMT.MESSAGE_ID) as 'ILOSC_KOMUNIKATOW',
IIMT.MESSAGE_TYPE as 'RODZAJ_KOMUNIKATU',
MAX(IIMT.CREATED) as 'OSTATNIA_DATA_UTWORZENIA'
FROM ERP_2_IN_MESSAGE_TAB as IIMT with (nolock)
where
IIMT.CREATED BETWEEN DATEADD(HOUR,-1,GETDATE()) AND GETDATE()
and IIMT.[STATUS] in (1,2,3,4)
group by IIMT.[STATUS], IIMT.MESSAGE_TYPE
having
(IIMT.[STATUS] = 1 and count(IIMT.MESSAGE_ID) >= 1)
and
(IIMT.[STATUS] = 3 and IIMT.MESSAGE_TYPE = 'CUST9ZAM' and count(IIMT.MESSAGE_ID) >= 10)
Niestety nie mam już pomysłów jak podejść do tego tematu, a spędza mi on sen z powiek.
Z góry dziękuje za jakieś wskazówki, podpowiedzi, lub gdzie należy coś poprawić żeby można było zbudować działające zapytanie.