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:
CREATE OR ALTER VIEW vw_view
AS
(
SELECT TOP (1) WITH TIES *
,SUM(VOY_PL_USD) OVER (PARTITION BY Vuid, Puid, ac_cd, sub_ac_cd) cost
FROM Temp
ORDER BY ROW_NUMBER() OVER (PARTITION BY Vuid, Puid, ac_cd, sub_ac_cd ORDER BY year_month DESC)
)
GO
To have the values in one column just use string concatenation:
CREATE OR ALTER VIEW vw_view_1
AS
(
SELECT TOP (1) WITH TIES year_month
,Vuid
,Puid
,ac_cd
,sub_ac_cd
,voy_pl_usd
,updt_stamp
,CAST(SUM(VOY_PL_USD) OVER (PARTITION BY Vuid, Puid, ac_cd, sub_ac_cd) AS VARCHAR(12)) + jdg_sts AS [cost]
FROM Temp
ORDER BY ROW_NUMBER() OVER (PARTITION BY Vuid, Puid, ac_cd, sub_ac_cd ORDER BY year_month DESC)
)
GO