Bangalore .Net User Group

Hi All,

As per project design we have to save the sql query in the database table (say, SQL_Table) and this query will be taken into execution at the runtime.

For example,

 The Value_Table table design is as follows.

I should return the value, sum(Value) of UnqId in (1,2) - Value of UnqId in 3 for the particular BusD.

Looks simple? I am supposed save teh query with out the BusD condition. Bucase the BusD condtion ("and  BusD=@BusD") will be added up with my saved query and will be executed after the concatination. (Confusing?!?!)

For example, forget about the minus in my calculation. I would save the following text in my SQL table.

"declare @Num float

select @Num=sum(Value) from Value_Table where UnqId in (1,2)"

My calculation sp, would aceept the @BusD and in the sp, we have hard coded. "and BusD=@BusD". The hardcoded string will be concatenated with my SQL_Table record. So, the executing statements will be,

declare @Num float

select @Num=sum(Value) from Value_Table where UnqId in (1,2) and BusD=@BusD. So, I will get the proper result in @Num and return the same.

Now my issue starts with minus value. I mean, sum(value) if UnqId in (1,2) - Sum(Value) if UnqId in (3). I have to write a single statement to achieve this. I dont know how to do that.

I hope, I explained my issue clearly. 

Looking forward for the response.

Hi Muthu,

Please fin the following Query. Table name is Test1.

Sum of values and specific valus has been calculated saperately two sub queries.

Condition BusD=@BusD can be concatinated in the end of the query.

In the SQL statements where clause will be executed first. So we will have filtered values and upon which these two sub queries will be executed.

Please let me know if my understanding of problem is not correct.

DECLARE @Num AS INT

@Num = ((SELECT SUM([Value]) FROM Test1 WHERE UniqID in (1,2) AND BusD = tt.BusD)-(SELECT SUM([VALUE]) FROM Test1 WHERE UniqID = 3 AND BusD = tt.BusD))

FROM Test1 tt

WHERE BusD=@BusD

Thanks,

Gowtam

Thanks Gowtam it works.

We ahve come up with one another solution also,

FYI,

declare @Num1 float
select @Num1 = sum(case when UnqId in (3) then -1*value
else value
end )
from test1 where BusD =@BusD.