Query fix for a SQL View

Question

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
    )

insert into @temp values(202005,1,1,'M01180','0','S',30)
insert into @temp values(202006,1,1,'M01180','0','P',20)
insert into @temp values(202007,1,1,'M01180','0','M',40)
insert into @temp values(202005,1,2,'M01180','0','S',15)
insert into @temp values(202006,1,2,'M01180','0','P',10)
insert into @temp values(202007,1,2,'M01180','0','P',15)
insert into @temp values(202005,2,1,'M01180','0','S',60)
insert into @temp values(202006,2,1,'M01180','0','P',15)
insert into @temp values(202007,2,1,'M01180','0','M',10)
insert into @temp values(202005,2,2,'M01180','0','S',25)
insert into @temp values(202006,2,2,'M01180','0','P',25)
insert into @temp values(202007,2,2,'M01180','0','A',30)
insert into @temp values(202005,1,1,'M01170','0','A',20)
insert into @temp values(202006,1,1,'M01170','0','A',20)
insert into @temp values(202007,1,1,'M01170','0','A',35)
insert into @temp values(202005,1,2,'M01170','0','P',15)
insert into @temp values(202006,1,2,'M01170','0','S',5)
insert into @temp values(202007,1,2,'M01170','0','M',5)
insert into @temp values(202005,2,1,'M01170','0','S',5)
insert into @temp values(202006,2,1,'M01170','0','P',15)
insert into @temp values(202007,2,1,'M01170','0','P',20)
insert into @temp values(202005,2,2,'M01170','0','S',5)
insert into @temp values(202006,2,2,'M01170','0','P',15)
insert into @temp values(202007,2,2,'M01170','0','M',10)

The output data set should look like :

In above image, Port cost is the SUM of VOY_PL_USD column concatenated with the latest jdg_sts column value based on voy_uid, pc_uid, ac_cd('M01180') & sub_ac_cd columns. Similarly Agent fee is the SUM of VOY_PL_USD column concatenated with the latest jdg_sts column value based on voy_uid, pc_uid, ac_cd('M01170') & sub_ac_cd columns. The latest record is selected based on the latest Year_month field.

PLEASE NOTE: My query will be placed inside a VIEW. Please tell me how do I achieve it..

DATASET:

This is what I have written:

SELECT TOP (1) WITH TIES 
                            Vuid
                            ,Puid
                            ,ac_cd
                            ,sub_ac_cd                          
                            ,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) 

Answer 1

Try this:

SELECT Vuid
      ,Puid
      ,CAST(MAX([Port Cost]) AS VARCHAR(12)) + MAX(CASE WHEN [Pro Cost Suffix] = 1 THEN [jdg_sts] END) AS  [Port Cost]
      ,CAST(MAX([Agent Fee]) AS VARCHAR(12)) +  MAX(CASE WHEN [Agent Fee Suffix] = 1 THEN [jdg_sts] END) AS  [Agent Fee]
FROM
(
    SELECT Vuid, Puid
          ,SUM(CASE WHEN ac_cd = 'M01180' THEN VOY_PL_USD END) OVER (PARTITION BY Vuid, Puid, ac_cd, sub_ac_cd)
          ,SUM(CASE WHEN ac_cd = 'M01170' THEN VOY_PL_USD END) OVER (PARTITION BY Vuid, Puid, ac_cd, sub_ac_cd)       
          ,CASE WHEN ac_cd = 'M01180' THEN DENSE_RANK() OVER (PARTITION BY Vuid, Puid, sub_ac_cd ORDER BY Year_month DESC) END
          ,CASE WHEN ac_cd = 'M01170' THEN DENSE_RANK() OVER (PARTITION BY Vuid, Puid, sub_ac_cd ORDER BY Year_month DESC) END
          ,jdg_sts
     FROM Temp
) DS (Vuid, Puid, [Port Cost], [Agent Fee], [Pro Cost Suffix], [Agent Fee Suffix], [jdg_sts])
GROUP BY Vuid
        ,Puid
ORDER BY Vuid
        ,Puid;