Tuesday, March 11, 2014

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

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.