push I have a query in firbird sql, which shows me the last jobs from the employees. It could be possible, that a job is shown multiple, because the employee could be often by one customer.
I have a query like:
**employee** - **from** - **till** - **Customer**
Ronaldo - 2017-01-01 - 2017-03-31 - Real Madrid
Ronaldo - 2017-04-01 - 2017-05-20 - Real Madrid
Ronaldo - 2017-05-25 - 2017-06-10 - Barcelona
Ronaldo - 2017-06-13 - 2017-12-31 - Real Madrid
Now, my result should be like that:
Ronaldo - 2017-01-01 - 2017-05-20 - Real Madrid
Ronaldo - 2017-05-25 - 2017-06-10 - Barcelona
That means, I do not want to have the row which is up to date and related rows combine with the first from and the last till, if the next from is not older then 7 days from the first till - otherwhise I want to have shown both.
My SQL now is:
SELECT *
FROM
(
WITH TB_LA as
(
SELECT EP.PERSONALNR, max(EP.DATUMVON) as DATUMVON
FROM EINSPERS EP, AUFTRAG A
WHERE EP.PERSONALNR = 10337 AND EP.BERUFSCHL is not null AND EP.DATUMVON
, TB_A as
(
SELECT EP.PERSONALNR, EP.AUFTRAGNR, EP.DATUMVON, EP.DATUMBIS, K.KUNDENNR, 1 as PARM
FROM EINSPERS EP, TB_LA, AUFTRAG A, KUNDEN K
WHERE EP.BERUFSCHL is not null AND EP.DATUMVON <= TIMESTAMP 'TODAY' AND TB_LA.PERSONALNR = EP.PERSONALNR AND TB_LA.DATUMVON = EP.DATUMVON AND EP.AUFTRAGNR = A.AUFTRAGNR AND A.KUNDENNR = K.KUNDENNR
)
, TB_H as
(
SELECT EP.PERSONALNR, EP.AUFTRAGNR, EP.DATUMVON, EP.DATUMBIS, K.KUNDENNR, K.KUNDNAME, K.DISPONENT as DISPOKU, P.DISPONENT AS DISPOMA
FROM EINSPERS EP
INNER JOIN AUFTRAG A ON EP.AUFTRAGNR = A.AUFTRAGNR
INNER JOIN KUNDEN K ON A.KUNDENNR = K.KUNDENNR
INNER JOIN TB_LA ON TB_LA.PERSONALNR = EP.PERSONALNR
INNER JOIN PERSONAL P ON P.PERSONALNR = EP.PERSONALNR
LEFT JOIN TB_A ON TB_A.PERSONALNR = EP.PERSONALNR AND TB_A.KUNDENNR = K.KUNDENNR AND (TB_A.DATUMVON >= (EP.DATUMBIS-14) or TB_A.DATUMVON = EP.DATUMVON)
WHERE EP.BERUFSCHL is not null AND EP.DATUMVON <= TIMESTAMP 'TODAY' AND EP.DATUMBIS >= TIMESTAMP '2017-01-31' AND CASE WHEN K.KUNDENNR = TB_A.KUNDENNR THEN 1 ELSE NULL END is null AND TB_A.PARM is null
)
SELECT * FROM TB_H
)
my result is:
employee from till customer 10337 30.01.2017 31.01.2017 120495 10337 14.02.2017 19.02.2017 117769 10337 20.02.2017 20.02.2017 127507 10337 21.02.2017 05.03.2017 127507 10337 06.03.2017 12.03.2017 117769
and at least I want to have the following result:
employee from till customer 10337 30.01.2017 31.01.2017 120495 10337 14.02.2017 19.02.2017 117769 10337 20.02.2017 05.03.2017 127507 10337 06.03.2017 12.03.2017 117769
How can I do the last step? I have no idea to solve my problem :-(
add: it is possible to do this in ms sql or firebird sql, no matter which way, but I prefer firebird sql.
thank you!
