SQLTeam.com | Weblogs | Forums

Aggregate to Sql

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

seems like a simple case when will work
Case when qta_lav is not null then 'LAV' when qta_str is not null then 'STR' end

I need to different incolumn,