I am building a system where users can customize queries to pull data out of a transaction history table. The parameters are stored in a temporal table so we can easily track changes
And the actual data table is
My struggle is (aside from using a cursor or some other type of loop) I am not sure how I would tie all of the values from the parameter table together because one bank may have the same alert for multiple different merchants or it may have the alert for 'All' merchants and another overriding entry for a single merchant.
The parameter data would look like
and the actual data would always only have one EntityId. So, any thoughts on how to ensure that all of the parameters for each record in the table stay together?
Thanks
wb
Code:
CREATE TABLE [dbo].[RedFlagsLimits](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedName] [varchar](50) NULL,
[UpdatedBy] [int] NULL,
[UpdatedName] [varchar](50) NULL,
[AlertId] [tinyint] NOT NULL,
[TimeFrame] [varchar](20) NULL,
[BankId] [int] NOT NULL,
[EntityIds] [varchar](4000) NULL,
[TransactionType] [varchar](20) NOT NULL,
[LowerTime] [varchar](12) NULL,
[UpperTime] [varchar](12) NULL,
[Measure] [varchar](4) NULL,
[ThresholdType] [varchar](12) NULL,
[ThresholdValue] [int] NULL,
[Product] [varchar](20) NULL,
[IsEnabled] [bit] NOT NULL
)
And the actual data table is
Code:
CREATE TABLE [mrb].[TransactionHistory](
[TransactionId] [int] IDENTITY(1,1) NOT NULL,
[ImportDate] [datetime2](0) NOT NULL,
[ImportFileName] [varchar](100) NULL,
[MonthYear] [varchar](9) NOT NULL,
[FirstOfMonth] [date] NOT NULL,
[BankId] [int] NOT NULL,
[AccountName] [varchar](50) NOT NULL,
[MerchantId] [int] NOT NULL,
[AccountNumber] [varbinary](100) NOT NULL,
[AccountISN] [int] NULL,
[TransactionDate] [datetime2](0) NOT NULL,
[TransactionAmount] [decimal](18, 2) NOT NULL,
[CashAmount] [decimal](18, 2) NOT NULL,
[ActionCode] [varchar](6) NULL,
[TransactionCode] [varchar](6) NULL,
[TellerId] [varchar](4) NULL,
[OpsCode] [varchar](4) NULL,
[TxCode] [int] NULL,
[TransactionType] [varchar](10) NOT NULL,
[TransactionDescription] [varchar](100) NULL,
[CheckNumber] [bigint] NULL,
[TransactionCodeDescription] [varchar](50) NULL,
[BeforeBalance] [decimal](18, 2) NULL,
[Balance] [decimal](18, 2) NULL,
[CounterPartyId] [varchar](50) NULL,
[CounterPartyName] [varchar](100) NULL
)
My struggle is (aside from using a cursor or some other type of loop) I am not sure how I would tie all of the values from the parameter table together because one bank may have the same alert for multiple different merchants or it may have the alert for 'All' merchants and another overriding entry for a single merchant.
The parameter data would look like
Code:
Id BankId EntityIds TimeFrame TransactionType ThresholdValue Product
6 300000050 100007762,100007763 Weekly Deposit 5 200
and the actual data would always only have one EntityId. So, any thoughts on how to ensure that all of the parameters for each record in the table stay together?
Thanks
wb