Need help on Dynamic T-SQL
-
I've 3 Tables as follow,
CREATE TABLE [dbo].[crpt_Comparison_Operator](
[idx] [int] IDENTITY(1,1) NOT NULL,
[Comparison_Operator_Code] [nvarchar](50) NOT NULL,
[Comparison_Operator_Desc] [nvarchar](100) NULL,
CONSTRAINT [PK_crpt_Comparison_Operator] PRIMARY KEY CLUSTERED
(
[Comparison_Operator_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [crpt_Comparison_Operator_UQ1] UNIQUE NONCLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[crpt_LembaranImbangan_2_Param_2](
[idx] [int] IDENTITY(-2147483648,1) NOT NULL,
[column_Nme] [nvarchar](100) NULL,
[Comparison_Operator_Code] [nvarchar](50) NULL,
[what_Value] [nvarchar](5) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[crpt_LembaranImbangan_1](
[batch_Id] [uniqueidentifier] NULL,
[group_chart_code_1] [int] NULL,
[group_chart_code_2] [int] NULL,
[group_chart_code_3] [int] NULL,
[group_chart_code_4] [int] NULL,
[chart_code] [nvarchar](5) NULL,
[pusat_kos] [nvarchar](50) NULL,
[jour_code] [nvarchar](10) NULL,
[jour_num] [nvarchar](50) NULL,
[jour_seq_num] [int] NULL,
[desc_text] [nvarchar](50) NULL,
[SA_Debit_Amt] [decimal](14, 2) NULL,
[SA_Credit_Amt] [decimal](14, 2) NULL,
[AA_Debit_Amt] [decimal](14, 2) NULL,
[AA_Credit_Amt] [decimal](14, 2) NULL
) ON [PRIMARY]
3 Tables with Data as attachment - sql-1.zip
1 - I allow user key-in the filter expression in [dbo].[crpt_LembaranImbangan_2_Param_2]. So the data as follow,
idx| column_Nme |Comparison_Operator_Code |what_Value
--------------------------------------------------------------------------------------
-2147483648chart_code >= 32000
-2147483647chart_code <= 36000
-2147483646chart_code < 33***
-2147483645chart_code != 37000
2 - Above filter expression will be used to built Dynamic Select Statement and query into [dbo].[crpt_LembaranImbangan_1]
My question is
1- Did my Table Design to store Filter Expression is acceptable? The Filter Expression Table is [dbo].[crpt_LembaranImbangan_2_Param_2]
2 - If [dbo].[crpt_LembaranImbangan_2_Param_2] is acceptable, how my Dynamic Select Statement and query into [dbo].[crpt_LembaranImbangan_1] looks like? We assume the Filter Expression Combination is OR
3- If I allow user combine the Filter Expression using OR or AND, How my [dbo].[crpt_LembaranImbangan_2_Param_2] looks like?
Please help
-
Ummm... answering your questions requires understanding exactly what such a dynamic query is supposed to accomplish, and I don't see that stated anywhere in your post. Also, by what standard do we judge adequacy on the filter expression? Adequate for what purpose? We really have nothing to go on but your data, and I don't see any obvious connections. They may be obvious to you, but we have no idea what this data is for.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Ok Sir. Will try to create more excellent story telling and what to achieve
Sorry for inconvenience
-
If you need to combine comparison terms using arbitrary OR , AND logical operators, consider
CREATE TABLE [dbo].[crpt_LembaranImbangan_2_Param_2]([idx] [int] IDENTITY(-2147483648,1) NOT NULL,
[left_bracket] [bit] NULL,
[column_Nme] [nvarchar](100) NULL,
[Comparison_Operator_Code] [nvarchar](50) NULL,
[what_Value] [nvarchar](5) NULL,
[right_bracket] [bit] NULL,
[Logial_Op_Code] [nvarchar](3) NULL
)
For example
( col1 >= 100 NULL ORNULL col12 < 200 ) AND
NULL col3 = 'ABC' NULL NULL
Your user should be educated so they feel comfortable with logical expressions.
-
Consider referring to a table [OperatorPrecedence] ([Level] tinyint, [Operator] varchar) - see https://msdn.microsoft.com/en-us/library/ms190276.aspx.
And (from the same URL) consider referring to a table [NestedParenthesis]([NestingLevel] tinyint, [Parenthesis] char(1)) .
Agreed - your users will need training. But I think it would be easier if building a query was coded at the client. This post's image implies the query is being built at the client, and what you want to do is log a user's choices. Either way, you should read http://sommarskog.se/dyn-search.html.
-
sgmunson (11/18/2015)
Ummm... answering your questions requires understanding exactly what such a dynamic query is supposed to accomplish, and I don't see that stated anywhere in your post. Also, by what standard do we judge adequacy on the filter expression? Adequate for what purpose? We really have nothing to go on but your data, and I don't see any obvious connections. They may be obvious to you, but we have no idea what this data is for.I improve the story telling. Looks here ...
I give you all - The table and the data scripted as below or see attachment
/****** Object: Table [dbo].[test_Figure1] Script Date: 11/21/2015 15:48:15 ******/
DROP TABLE [dbo].[test_Figure1]
GO
/****** Object: Table [dbo].[test_Figure1_Expression] Script Date: 11/21/2015 15:48:15 ******/
DROP TABLE [dbo].[test_Figure1_Expression]
GO
/****** Object: Table [dbo].[test_Figure1_Expression] Script Date: 11/21/2015 15:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test_Figure1_Expression](
[idx] [int] IDENTITY(1,1) NOT NULL,
[batch_Id] [uniqueidentifier] NULL,
[column_Nme] [nvarchar](100) NULL,
[Comparison_Operator_Code] [nvarchar](50) NULL,
[what_Value] [nvarchar](5) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[test_Figure1_Expression] ON
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (1, N'3e6b28da-bc3a-4d33-b6a0-2ced41d7545e', N'chart_code', N'=', N'35000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (2, N'3e6b28da-bc3a-4d33-b6a0-2ced41d7545e', N'chart_code', N'>', N'36000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (3, N'3e6b28da-bc3a-4d33-b6a0-2ced41d7545e', N'chart_code', N'>=', N'38000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (4, N'3e6b28da-bc3a-4d33-b6a0-2ced41d7545e', N'chart_code', N'<=', N'38790')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (5, N'3e6b28da-bc3a-4d33-b6a0-2ced41d7545e', N'chart_code', N'=', N'34***')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (6, N'3e6b28da-bc3a-4d33-b6a0-2ced41d7545e', N'chart_code', N'>', N'34***')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (7, N'3e6b28da-bc3a-4d33-b6a0-2ced41d7545e', N'chart_code', N'<', N'37***')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (8, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'chart_code', N'>', N'32***')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (9, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'chart_code', N'<', N'34***')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (10, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'chart_code', N'>=', N'37000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (11, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'chart_code', N'<=', N'39000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (12, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'chart_code', N'>=', N'31000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (13, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'chart_code', N'<=', N'37000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (14, N'9b8c1c3a-b04b-4f6e-85ff-97c614c7fbf9', N'chart_code', N'>', N'31***')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (15, N'9b8c1c3a-b04b-4f6e-85ff-97c614c7fbf9', N'chart_code', N'<', N'33***')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (16, N'9b8c1c3a-b04b-4f6e-85ff-97c614c7fbf9', N'chart_code', N'>=', N'36000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (17, N'9b8c1c3a-b04b-4f6e-85ff-97c614c7fbf9', N'chart_code', N'>', N'31000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (18, N'9b8c1c3a-b04b-4f6e-85ff-97c614c7fbf9', N'chart_code', N'<', N'33000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (19, N'8e38f536-4747-4340-8c8c-ba936edb7741', N'chart_code', N'<', N'44444')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (20, N'182a559f-4381-4b26-8456-fb6b865e7133', N'chart_code', N'>', N'33333')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (21, N'a6ac9723-0410-4066-a65b-3a7d2dd2fac9', N'chart_code', N'=', N'dsds')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (22, N'c39d7922-ffc7-44c7-9032-b58b5d75d018', N'chart_code', N'<', N'70000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (23, N'582f0424-9eab-4e61-93ca-4f2a818ef3d5', N'chart_code', N'<', N'4433')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (24, N'dfdb5a1e-8595-4684-9d51-6d3ba73ebb7c', N'chart_code', N'=', N'3182')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (25, N'366b6461-cc14-4941-9e9d-2f3ef0b284c5', N'chart_code', N'=', N'6182')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (26, N'd162d770-4279-4e39-bccf-03db6732979e', N'chart_code', N'>=', N'31000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (27, N'd162d770-4279-4e39-bccf-03db6732979e', N'chart_code', N'<=', N'34000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (28, N'd162d770-4279-4e39-bccf-03db6732979e', N'chart_code', N'>=', N'34000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (29, N'd162d770-4279-4e39-bccf-03db6732979e', N'chart_code', N'<=', N'36000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (30, N'd162d770-4279-4e39-bccf-03db6732979e', N'chart_code', N'>=', N'37000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (31, N'd162d770-4279-4e39-bccf-03db6732979e', N'chart_code', N'<=', N'39000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (32, N'd2389ee3-57a1-4463-9e36-ea66d1199850', N'chart_code', N'>=', N'31000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (33, N'd2389ee3-57a1-4463-9e36-ea66d1199850', N'chart_code', N'<=', N'33000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (34, N'd2389ee3-57a1-4463-9e36-ea66d1199850', N'chart_code', N'>=', N'33000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (35, N'd2389ee3-57a1-4463-9e36-ea66d1199850', N'chart_code', N'<=', N'37000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (36, N'd2389ee3-57a1-4463-9e36-ea66d1199850', N'chart_code', N'>=', N'34000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (37, N'd2389ee3-57a1-4463-9e36-ea66d1199850', N'chart_code', N'<=', N'38000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (38, N'2be68073-b279-4f9e-b258-81d284124c03', N'chart_code', N'>=', N'25000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (39, N'2be68073-b279-4f9e-b258-81d284124c03', N'chart_code', N'<=', N'35000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (40, N'2be68073-b279-4f9e-b258-81d284124c03', N'chart_code', N'>=', N'35000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (41, N'2be68073-b279-4f9e-b258-81d284124c03', N'chart_code', N'<=', N'37000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (42, N'2be68073-b279-4f9e-b258-81d284124c03', N'chart_code', N'>=', N'36000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (43, N'2be68073-b279-4f9e-b258-81d284124c03', N'chart_code', N'<=', N'45000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (44, N'8f2a6334-a94f-473e-8d8a-c13bb06db6f5', N'chart_code', N'>=', N'20000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (45, N'8f2a6334-a94f-473e-8d8a-c13bb06db6f5', N'chart_code', N'<=', N'35000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (46, N'5a0fa7d9-68c2-4714-a39a-df16b16965e4', N'chart_code', N'>=', N'20000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (47, N'5a0fa7d9-68c2-4714-a39a-df16b16965e4', N'chart_code', N'<=', N'35000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (48, N'5a0fa7d9-68c2-4714-a39a-df16b16965e4', N'chart_code', N'>=', N'36000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (49, N'5a0fa7d9-68c2-4714-a39a-df16b16965e4', N'chart_code', N'<=', N'45000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (50, N'8b6b9729-a93e-496c-b79c-074d20e72c09', N'chart_code', N'>=', N'30000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (51, N'8b6b9729-a93e-496c-b79c-074d20e72c09', N'chart_code', N'<=', N'41000')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (52, N'7b016f49-d229-48dd-a4d1-09aab026926a', N'chart_code', N'>', N'31***')
INSERT [dbo].[test_Figure1_Expression] ([idx], [batch_Id], [column_Nme], [Comparison_Operator_Code], [what_Value]) VALUES (53, N'7b016f49-d229-48dd-a4d1-09aab026926a', N'chart_code', N'<', N'39***')
SET IDENTITY_INSERT [dbo].[test_Figure1_Expression] OFF
/****** Object: Table [dbo].[test_Figure1] Script Date: 11/21/2015 15:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test_Figure1](
[idx] [int] IDENTITY(1,1) NOT NULL,
[batch_Id] [uniqueidentifier] NULL,
[chart_code] [nvarchar](5) NULL,
[debit_amt_year1] [decimal](14, 2) NULL,
[credit_amt_year1] [decimal](14, 2) NULL,
[debit_amt_year2] [decimal](14, 2) NULL,
[credit_amt_year2] [decimal](14, 2) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[test_Figure1] ON
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (1, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'32000', CAST(5109337.13 AS Decimal(14, 2)), CAST(5475.75 AS Decimal(14, 2)), CAST(5573964.18 AS Decimal(14, 2)), CAST(3357.53 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (2, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'32000', CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(104657.10 AS Decimal(14, 2)), CAST(37.27 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (3, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'32000', CAST(1104882.69 AS Decimal(14, 2)), CAST(230.57 AS Decimal(14, 2)), CAST(1215586.82 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (4, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'32000', CAST(894275.67 AS Decimal(14, 2)), CAST(340.10 AS Decimal(14, 2)), CAST(897668.07 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (5, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'32000', CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(6032.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (6, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'32000', CAST(798063.88 AS Decimal(14, 2)), CAST(97.74 AS Decimal(14, 2)), CAST(1674063.64 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (7, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'32000', CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(17747.67 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (8, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'32001', CAST(1133702.13 AS Decimal(14, 2)), CAST(2191.98 AS Decimal(14, 2)), CAST(1483576.66 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (9, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'34000', CAST(844457.78 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(853846.71 AS Decimal(14, 2)), CAST(1000.04 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (10, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'34003', CAST(2227579.68 AS Decimal(14, 2)), CAST(221.70 AS Decimal(14, 2)), CAST(2601787.50 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (11, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'34010', CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(19889.79 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (12, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'34000', CAST(984078.05 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(998857.95 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (13, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'34000', CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(29075.86 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (14, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'34000', CAST(3649284.45 AS Decimal(14, 2)), CAST(30309.15 AS Decimal(14, 2)), CAST(4745800.41 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (15, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'34000', CAST(3011010.59 AS Decimal(14, 2)), CAST(18.50 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (16, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'34000', CAST(52797.85 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(39654.60 AS Decimal(14, 2)), CAST(3433.65 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (17, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'34023', CAST(134979.60 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(19935.10 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (18, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81483', CAST(269180.90 AS Decimal(14, 2)), CAST(2416.54 AS Decimal(14, 2)), CAST(50440.19 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (19, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81483', CAST(52182.95 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (20, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81483', CAST(1324522.01 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(892861.20 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (21, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'37600', CAST(110160.35 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(132678.85 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (22, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'37612', CAST(2070991.11 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(1035714.40 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (23, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'37600', CAST(642762.78 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(190289.58 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (24, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'37600', CAST(815068.35 AS Decimal(14, 2)), CAST(9310.55 AS Decimal(14, 2)), CAST(9310.55 AS Decimal(14, 2)), CAST(68927.50 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (25, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'37600', CAST(548437.33 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(110390.24 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (26, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'37000', CAST(1087174.19 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(256615.90 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (27, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'37000', CAST(187828.55 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(12195.25 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (28, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'37000', CAST(66923.60 AS Decimal(14, 2)), CAST(3288.11 AS Decimal(14, 2)), CAST(3288.11 AS Decimal(14, 2)), CAST(97079.20 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (29, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81483', CAST(1692749.23 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(243203.50 AS Decimal(14, 2)), CAST(6649.99 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (30, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'37000', CAST(1746435.75 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(113601.35 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (31, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'37000', CAST(2443442.46 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(49385.35 AS Decimal(14, 2)), CAST(19904.96 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (32, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'37000', CAST(223573.95 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(74177.40 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (33, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'37000', CAST(898051.40 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(5967.86 AS Decimal(14, 2)), CAST(195220.35 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (34, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81483', CAST(983099.60 AS Decimal(14, 2)), CAST(17791.18 AS Decimal(14, 2)), CAST(26969.58 AS Decimal(14, 2)), CAST(111258.35 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (35, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81483', CAST(296439.40 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(59937.44 AS Decimal(14, 2)), CAST(61638.80 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (36, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81483', CAST(410323.11 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(268482.45 AS Decimal(14, 2)), CAST(96508.24 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (37, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81483', CAST(1877801.26 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(167380.57 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (38, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81483', CAST(1300615.54 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(292184.40 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (39, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81483', CAST(323534.40 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(51846.95 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (40, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81483', CAST(210979.90 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(55111.80 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (41, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'39000', CAST(224845.90 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(103890.48 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (42, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'39000', CAST(38211.80 AS Decimal(14, 2)), CAST(27012.25 AS Decimal(14, 2)), CAST(43294.91 AS Decimal(14, 2)), CAST(41340.10 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (43, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81483', CAST(199468.06 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(17868.53 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (44, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81483', CAST(78482.33 AS Decimal(14, 2)), CAST(39451.10 AS Decimal(14, 2)), CAST(198724.63 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (45, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'39000', CAST(569375.90 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(120188.60 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (46, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81485', CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(215280.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (47, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'39000', CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(36334984.28 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (48, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81501', CAST(1451629.00 AS Decimal(14, 2)), CAST(1556125.00 AS Decimal(14, 2)), CAST(313798.00 AS Decimal(14, 2)), CAST(151444.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (49, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81503', CAST(340744.00 AS Decimal(14, 2)), CAST(305774.00 AS Decimal(14, 2)), CAST(617115.00 AS Decimal(14, 2)), CAST(674996.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (50, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'39010', CAST(47960.00 AS Decimal(14, 2)), CAST(47960.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (51, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'39012', CAST(164375.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (52, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81514', CAST(197980.00 AS Decimal(14, 2)), CAST(374275.00 AS Decimal(14, 2)), CAST(379806.00 AS Decimal(14, 2)), CAST(367286.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (53, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81519', CAST(42272.00 AS Decimal(14, 2)), CAST(73314.00 AS Decimal(14, 2)), CAST(42568.00 AS Decimal(14, 2)), CAST(2573.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (54, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'39015', CAST(4235.00 AS Decimal(14, 2)), CAST(4235.00 AS Decimal(14, 2)), CAST(1590.00 AS Decimal(14, 2)), CAST(1590.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (55, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81561', CAST(1060.00 AS Decimal(14, 2)), CAST(1590.00 AS Decimal(14, 2)), CAST(530.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (56, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'39023', CAST(277383.00 AS Decimal(14, 2)), CAST(279116.00 AS Decimal(14, 2)), CAST(479635.73 AS Decimal(14, 2)), CAST(477902.73 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (57, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'39100', CAST(15930.00 AS Decimal(14, 2)), CAST(15930.00 AS Decimal(14, 2)), CAST(530.00 AS Decimal(14, 2)), CAST(530.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (58, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81592', CAST(35546.00 AS Decimal(14, 2)), CAST(76651.00 AS Decimal(14, 2)), CAST(106707.00 AS Decimal(14, 2)), CAST(69047.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (59, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81593', CAST(62687.50 AS Decimal(14, 2)), CAST(76157.50 AS Decimal(14, 2)), CAST(15847.00 AS Decimal(14, 2)), CAST(2377.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (60, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81603', CAST(7873.00 AS Decimal(14, 2)), CAST(7873.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (61, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81623', CAST(25780.00 AS Decimal(14, 2)), CAST(25780.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (62, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81627', CAST(69721.00 AS Decimal(14, 2)), CAST(80945.00 AS Decimal(14, 2)), CAST(15355.00 AS Decimal(14, 2)), CAST(4131.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (63, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81648', CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(1650.00 AS Decimal(14, 2)), CAST(1650.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (64, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81655', CAST(7135.00 AS Decimal(14, 2)), CAST(7135.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (65, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81663', CAST(2460.00 AS Decimal(14, 2)), CAST(2460.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (66, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81665', CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(7776.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (67, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81665', CAST(27759.00 AS Decimal(14, 2)), CAST(30965.00 AS Decimal(14, 2)), CAST(20966.00 AS Decimal(14, 2)), CAST(9984.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Figure1] ([idx], [batch_Id], [chart_code], [debit_amt_year1], [credit_amt_year1], [debit_amt_year2], [credit_amt_year2]) VALUES (68, N'63e23aef-023b-4c19-9ed4-920ee70f55ae', N'81668', CAST(26615.40 AS Decimal(14, 2)), CAST(30335.40 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
SET IDENTITY_INSERT [dbo].[test_Figure1] OFF
Now I'm facing the difficulty to built the Dynamic SQL. Here the logic,
1- Select the data from test_Figure1 where batch_Id='63e23aef-023b-4c19-9ed4-920ee70f55ae'
select * from test_Figure1 where batch_Id='63e23aef-023b-4c19-9ed4-920ee70f55ae'
2-Find the Filter Expression from test_Figure1_Expression where batch_Id='63e23aef-023b-4c19-9ed4-920ee70f55ae'
select * from test_Figure1_Expression where batch_Id='63e23aef-023b-4c19-9ed4-920ee70f55ae'
3- Now, I need help to built Dynamic SQL that combine Query (1) and Query (2)
May be, something like
select *
from test_Figure1 t1 inner join test_Figure1_Expression t2 on t1.batch_Id=t2.batch_Id
Then, the Dynamic SQL will produce as follow
select * from test_Figure1
where 1=1
/*this is the filter expression from test_Figure1_Expression*/
and chart_code>32***
and chart_code<34***
and chart_code>=37000
and chart_code<=39000
and chart_code>=31000
and chart_code<=37000
Please help. I'm stuck
-
SELECT 'select * from test_Figure1 where ' +STUFF
(
(
SELECT ' AND ' + [column_Nme] + ' ' +
[Comparison_Operator_Code] +
' ''' + [what_Value] + ''''
FROM [test_Figure1_Expression]
WHERE [batch_Id]='63e23aef-023b-4c19-9ed4-920ee70f55ae'
ORDER BY [idx]
FOR XML PATH (''), TYPE
).value('.', 'NVARCHAR(max)')
, 1, 5, ''
)
-
Tq to all
-
Qira,
really it can be a beginning of rather long road. Next step is to take target column data type into account. Both at query generation and at user input. Consider user is about to include a comparison term which refers [test_Figure1].[debit_amt_year1]. Friendly front-end must help him and prevent illegal constant input.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply