Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Populating query parameters from a temp table

Status
Not open for further replies.

wbodger

Programmer
Apr 23, 2007
769
US
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

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


 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top