Scenario - Updating a column based on the other column's value using Dense_Rank function

Requirement:

There should only be one row for any given TransactionId that has a 1 in the ResponseCount. All other rows should have a 0.

In cases where a response is split among several rows, add a 1 to the row based on the transaction type hierarchy below:
1) Donation
2) Subscription
3) Product
4) Event
5) Non Cash
6) Communcation

The top ranking row will get a 1 while the other(s) will get a 0.

In cases with only one row, it will of course get a 1

If there are multiple rows within any of the 1-6 categories above, select the highest dollar row first to get the ResponseCount 1 value, else select the first row.

Solution:

Create a table "dbo.TransactionDetailGiftTest" as shown below:


CREATE TABLE [dbo].[TransactionDetailGiftTest](
[TransactionHistoryKey] [int] NOT NULL,
[DSTransactionId] [int] NOT NULL,
[Type] [varchar](20) NOT NULL,
[TransactionDate] [date] NULL,
[FiscalYear] [int] NULL,
[FiscalMonth] [int] NULL,
[TotalAmount] [money] NULL,
[ResponseCount] [int] NULL,
 CONSTRAINT [PK_TransactionDetailGiftTest] PRIMARY KEY CLUSTERED
(
[TransactionHistoryKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Also create a Dimenstion table "dbo.dim" for type hierarchy 

CREATE TABLE [dbo].[Dim](

[ResponseCountKey] [int] IDENTITY(1,1) NOT NULL,

[Type] [varchar](20) NOT NULL,

 CONSTRAINT [PK_Dim] PRIMARY KEY CLUSTERED 

(

[ResponseCountKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Now write the below mention script in order to achieve the result

WITH CTE AS (SELECT Responsecount, DENSE_RANK()

OVER (PARTITION BY dstransactionid ORDER BY td.responsecountkey, totalamount DESC) AS Result

FROM dbo.TransactionDetailGiftTest tt

INNER JOIN dbo.dim td on tt.[Type] = td.[type])

UPDATE CTE

SET ResponseCount = Result

UPDATE dbo.TransactionDetailGiftTest

SET ResponseCount = 0

WHERE responsecount > 1 

Now the table should be updated as required: Run the query to check the table:

SELECT * FROM dbo.TransactionDetailGiftTest

This scenario helps to understand a logic for the similar kind of requirement.