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
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.
ROW_NUMBER()
CASE
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