sqlsql-servert-sqlviewinline-view

Need to fix a SQL Query for creating a view


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 :

enter image description here

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:

enter image description here

Another version of output:

enter image description here


Solution

  • 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
    

    enter image description here

    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