Post reply

Need help on Dynamic T-SQL

  • November 16, 2015 at 10:09 pm

    #309691

    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

  • November 18, 2015 at 3:39 pm

    #1840852

    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)

  • November 18, 2015 at 9:55 pm

    #1840877

    Ok Sir. Will try to create more excellent story telling and what to achieve

    Sorry for inconvenience

  • November 19, 2015 at 6:33 am

    #1840960

    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 OR

    NULL col12 < 200 ) AND

    NULL col3 = 'ABC' NULL NULL

    Your user should be educated so they feel comfortable with logical expressions.

  • November 19, 2015 at 7:11 am

    #1840975

    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.

  • November 21, 2015 at 1:03 am

    #1841502

    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

  • November 21, 2015 at 1:48 pm

    #1841526

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

    )

  • November 21, 2015 at 7:10 pm

    #1841543

    Tq to all

  • November 23, 2015 at 1:25 am

    #1841597

    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