Populating query parameters from a temp table

[wbodger]

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

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

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

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

 

[johnherman]

Presumably, the queries are not going against the transactional database, but instead to some kind of data warehouse or ODS structure. That being the case, why not use a CURSOR? The CURSOR will be easy to read and maintain and the performance hit of using the cursor rather than pure SQL is probably tolerable. Cursors, like GO TO statements in programming, are not EVIL. Their overuse is what creates evil.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[wbodger]

I do believe that I found a way to do this without using cursors by creating a temp table, then using cross apply to broaden the temp table and then doing a cross apply against the transaction table with the temp table. If anybody is interested I can post what I ended up with.

wb