Hi,
Below is the SQL Query i am currently having
SELECT IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionCode,IG_FinancialTransactionSummary.TransactionDate,IG_FinancialTransactionSummary.Username, FinancialTransactionSummaryTest.ClaimNum,FinancialTransactionSummaryTest.TransactionAmount,FinancialTransactionSummaryTest.UserName--,FinancialTransactionSummaryTest.TransactionDate FROM [ClaimsOperations].[Stg].[IG_FinancialTransactionSummary] inner join [dbo].[FinancialTransactionSummaryTest] ON IG_FinancialTransactionSummary.ClaimNum = FinancialTransactionSummaryTest.ClaimNum where ----Username like '%data%' and FinancialTransactionSummaryTest.ClaimNum IN (2000074,2000257) order by IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionDate
And here is the result dataset
ClaimNum TransactionDate Username ClaimNum TransactionAmount UserName 2000074 20150209 jerry.witt 2000074 -10000 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 20150626 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 -10000 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
ClaimNum TransactionDate Username ClaimNum TransactionAmount UserName 2000257 20150130 jevans 2000257 2547.76 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000257 20150626 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000257 2547.76 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000257 20150721 jevans 2000257 2547.76 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000257 20150721 jevans 2000257 2547.76 DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
So,if we look at the result set, we notice 2 conditions where the IG_FinancialTransactionSummary.Username is like 'Data' and if we see the transaction date then sometimes that is the max transaction date or sometimes there are transactions that happened after but that doesn't have like '%data%' in username . So, i need to add a new column to my sql query which should basically verify if the username is like '%data%' and if that is the max(transaction date) or even if there are any transactions after that doesn't have like '%data%' then YES else No.
- Edited by Naomi NModerator 12 hours 59 minutes ago Better formatting
- Edited by SqlDev12 12 hours 59 minutes ago