Hej, mam dwie tabele transition_log
i grdm
.
Potrzebuje aby połączyć te dwie tabele i zrobić widok, który pokaże ostatnią date, id produktu, na kazdym z level_inv
.
Te dwie tabele można połączyć za pomocą transition_log.status_from = GRDM.state_from and transition_log.status_to=GRDM.state_to
Transition log
DATE | ID | status_from | status_to |
---|---|---|---|
5.10.2022 | 123456 | Open | In progress |
20.01.2023 | 123456 | Open | Closed |
24.05.2023 | 123456 | In progress | Closed |
5.11.2023 | 123456 | Assigned | In progress |
6.12.2023 | 123456 | In progress | Assigned |
GRDM
LEVEL_INV | Comment | state_from | state_to |
---|---|---|---|
L1 | xxxxxxx | Open | In progress |
L2 | xxxxxxx | Open | Closed |
L2 | xxxxxxx | In progress | Closed |
L1 | xxxxxxx | Assigned | In progress |
L1 | xxxxxxx | In progress | Assigned |
Rezultat
LEVEL_INV | DATE | ID | status_from | status_to |
---|---|---|---|---|
L1 | 6.12.2023 | 123456 | In progress | Assigned |
L2 | 24.05.2023 | 123456 | In progress | Closed |
Select GRDM.level_INV, MAX(TRL.DATE), TRL.ID, TRL.status,TRL.status_to
FROM TRANSITION_LOG as TRL
inner join GRDM on TRL.status_from=grdm.state_from and TRL.status_to=grdm.state_to
group by LEVEL_INV, ID
Niestety taka kwerenda nie działa