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
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.