CODEWITHSUNDEEP
sqlsql-server
Mike
Mike

Reputation: 67

Dynamically create column by Aggregating other column

I am trying to create a new column called "Premium" by summing the values of the column "PremiumByCode" based on their respective "Codes" column.. I want to display only the non-zero Premium rows in the final result ..Also, I am trying to approximate the final Premium column with no decimal values . I am using MS SQL and here is my below code.

I want to create new column as following :

[Premium] = max([PremiumByCodes]) per Codes and finally show only the records that has non-zero [Premium] value

   ...............
    select  a.FileID,
                a.InsName, 
                a.[Policy Number],
                a.[Effective Date], 
                a.LOB, 
                b.Codes, 
                --a.[NWP],              
                [PremiumByCodes]  = a.[NWP]/(1-c.[Commission%]-c.[Deduction%]),
                c.[Commission%]*100 as Commission,
                c.[Deduction%]*100 as Deduction,  
                c.[Currency]


                --Codes_Commission = [Premium] * c.[Commission%]
        from #temp1 a
        inner join IT.dbo.Perils_Codes_Lookup b 
        on b.Peril = a.LOB

        right join (
        -- Extract Commission Information
            select FileID, 
                    --ISNULL([Commission], 0) as Commisssion,
                    CAST (ISNULL([Commission], 0) as float) as [Commission%],           
                    CAST (ISNULL([Deduction], 0) as float) as [Deduction%],
                    ISNULL([Currency],0) as [Currency]
        from (select FileID, Worksheet, Label,LOB,Data
        from IT.[dbo].[DATA] 
        where 
        FileID = 18265
        and Worksheet in ('Summary')
        and LOB in ( 'N/A')
        ) d 
        PIVOT
            (max([Data]) 
             for [Label] in ([Commission], [Deduction],[Currency])
             --,[Commission])
            )piv 
        )c

        on c.FileID = a.FileID

My current table looks like: 

+ ----- + ---------------- +
| Codes | PremiumByASL     |
+ ----- + ---------------- +
| 010   | 27857.9403619788 |
| 027   | 4295.89527248191 |
| 021   | 22211.0617400998 |
| 120   | 3718.53861388411 |
| 012   | 0                |
| 120   | 6952.564745595   |
| 025   | 23970.5245355982 |
| 021   | 0                |
| 021   | 63683.0552706094 |
| 021   | 139.161555555556 |
| 021   | 39.3638524365101 |
+ ----- + ---------------- +

My expected output is like below :

+ ----- + ---------------- +
| Codes | Premium          |
+ ----- + ---------------- +
| 010   | 27,858           |
| 021   | 86,073           |
| 027   | 4,296            |
| 120   | 249,803          |
| 025   | 23,971           |
+ ----- + ---------------- +

Upvotes: 1

Views: 67

Answers (2)

Balan
Balan

Reputation: 421

Cost the field as money

 CONVERT(varchar, CAST(Premium AS money), 1)

Upvotes: 0

Erwin Dockx
Erwin Dockx

Reputation: 283

I would create a view, function or another temporary table that simply returns a set with each code and its aggregated premiums. Then join with that view, function or temporary table.

That way you can perform whatever follow-up logic on the results. And you can filter out the codes that return zero.

Upvotes: 1

Related Questions