Hello I need to aggregate the information so that: there should be a new column called Type, in this column if I have a value in the qta_lav column then I put it as lav, if I have qta_str in the column then I put it as str .... I am attaching images
SELECT hr01.kint,
left(convert(varchar, hr03.data, 112), 6) as oelen,
ROW_NUMBER() over (order by hr01.kint) as nprog_ielen,
hr07.codice as progetto,
hr08.codice as argomento,
hr09.codice as attivita,
(hr07.codice+hr08.codice+hr09.codice) as commessa,
hr03.qta_lav,
hr03.qta_str,
hr04.kmacchina,
hr04.oremacchina,
hr06.codice
FROM rapp_lavoro_ris hr01
LEFT JOIN UserHR.ts_rapp_timesheetgg hr02 ON (hr01.kint = hr02.kpadre)
LEFT JOIN UserHR.ts_rapp_timesheetdet hr03 ON (hr02.kint = hr03.kpadre)
LEFT JOIN xts_timesheetdet_macchine hr04 ON (hr03.kint = hr04.kpadre)
LEFT JOIN rapp_raggrcontr hr05 ON (hr01.kint = hr05.kpar)
LEFT JOIN ts_progetti hr07 ON hr07.kint=hr03.kprogetti
LEFT JOIN ts_argomenti hr08 ON hr08.kint=hr03.kargomenti
LEFT JOIN ts_attivitamaster hr09 ON hr09.kint=hr03.kattivita
LEFT JOIN raggrcont hr06 ON (hr05.karr = hr06.kint) WHERE (hr03.data BETWEEN '20220301' AND '20220301')
| kint | oelen | nprog_ielen | progetto | argomento | attivita | comm | qta_lav | qta_str | kmacchina | oremacchina | codice |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 10071 | 202203 | 1 | 1803 | 1803C | 1803CD | 18031803C1803CD | 480 | 60 | 10001 | 120 | DL4 |
| 10077 | 202203 | 2 | 1803 | 1803C | 1803CD | 18031803C1803CD | 600 | DL2 |
| kint | oelen | nprog_ielen | progetto | argomento | attivita | comm | type | qta | kmacchina | oremacchina | codice |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 10071 | 202203 | 1 | 1803 | 1803C | 1803CD | 18031803C1803CD | LAV | 480 | |||
| 10071 | 202203 | 1 | 1803 | 1803C | 1803CD | 18031803C1803CD | STR | 60 | |||
| 10071 | 202203 | 1 | 1803 | 1803C | 1803CD | 18031803C1803CD | 10001 | 120 | DL4 | ||
| 10077 | 202203 | 2 | 1803 | 1803C | 1803CD | 18031803C1803CD | STR | 600 | DL2 |
