February 3, 2016 at 3:09 am
Hi,
I have a table
CREATE TABLE [dbo].[OP_Transaction](
[prod_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[productid] [numeric](18, 0) NULL,
[productname] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OS_GS_IdNo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OF_FeeHeadcode] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[accountno] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_OP_Transaction] PRIMARY KEY CLUSTERED
(
[prod_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
table with sample values
prod_id productid productname OS_GS_IdNo OF_FeeHeadcode accountno
1 1 Product 1 1,2,3,4,5 450, 451 A/C no 12345678
2 2 Product 2 1,2,3,4,5 452,453 A/C no 78965412
3 3 Product 3 6,7,8,9,10 450,451 A/C no 98745632
4 4 product 4 6,7,8,9,10 452,453, 454 A/C no 478521693
From the above table
OS_GS_IdNo = 8
i have string[] headcodes and string[] feeamt values .
headcodes[0] = 451
headcodes[1] = 453
headcodes[2] = 454
feeamt[0] = 1500
feeamt [1] = 800
feeamt [2] = 1200
if headcode value is 451 then its corresponding feeamt value (1500) and accountno (from table)should come
and if headcode values is 453 and 454 , its feeamt value should sum up (800+ 1200) (as it comes under same accountno) and get total feeamount and get account no from the table.
How to write query for that? Could anyone please help to solve this
February 3, 2016 at 4:29 am
I notice that you use the data type "text". This data type has been deprecated a long time ago. Please consider replacing it with varchar(max).
Can you repost the sample data as INSERT statements, please? That should save a lot of people a lot of typing.
Also, you want to return values such as 1500 and (800 + 1200) that are not even in this table. That's rather hard. If you want SQL Server to return that, then it must be in a table somewhere, not only in an array in your C# or VB.Net code.
If you want SQL Server to return "something" that you can use in the client code to get the correct final result, then please specify what SQL Server should return. Also clarify exactly how you intend the client to call the database, and in what form the result should be returned to the client (e.g. return parameters, result set, ...)
This is a SQL Server forum, so we can help you with that part. Ideally, we want to provide code that you can run in a query window and that returns the expected results in the query output pane; you can then lift that code to use it in your C#/VB.Net code.
Finally, I need more explanation on the results, since the data you posted plus the explanation doesn't help me understand the logic.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply