Scenario of getting the largest amount from multiple max date entries of a customer

This is a scenario where getting the largest transaction amount of a customer in which there are multiple same max date entries available for him.

WITH CTE AS (SELECT ConstituentDefinitionId
,CompanyId
, RetailAmountReceived
,TransactionDate
,TransactionId
,SourceCodeId
, RN
FROM ( SELECT 
[ConstituentDefinitionId] 
 ,sc.CompanyId
 ,ts.RetailAmountReceived
 ,t.TransactionDate
 ,t.TransactionId
 ,t.SourceCodeId
 , ROW_NUMBER() OVER(PARTITION BY [ConstituentDefinitionId], ts.RetailAmountReceived ORDER BY transactiondate DESC) AS RN
 FROM [dbo].[Transaction] t
 INNER JOIN [dbo].[TransactionCompany] sc ON t.TransactionId = sc.TransactionId
 INNER JOIN dbo.TransactionSummary ts ON t.TransactionId = ts.TransactionId
WHERE t.Isbenevolent = 0 and RetailAmountReceived > 0 and TotalTransactionAmount > 0 ) AS t )


SELECT [ConstituentDefinitionId]
,CompanyId
, MAX(CONVERT(Binary(10),RetailAmountReceived) + CONVERT(Binary(10),[TransactionDate]) + CONVERT(Binary(10),[TransactionId]) + CONVERT(Binary(10),[SourceCodeId])) as LargestPurchaseData
FROM cte
WHERE RN = 1
GROUP BY [ConstituentDefinitionId], CompanyId

Scenario of getting first and last communication date of each customer

This is a scenario where I need to get the first and last communication date of each customer from a customer table along with corresponding communication Id and SourceCodeId


WITH cte AS(SELECT 
 [CustomerId] 
 ,sc.CompanyId

 ,MIN(CONVERT(VARCHAR, cm.CommunicationDate,121) + CONVERT(VARCHAR, cm.CommunicationId)+ '*' + CONVERT(VARCHAR,cm.SourceCodeId)) AS FirstCommunicationData
 ,MAX(CONVERT(VARCHAR, cm.CommunicationDate,121) + CONVERT(VARCHAR, cm.CommunicationId) + '*' + CONVERT(VARCHAR,cm.SourceCodeId)) AS LastCommunicationData

FROM dbo.Customer cm
INNER JOIN [dbo].[SourceCode] sc on cm.SourceCodeId = sc.SourceCodeId
WHERE CustomerId = 123 --Just used a customer id for test
GROUP BY CustomerId ,sc.CompanyId)

SELECT [CustomerId]
, CompanyId 
, CONVERT(DATE,SUBSTRING(FirstCommunicationData,1,23),121) [FirstCommunicationDate]
, CONVERT(INT,SUBSTRING(FirstCommunicationData,24,CHARINDEX('*',FirstCommunicationData)-24))[FirstCommunicationId]
, CONVERT(INT,SUBSTRING(FirstCommunicationData,CHARINDEX('*',FirstCommunicationData)+1,(LEN(FirstCommunicationData) - CHARINDEX('*',FirstCommunicationData)+1)))[FirstCommunicationSourceCodeID]
, CONVERT(DATE,SUBSTRING(LastCommunicationData,1,23),121) [LastCommunicationDate]
, CONVERT(INT,SUBSTRING(LastCommunicationData,24,CHARINDEX('*',LastCommunicationData)-24))[LastCommunicationId]
, CONVERT(INT,SUBSTRING(LastCommunicationData,CHARINDEX('*',LastCommunicationData)+1,(LEN(LastCommunicationData) - CHARINDEX('*',LastCommunicationData)+1)))[LastCommunicationSourceCodeID]
FROM cte



Using multiple char index:

WITH CTE AS(SELECT 
 [DonorId] 
 ,sc.CompanyId
 , MIN(CONVERT(VARCHAR,t.[TransactionDate],120) + CONVERT(VARCHAR,t.[TransactionId]) + '*' + CONVERT(VARCHAR,t.SourceCodeId)+ '&'+ CONVERT(VARCHAR,ts.TotalDonation)) [FirstDonationData]                            
 , MAX(CONVERT(VARCHAR,t.[TransactionDate],120) + CONVERT(VARCHAR,t.[TransactionId]) + '*' + CONVERT(VARCHAR,t.SourceCodeId)+ '&'+ CONVERT(VARCHAR,ts.TotalDonation)) [LastDonationData]
 , MAX(CONVERT(Binary(10),ts.TotalDonation) + CONVERT(Binary(10),t.[TransactionDate]) + CONVERT(Binary(10),t.[TransactionId]) + CONVERT(Binary(10),t.[SourceCodeId])) as LargestDonationData
 FROM [dbo].[Transaction] t
 INNER JOIN [dbo].[TransactionCompany] sc on t.TransactionId = sc.TransactionId
 INNER JOIN dbo.TransactionSummary ts ON t.TransactionId = ts.TransactionId
WHERE DonorId = 13383180
GROUP BY [DonorId], sc.CompanyId)

 SELECT DonorId
 , SUBSTRING([FirstDonationData],1,10) [FirstDonationDate]
 , REPLACE(SUBSTRING([FirstDonationData],11,LEN([FirstDonationData])), (SUBSTRING([FirstDonationData],CHARINDEX('*',[FirstDonationData]),LEN([FirstDonationData]))),'') [FirstDonationID]
 , REPLACE((SUBSTRING([FirstDonationData],CHARINDEX('*',[FirstDonationData])+1,LEN([FirstDonationData]))),(SUBSTRING([FirstDonationData],CHARINDEX('&',[FirstDonationData]),LEN([FirstDonationData]))),'') [FirstDonationSourceCodeId]
 , SUBSTRING([FirstDonationData],CHARINDEX('&',[FirstDonationData])+1,LEN([FirstDonationData])) [FirstDonationAmount]
 , SUBSTRING([LastDonationData],1,10) [LastDonationDate]
 , REPLACE(SUBSTRING([LastDonationData],11,LEN([LastDonationData])), (SUBSTRING([LastDonationData],CHARINDEX('*',[LastDonationData]),LEN([LastDonationData]))),'') [LastDonationID]
 , REPLACE((SUBSTRING([LastDonationData],CHARINDEX('*',[LastDonationData])+1,LEN([LastDonationData]))),(SUBSTRING([LastDonationData],CHARINDEX('&',[LastDonationData]),LEN([LastDonationData]))),'') [LastDonationSourceCodeId]
 , SUBSTRING([LastDonationData],CHARINDEX('&',[LastDonationData])+1,LEN([LastDonationData])) [LastDonationAmount]
 , CONVERT(money,SUBSTRING(LargestDonationData,1,10)) [LargestDonationAmount]
 , CONVERT(DATE,SUBSTRING(LargestDonationData,11,10)) [LargestDonationDate]
 , CONVERT(INT,SUBSTRING(LargestDonationData, 21,10)) [LargestDonationID]
 , CONVERT(INT,SUBSTRING(LargestDonationData,31,10)) [LargestDonationSourceCodeID]

FROM CTE

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.

Scenario (Updating the existing records and replacing with new entry and inserting new records)

Scenario: The details in the table is updated with IsActive, StartDate and EndDate columns to manage the History. The given script in this scenario is to reflect the below condition.


Condition: A table is already updated with records of product and representative details.
(1). When an existing product details updated in source, then the existing record should be udpated with EndDate  and '0' in IsActive column in destination. A new row should be inserted with the updated values with StartDate and '1' as IsActive. 
(2). When an existing employee details updated in source, then the existing record should be updated with respective changes in destination. No need to insert new row.
(3). When a new entry made in the source, then that should be inserted in the destination table.
(4). Delete the record in Destination when the record is deleted in source.


Below are the source tables:
ExamleTable1: work.dbo.Product

ExampleTable2: work.dbo.Employee

Now the below SQL tasks are used to perform the scripts for conditions.

Script to delete records in Destination those are deleted in source.

MERGE dbo.PractiseTable1 AS T
USING (SELECT [ProductId]
,[ProductName]
,[ProductType]
,[AvailableQty]
,[Price]
,[Location]
,[EmpName]
,[Age]
,[Designation]
,[CtNumber]
,1 AS [IsActive]
,GETDATE() AS StartDate
,NULL AS EndDate
,BINARY_CHECKSUM ([ProductId],[ProductName],[ProductType],[Price],[Location]) AS  HashKey
  FROM dbo.Employee S1
    JOIN dbo.Product S2
       ON s1.EmpId = s2.RepId) AS S
       ON s.productid = t.productid
 WHEN not matched BY source
  THEN DELETE;


Script to updating the existing records as Inactive with EndDate


MERGE dbo.PractiseTable1 AS T
USING (SELECT [ProductId]
,[ProductName]
,[ProductType]
,[AvailableQty]
,[Price]
,[Location]
,[EmpName]
,[Age]
,[Designation]
,[CtNumber]
,1 AS [IsActive]
,GETDATE() AS StartDate
,NULL AS EndDate
,BINARY_CHECKSUM ([ProductId],[ProductName],[ProductType],[Price],[Location]) AS HashKey
    FROM dbo.Employee S1
      JOIN dbo.Product S2
         ON s1.EmpId = s2.RepId) AS S
         ON s.productid = t.productid
   WHEN matched and s.hashkey <> t.hashkey
    THEN update
       SET t.IsActive = 0, T.Enddate = GETDATE();


Script to update records for changes in Employee table and to insert new records for changes in Product table


MERGE dbo.PractiseTable1 AS T
USING (SELECT [ProductId]
,[ProductName]
,[ProductType]
,[AvailableQty]
,[Price]
,[Location]
,[EmpName]
,[Age]
,[Designation]
,[CtNumber]
,1 AS [IsActive]
,GETDATE() AS StartDate
,NULL AS EndDate
,BINARY_CHECKSUM ([ProductId],[ProductName],[ProductType],[Price],[Location]) AS HashKey
FROM dbo.Employee S1
JOIN dbo.Product S2
ON s1.EmpId = s2.RepId) AS S
ON t.hashkey = s.hashkey
WHEN matched THEN UPDATE
SET t.[Productid] = s.[productid]
,t.[ProductType] = s.[ProductType]
,t.[AvailableQty] = s.[AvailableQty]
,t.[Price] = s.[Price]
,t.[Location] = s.[Location]
,t.[EmpName] = s.[EmpName]
,t.[Age] = s.[Age]
,t.[Designation] = s.[Designation]
,t.[CtNumber] = s.[CtNumber]
,t.[IsActive] = s.[IsActive]
,t.[StartDate] = s.[StartDate]
,t.[EndDate] = s.[EndDate]
,t.[HashKey] = s.[HashKey]
WHEN not matched
THEN INSERT  ([ProductId]
,[ProductName]
,[ProductType]
,[AvailableQty]
,[Price]
,[Location]
,[EmpName]
,[Age]
,[Designation]
,[CtNumber]
,[IsActive]
,[StartDate]
,[EndDate]
,[HashKey])
VALUES (s.[ProductId]
,s.[ProductName]
,s.[ProductType]
,s.[AvailableQty]
,s.[Price]
,s.[Location]
,s.[EmpName]
,s.[Age]
,s.[Designation]
,s.[CtNumber]
,s.[IsActive]
,s.[StartDate]
,s.[EndDate]
,s.[HashKey]);

Script to insert new records


MERGE dbo.PractiseTable1 AS T
USING (SELECT [ProductId] 
,[ProductName]
,[ProductType]
,[AvailableQty]
,[Price]
,[Location]
,[EmpName]
,[Age]
,[Designation]
,[CtNumber]
,1 AS [IsActive]
,GETDATE() AS StartDate
,NULL AS EndDate
,BINARY_CHECKSUM ([ProductId],[ProductName],[ProductType],[Price],[Location]) AS HashKey
FROM dbo.Employee S1
JOIN dbo.Product S2
ON s1.EmpId = s2.RepId) AS S
ON s.productid = t.productid
WHEN not matched
THEN INSERT  ([ProductId] 
,[ProductName]
,[ProductType]
,[AvailableQty]
,[Price]
,[Location]
,[EmpName]
,[Age]
,[Designation]
,[CtNumber]
,[IsActive]
,[StartDate]
,[EndDate]
,[HashKey])
VALUES (s.[ProductId] 
,s.[ProductName]
,s.[ProductType]
,s.[AvailableQty]
,s.[Price]
,s.[Location]
,s.[EmpName]
,s.[Age]
,s.[Designation]
,s.[CtNumber]
,s.[IsActive]
,s.[StartDate]
,s.[EndDate]
,s.[HashKey]);

Below is the result for the requirement.

Scenario (Update and Insert Using HashKey)

Scenario: The details of products are updated in a table with StartDate, EndDate as null since the product is active and IsActive as 1 since the product is still active. 


Condition: When the details of a product is updated in source, then a new row should be updated for the same product in the destination and existing row should be udpated with EndDate as when the product updated second time and IsActive should be udpated as 0 which the product is not active.


SQL Update Script


MERGE dbo.ProductCodeHistory AS T1
USING (SELECT  ProductCodeID
               ,BINARY_CHECKSUM([ProductCodeId], [ProductDefinitionId], [ProductCodeType],      CodeValue, [IsActive], [IsDeleted]) AS HashKey
              , ? AuditTaskID
FROM ProductCode
WHERE ProductCodeType 
IN ('Cable', 'Wire', 'Flange', 'Ring') S1
ON (S1.ProductCodeID = T1. ProductCodeId)
WHEN MATCHED AND S1.HashKey <> T1.Hashkey  
THEN UPDATE SET T1.IsActive = 0, T1.EndDate = GETDATE(), T1.AuditTaskID = S1.AuditTaskID;
SELECT ? = @@ROWCOUNT;


SQL Insert Script


MERGE dbo. ProductCodeHistory AS T1
USING (SELECT   ProductCodeID
        ,[ ProductDefinitionId]
        ,[ ProductCodeType]
        ,[CodeValue]
        ,[IsActive] CodeIsActive
        ,IsDeleted
        ,BINARY_CHECKSUM([ProductCodeId], [ ProductDefinitionId], [ ProductCodeType],  CodeValue, [IsActive], [IsDeleted]) HashKey
       , ? AuditTaskID
FROM  ProductCode
WHERE  ProductCodeType 
IN ('Cable', 'Wire', 'Flange', 'Ring') S1
ON  S1.HashKey = T1.Hashkey  AND T1.IsActive = 1
WHEN NOT MATCHED THEN
INSERT ( ProductCodeId
,ProductDefinitionId
,CodeType
,CodeValue
,CodeIsActive
,IsDeleted
,IsActive
,StartDate
,AuditTaskID)
VALUES
(S1. ProductCodeID
,S1.[ProductDefinitionId]
,S1.[ProductCodeType]
,S1.[CodeValue]
,S1.CodeIsActive
,S1.IsDeleted
,1
,Getdate()
,S1.AuditTaskID
);
SELECT ? = @@ROWCOUNT;

Scenario Based SQL Script

Scenario:
Table A contain columns like PledgeId, CodeType, Codevalue. etc.,
(In the below script, table A name is "ConstituentPledgeCode")


Table B contain columns like PledgeId, etc.,
(In the below script, table B name is "ConstituentPledge")


Requirement is:
Create 3 new columns in ConstituentPledge to roll the ConstituentPledgeCode data up (in EDW, ListSelect20. 
  A. RecurringType (if multiple, select the first one) 
     Look for CodeType = 'RECUR' and IsActive = 1... load the code value into RecurringType 
  B. FriendsFamStatementPreference (if multiple, select the first one) 
     Look for CodeType = 'STMNTPREF' and IsActive = 1... load the code value into FriendsFamStatementPreference 
  C. CLinkStatementPreference (if multiple, select the first one) 
     Look for CodeType = 'CLSTMNTPRF' and IsActive = 1... load the code value into CLinkStatementPreference 




SELECT  ap.[RecordId] as [ConstituentPledgeKey]
      ,cd.[ConstituentDefinitionId]
      ,ap.[PledgeCode]
      ,ap.[PledgeId]
      ,pm.PledgeMasterId
      ,[TotalAmount]
      ,[AmountPerGift]
      ,[PledgeFrequency]
      ,[NumberPledged]
      ,ap.[StartDate]
      ,ap.[EndDate]
      ,[OpenEnded]
      ,[PledgeStatus]
      ,sc.SourceCodeId
      ,[CurrencyCode]
      ,[CancelReasonCode]
      ,[CancelDate]
      ,ap.[ResponseId]
 ,trm.[TransactionResponseMasterId]
      ,[NbrOfAdjustmentMonths]
      ,[AcctNbrForPledgeStmtAndAck]
      ,[AcctNbrForPledgeInfoUpdates]
      ,[OriginalPledgeId]
      ,ap.[RecordId]
      ,ISNULL(RT.CodeValue,'') AS RecurringType 
 ,ISNULL(FFSP.CodeValue,'') AS FriendsFamStatementPreference 
 ,ISNULL(CLP.CodeValue,'') AS CLinkStatementPreference
FROM [dbo].s_A10_AccountPledges ap
INNER JOIN ConstituentDefinition cd  ON cd.AccountKey = ap.AccountNumber
LEFT JOIN PledgeMaster pm on pm.PledgeCode = ap.PledgeCode
LEFT JOIN SourceCode sc on sc.SourceCode = ap.SourceCode
LEFT JOIN TransactionResponseMaster trm ON trm.[ResponseId] = ap.[ResponseId]
LEFT JOIN (SELECT Pledgeid, Codevalue FROM  dbo.ConstituentPledgeCode
WHERE CodeType = 'RECUR' and Active = 1
) RT on RT.PledgeId = ap.PledgeId


LEFT JOIN (SELECT Pledgeid, Codevalue FROM  dbo.ConstituentPledgeCode
WHERE CodeType = 'STMNTPREF' and Active = 1
) FFSP on FFSP.PledgeId = ap.PledgeId


LEFT JOIN (SELECT Pledgeid, Codevalue FROM  dbo.ConstituentPledgeCode
WHERE CodeType = 'CLSTMNTPRF' and Active = 1
) CLP ON CLP.PledgeId = ap.PledgeId
WHERE ap.Operation = 'M' AND ap.[RecordExists]  IS NULL