Need to fix a SQL Query for creating a view

66 Views Asked by At

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

2

There are 2 best solutions below

0
On BEST ANSWER

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
0
On
 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()
    )

SELECT *
FROM @Temp(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);

WITH cte
AS (
    SELECT Puid
        ,jdg_sts
        ,ROW_NUMBER() OVER (
            PARTITION BY puid ORDER BY updt_stamp DESC
            ) AS run
    FROM @Temp
    )
SELECT A.*
    ,cast(Cost AS VARCHAR) + jdg_sts
FROM (
    SELECT T1.Vuid
        ,T1.Puid
        ,T1.ac_cd
        ,T1.sub_ac_cd
        ,SUM(T1.VOY_PL_USD) AS Cost
    FROM @Temp T1
    GROUP BY T1.Vuid
        ,T1.Puid
        ,T1.ac_cd
        ,T1.sub_ac_cd
    ) A
LEFT JOIN cte cte ON cte.Puid = A.Puid
WHERE run = 1