Please use the below temporary table and dataset for reference. Using the below dataset, I am trying to creating a new dataset.
DECLARE @Temp TABLE
(
year_month int,
Vuid int,
Puid int,
ac_cd varchar(20),
sub_ac_cd varchar(20),
jdg_sts varchar(20),
voy_pl_usd int,
updt_stamp datetime
)
insert into @temp values(
202005,1,1,'M011','0','S',30,GETDATE()-2
)
insert into @temp values(
202006,1,1,'M011','0','P',20,GETDATE()-1
)
insert into @temp values(
202007,1,1,'M011','0','M',40,GETDATE()
)
insert into @temp values(
202005,1,2,'M011','0','S',15,GETDATE()-2
)
insert into @temp values(
202006,1,2,'M011','0','P',10,GETDATE()-1
)
insert into @temp values(
202007,1,2,'M011','0','P',15,GETDATE()
)
The output data set should look like :
I have written the below query which is partially fulfilling my requirement:
(SELECT Vuid, Puid, ac_cd, sub_ac_cd, SUM (VOY_PL_USD) AS Cost
FROM @Temp
GROUP BY
Vuid,
Puid,
ac_cd,
sub_ac_cd
)
But Cost should also append the latest jdg_sts column value along with the SUM..I know the latest record is selected based on Latest Year_month.
PLEASE NOTE: My query will be placed inside a VIEW. Please tell me how do I achieve it..
DATASET:
Another version of output:
Try this:
To have the values in one column just use string concatenation: