Preetham Thangamma

Preetham Thangamma

  • NA
  • 167
  • 16.9k

display premium only for distinct row

May 11 2023 11:32 AM

Hi,

i have 4 columns in lossrun table in sql server named policynumber,effdate,premium and losstype. My requirement is if there are same values for effdate and policynumber in 3 columns then the premium values has to be dispalyed only for first row and all the remaining rows should display zero.there are 3 columns repeating with the same policynumber and effdate is because of different losstype

Note:the  same effdate and policynumber in single column present having a single losstype should display the premium value as it is. no change in it

thank you

 


Answers (1)

0
Mohamed Azarudeen Z

Mohamed Azarudeen Z

  • 142
  • 13.1k
  • 178.6k
May 11 2023 11:35 AM

To achieve your requirement in SQL Server, you can use a combination of ROW_NUMBER() function and a CASE statement to conditionally display the premium values based on the effdate and policynumber columns.

Here's an example query that should achieve your requirement:

SELECT policynumber, effdate,
  CASE
    -- Display premium value only for the first row
    WHEN ROW_NUMBER() OVER (PARTITION BY policynumber, effdate ORDER BY losstype) = 1
      THEN premium
    -- Display zero for all other rows with the same effdate and policynumber
    ELSE 0
  END AS premium,
  losstype
FROM lossrun
 

Accepted Answer