Selezionare ultimo record di una sotto query

[stefanoxjx]

Ciao a tutti,
ho un problema dal quale non riesco a venirne fuori nonostante svariate prove.
Ho una tabella "Anagrafica" e una tabella "Assunzioni" dalle quali riesco a ricavarmi i dati anagrafici del dipendente e se lo stesso è attualmente assunto o dimesso.
La tabella "Assunzioni" può avere più righe e nello specifico una per ogni nuovo contratto o assunzione.
Per esempio:

1	18/06/2019	Tempo determinato	18/07/2019
2	25/09/2020	Tempo indeterminato	NULL

Quindi riesco a capire se il dipendente è ancora operativo o dimesso dal fatto che ci sia oppure non la data di dimissione nel record con id più alto.
A tale scopo ho preparato una query che sarebbe questa:

SELECT * FROM Dipendenti d WHERE (Cognome LIKE '%%' OR Nome LIKE '%%')
AND Cooperative_Id = 1
AND if((SELECT max(id) From assunzioni a where a.dipendenti_id = d.id AND a.datadimissione IS not NULL LIMIT 1), 0, 1)
ORDER BY Cognome

ma la sotto query (quella interna alla if per capirci) funziona bene solo se nella tabella "Assunzioni" c'è solo una riga inerente il Dipendente.id, ma nel caso specificato sopra il record che ritorna è quello con la data di dimissione e quindi mi trovo con un risultato che non è quello aspettato.
Ho provato con GROUP BY, con ORDER BY, con LIMIT ma non riesco a fare in modo che mi ritorni il record corretto.
Chiedo quindi aiuto altrimenti ci perdo altri sei mesi
Grazie.

 

[marino51]

la query, restituisce
datadimissione = null per i dipendenti attivi
oppure la data di chiusura del rapporto

se ti garba, questa é la query

SELECT
  x.dipendenti_id
, CONVERT(varchar, x.dataassunzione, 23) AS dataassunzione
, d.cognome
, d.nome
, CONVERT(varchar, a.datadimissione, 23) AS datadimissione
FROM
(
SELECT
  dipendenti_id
, max(dataassunzione) AS dataassunzione
FROM assunzioni
GROUP BY dipendenti_id
) x
LEFT JOIN dipendenti d ON x.dipendenti_id = d.id
LEFT JOIN assunzioni a ON a.dipendenti_id = d.id AND a.dataassunzione = x.dataassunzione
WHERE ( d.cognome LIKE '%gno%' OR d.nome LIKE '%nom%' )
AND d.Cooperative_Id = 1
ORDER BY d.cognome, d.nome

ps, i "convert" mi sono serviti solo per formattare la data, puoi evitarli o usare quelli propri di mySQL

 

[stefanoxjx]

Ti ringrazio per la query, ma purtroppo non funziona.
Direi di semplificare la cosa, altrimenti ho anche difficoltà a spiegare il risultato che mi servirebbe avere.
Ammettendo di avere una tabella come questa:

id	Dipendenti_id	data_assunzione	data_dimissione
1	3	18/06/2020	31/12/2020
2	6	20/06/2020	NULL
3	7	20/09/2020	31/12/2020
4	4	30/09/2020	30/11/2020
5	3	01/02/2021	30/06/2021
6	3	01/09/2021	NULL
7	7	04/07/2021	NULL
8	9	18/07/2021	NULL
9	11	20/07/2021	NULL

Guardando la colonna Dipendenti_id, i dipendenti che hanno uno storico sono quelli con id 3 e 7 e sono quelli che mi complicano la vita.
Dovrei tirare fuori una query che nel caso specifico sopra mi dia come risultato solo i dimessi alla data attuale, quindi nel caso specifico solo il dipendente con ID 4.
Questo perchè l'id 3 è stato assunto e dimesso due volte, ma alla riga 6 è stato assunto e attualmente lo è ancora, il 7 è stato assunto e dimesso alla riga 3 ma poi riassunto alla riga 7 e così via.
Spero di essermi spiegato.
Grazie.

Stefano

 

[marino51]

Ti ringrazio per la query, ma purtroppo non funziona

potremmo forse dire che non l'hai capita,

con l'ultima tabella pubblicata la query estrae un risultato consono,

se l'avessi capita, avresti aggiunto

AND a.datadimissione is not null

nella clausola where prima dell' "order by", ottenendo

cosi come togliendo il "NOT" ottieni tutti i dipendenti con contratto in essere

se cerchi qualcosa di più semplice, non sono riuscito a fare meglio,
forse qualcuno più capace può suggerirti una query "unica" più semplice

 

[marino51]

considera anche il formato delle date che usi,

nel db dovrebbero essere dichiarate "date" o "datetime"

poi,
la data 01/02/2021 in "lingua" americana esprime 2 gennaio 2021

la stessa data 01/02/2021 in "lingua" italiana esprime 1 febbraio 2021

quindi come stai usando le date ?

 

[marino51]

d' altra parte non v'é ombra di dubbio che la query più interna estragga la data più alta per ciascun dipendente

la query lavora soprattutto in modo consistente, estraendo le date più alte NON l' ID del record perchè
i contratti potrebbero essere inseriti in ordine casuale e quindi l'ID può non esprime l'ultimo inserimento "necessario"
quello con data più alta

ps la chiocciola che precede il nome della tabella, identifica una tabella temporanea, usata per la prova della query

 

[stefanoxjx]

Non c'è ombra di dubbio sul fatto che non abbia capito bene la query, non sono ancora arrivato a questi livelli di complessità.
Comunque ci sono delle cose che non mi tornano.
Come prima cosa ho dovuto modificare la query come segue:

SELECT x.dipendenti_id, x.dataassunzione AS dataassunzione, d.cognome, d.nome, a.datadimissione AS datadimissione
FROM
(SELECT dipendenti_id, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id) x
LEFT JOIN dipendenti d ON x.dipendenti_id = d.id
LEFT JOIN assunzioni a ON a.dipendenti_id = d.id AND a.dataassunzione = x.dataassunzione
WHERE (d.cognome LIKE '%%' OR d.nome LIKE '%%')
AND d.Cooperative_Id = 1 AND a.datadimissione is null
ORDER BY d.cognome, d.nome

per quanto riguarda le date, le gestisco come MySQL vuole e i campi dataassunzione e datadimissione sono di tipo date.
Nella tabella che ho postato in precedenza, avevo messo un formato più semplice da leggere per noi umani

Ho allegato a questo post la tabella sulla quale sto facendo le prove, così magari perdi meno tempo anche tu a verificare.
Eseguendo la query nella tabella allegata, con il IS NOT NULL dovrei avere come risultato i Dipendenti_id: 3, 185, 275, 292.
Invece ho come risultato solo 3 e 185.

Se invece eseguo la query con IS NULL, ho come risultato 1 e 32 e mancano tutti gli altri che hanno data dimissione == NULL.
Inoltre, ma qui devo dire che sono io a non averlo specificato, può esserci il caso in cui sulla tabella Dipendenti ci siano dei dati anagrafici e che però sulla tabella Assunzioni non sia ancora stato inserito nulla inerente ad un determinato dipendente.
In questo caso avrei bisogno che nella lista apparissero anche queste anagrafiche, sia con IS NOT NULL che con IS NULL.

Per questo ti avevo detto che forse era meglio semplificare, perchè vorrei evitare di farti perdere un sacco di tempo.
Grazie.

 

[marino51]

Dipendenti_id: 3, 185, 275, 292.
Invece ho come risultato solo 3 e 185.

sto controllando la mia query che sembra rispondere bene

 

[marino51]

Se invece eseguo la query con IS NULL, ho come risultato 1 e 32 e mancano tutti gli altri che hanno data dimissione == NULL.

sto sempre controllando la mia query, che anche nel secondo caso sembra rispondere bene

 

[marino51]

tabella Dipendenti ci siano dei dati anagrafici e che però sulla tabella Assunzioni non sia ancora stato inserito nulla inerente ad un determinato dipendente.

ok questo lo avevo pensato, per ottenere questo é necessario aggiungere in "UNION" una seconda query che estrae i dipendenti non esistenti in assunzioni

ho aggiunto una anagrafica e testato la qury relativa che sembra funzionare

bisogna però sapere quali dati estrarre perchè questa query deve "combaciare" in termini di colonne e tipo dati alla precedente

da ultimo,

Per questo ti avevo detto che forse era meglio semplificare

credo sia il modo più semplice possibile


ora controllo la query da te modificata per cercare di capire come mai perde dei record

poi integro la nuova query nella precedente e tutto dovrebbe essere fatto

 

[marino51]

insomma qualcosa tipo così (nel caso non assunti)

ti può andare ?

 

[marino51]

rivista in base alle ultime considerazioni, ti posto la query ed un suo risultato (where non commentata)
ho esposto la colonna "cooperative_id" perché necessaria per una coerenza dell'insieme

SELECT * FROM (
SELECT x.dipendenti_id, d.Cooperative_Id, x.dataassunzione, d.cognome, d.nome, a.datadimissione
FROM
( SELECT dipendenti_id, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id ) x
LEFT JOIN dipendenti d ON x.dipendenti_id = d.id
LEFT JOIN assunzioni a ON a.dipendenti_id = d.id AND a.dataassunzione = x.dataassunzione
WHERE a.datadimissione is not null
-- WHERE a.datadimissione is null
UNION ALL
SELECT d.id AS dipendenti_id, d.Cooperative_Id, null AS dataassunzione, d.cognome, d.nome, null AS datadimissione
FROM dipendenti d
LEFT JOIN assunzioni a ON d.id = a.dipendenti_id
WHERE a.dipendenti_id is null
) y
WHERE ( cognome LIKE '%%' OR nome LIKE '%%' )
AND Cooperative_Id = 1
ORDER BY cognome, nome

ti trovi ?

 

[stefanoxjx]

Scusa se non ti ho ancora risposto, ma ho dei comportamenti diversi da quelli che dici e sto cercando di capire cosa sta succedendo.
Che possa dipendere dal fatto che uso MariaDB e non MySQL e sto dando troppo per scontato che sono la stessa cosa?

 

[marino51]

la query che ti ho suggerito é abbastanza normale per funzionare con qualunque tipo di database,

ho capito che vengono esclusi dalla selezione alcuni elementi,
hai controllato i valori che vengono filtrati dalla

AND Cooperative_Id = 1

può essere che non tutti abbiano valore 1 ?

come secondo step, controlla che

SELECT dipendenti_id, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id

estragga i record correttamente, ovvero le date siano definite correttamente

il terzo step estrae elementi combinati dalle 3 tabelle (temporanea vedi step 2 + dipendenti + assunzioni

SELECT x.dipendenti_id, d.Cooperative_Id, x.dataassunzione, d.cognome, d.nome, a.datadimissione
FROM
( SELECT dipendenti_id, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id ) x
LEFT JOIN dipendenti d ON x.dipendenti_id = d.id
LEFT JOIN assunzioni a ON a.dipendenti_id = d.id AND a.dataassunzione = x.dataassunzione
WHERE a.datadimissione is not null
-- WHERE a.datadimissione is null

il quarto step aggiunge le anagrafiche senza assunzioni

SELECT d.id AS dipendenti_id, d.Cooperative_Id, null AS dataassunzione, d.cognome, d.nome, null AS datadimissione
FROM dipendenti d
LEFT JOIN assunzioni a ON d.id = a.dipendenti_id
WHERE a.dipendenti_id is null

ed infine l'ultimo step, unisce, filtra e ordina

SELECT * FROM (
....
UNION ALL
....
) y
WHERE ( cognome LIKE '%%' OR nome LIKE '%%' )
AND Cooperative_Id = 1
ORDER BY cognome, nome

fai sapere

 

[stefanoxjx]

Il problema sta in questa query secondo me, ed proprio quella su cui stavo lavorando per capire le differenze tra i tuoi e i miei risultati:

SELECT dipendenti_id, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id

infatti, espandendola un po':

SELECT id, dipendenti_id, dataassunzione, datadimissione, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id

trovo che mi da come risultato anche Dipendenti_id=1 che in realtà non dovrebbe esserci:

perchè la tabella contiene il record più recente con Dipendenti_id=1 che ha data dimissione vuota (record 44):

Inoltre, se guardiamo il risultato della query, vediamo che gli id dei record estratti di Dipendenti_id 1 e 3 sono 40 e 43 che non equivalgono al record più recente.
Dovrebbero infatti essere 41 per Dipendenti_Id = 3 e 44 per Dipendenti_id = 1.
Tu hai gli stessi risultati oppure diversi?
Grazie.

 

[marino51]

infatti, espandendola un po

non puoi espanderla un po', perchè cambi le condizioni, estrai più records di quello che deve essere estratto

puoi estendere la query più esterna, rispettando,
d.nomecolonna per tutti i campi che provengono dalla tabella dipendenti
a.nomecolonna per tutti i campi che provengono dalla tabella assunzioni
( "x." non può avere altre colonne )

le colonne che aggiungi devi riportarle anche nella query che ti estrae i dipendenti senza assunzioni

nella lista hai indicato id, suppongo sia l' id in assunzioni, dovresti scrivere così,

SELECT
  x.dipendenti_id, x.dataassunzione,
  d.Cooperative_Id, d.cognome, d.nome,
  a.datadimissione, a.id
FROM
( SELECT dipendenti_id, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id ) x

mentre nella query che aggiunge i dipendenti senza assunzioni devi aggiungere id,

SELECT d.id AS dipendenti_id, d.Cooperative_Id, null AS dataassunzione, d.cognome, d.nome, null AS datadimissione, null AS id
FROM dipendenti d
LEFT JOIN assunzioni a ON d.id = a.dipendenti_id
WHERE a.dipendenti_id is null

tutto questo se vuoi ottenere la lista di chi é assunto / non assunto / senza assunzioni (presente sempre nei casi precedenti)

se vuoi una lista diversa anche la query (più o meno complessa) sarà diversa


aggiungo un particolare, quando in una select é presente il "GROUP BY", tutte le colonne non "raggruppate" devono essere elencate nella clausola stessa

GROUP BY dipendenti_id, id ) x

non elencarli costituisce errore di sintassi, mySQL mi sembra che non lo rilevi, ma lo tratta come elemento per cui eseguire il raggruppamento

esaminiamo il caso seguente, é normale che estragga 2 records se aggiungi id, essendo id diverso nei 2 records per il dipendente 3

 

[marino51]

ho aggiunto id nella select, per evidenziarti l'errore (che mySQL non riporta)

 

[marino51]

ho dimenticato, questo il risultato xon "id" aggiunto

( pensiero : da ultimo si può agganciare anche la tabella cooperative per estrarne la descrizione )

 

[marino51]

dovrebbe essere completa,

la query é scritta per renderla il più comprensibile possibile,

SELECT
  y.dipendenti_id
, y.cognome
, y.nome
, y.Cooperative_Id
, c.cooperativa
, y.assunzioni_id
, y.dataassunzione
, y.datadimissione
FROM (
SELECT
  x.dipendenti_id
, d.cognome
, d.nome
, d.Cooperative_Id
, a.id                                   AS assunzioni_id
, CONVERT(varchar, x.dataassunzione, 23) AS dataassunzione
, CONVERT(varchar, a.datadimissione, 23) AS datadimissione
FROM
( SELECT dipendenti_id, max(dataassunzione) AS dataassunzione FROM assunzioni GROUP BY dipendenti_id ) x
LEFT JOIN dipendenti d ON x.dipendenti_id = d.id
LEFT JOIN assunzioni a ON a.dipendenti_id = d.id AND a.dataassunzione = x.dataassunzione
WHERE a.datadimissione is not null
-- WHERE a.datadimissione is null
UNION ALL
SELECT
  d.id  AS dipendenti_id
, d.cognome
, d.nome
, d.Cooperative_Id
, null  AS assunzioni_id
, null  AS dataassunzione
, null  AS datadimissione
FROM dipendenti d
LEFT JOIN assunzioni a ON d.id = a.dipendenti_id
WHERE a.dipendenti_id is null
) y
LEFT JOIN cooperative c ON y.cooperative_id = c.id
WHERE ( cognome LIKE '%%' OR nome LIKE '%%' )
AND Cooperative_Id = 1
ORDER BY cognome, nome

ok ?

 

[marino51]

sono riuscito a creare una nuova query, che sembra essere più semplice,

una select combina le 3 tabelle, dipendenti, cooperative assunzioni ed il solo valore massimo della "dataassunzione" associato ad ogni record estratto

ho dovuto usare gli alias perché nelle tabelle vi sono molti nomi uguali

l'elemento caratteristico sta nella scelta degli elementi da estrarre

WHERE ( a_dipendenti_id  is null OR ( a_datadimissione is not null AND x_dataassunzione = a_dataassunzione ) )
--WHERE ( a_dipendenti_id  is null OR ( a_datadimissione is null AND x_dataassunzione = a_dataassunzione ) )

puoi usare la "select *" per elencare le colonne che vuoi, nell'ordine necessario
ovviamente devono essere elencate anche nella select più interna

questo il (mio) risultato

questo il codice

SELECT *
FROM (
    SELECT
      d.id             AS d_id
    , d.cognome        AS d_cognome
    , d.nome           AS d_nome
    , d.Cooperative_Id AS d_Cooperative_Id
    , c.id             AS c_id
    , c.cooperativa    AS c_cooperativa 
    , a.id             AS a_id
    , a.dipendenti_id  AS a_dipendenti_id
    , a.tipo           AS a_tipo
    , a.dataassunzione AS a_dataassunzione
    , a.datadimissione AS a_datadimissione
    , ( SELECT max(x.dataassunzione) FROM @assunzioni x WHERE x.dipendenti_id = d.id ) AS x_dataassunzione
    FROM      @dipendenti  d
    LEFT JOIN @cooperative c ON d.cooperative_id = c.id
    LEFT JOIN @assunzioni  a ON a.dipendenti_id  = d.id
) y
WHERE ( a_dipendenti_id  is null OR ( a_datadimissione is not null AND x_dataassunzione = a_dataassunzione ) )
--WHERE ( a_dipendenti_id  is null OR ( a_datadimissione is null AND x_dataassunzione = a_dataassunzione ) )
  AND ( d_cognome LIKE '%%' OR d_nome LIKE '%%' )
  AND d_Cooperative_Id = 1
ORDER BY d_cognome, d_nome

fai sapere