its a huge database with many tables having relationships with each other.

system is stable with user input in the UI level.

database designed by a certified DBA. Its not easy to comment on design without looking at the complete database design and complicacy. I also have least control altering any database design changes here.

Now having said these ... I understand if is an alternative way out...but I'm looking out possibilities for a better approach.

Is not that going to bring many IF's here ? Can we think about minimizing IF here ?

How you are planning to mark the range ?

cpradio
—
2012-10-25T15:57:20Z —
#6

Personally, due to your limitations, I'd argue going on a software approach instead of a query approach.

Let the UI collect the start and ending months, but return the entire 12 months back in the query and then let the programming language figure out which columns to add together. Otherwise you are looking at a very bulky, hard to maintain stored procedure.

windowsxp
—
2012-10-25T16:03:17Z —
#7

cpradio said:

Personally, due to your limitations, I'd argue going on a software approach instead of a query approach.

Let the UI collect the start and ending months, but return the entire 12 months back in the query and then let the programming language figure out which columns to add together. Otherwise you are looking at a very bulky, hard to maintain stored procedure.

I liked this approach. Yes. I do have a control on JSP / Java side . I hold UI data in a java variable.

I'm not clear ... are you suggesting to build query in java side and then pass the query to the stored procedure ? OR are you saying to collect how many months needs to be passed to the store procedure ?

where the select query will built up ?

Please clarify .

cpradio
—
2012-10-25T16:09:23Z —
#8

I am saying, let the UI collect the start and end month, run a generic query that returns all months, then let the code loop through the results and sum up the appropriate columns based on the start and end month.

You could have the UI build the query too (if that is acceptable in your work environment) and run an ad-hoc query instead of a stored procedure. Either way, doing this in a stored procedure (in my opinion) is a mistake due to the restraints on the table design.

windowsxp
—
2012-10-25T16:22:18Z —
#9

cpradio said:

I am saying, let the UI collect the start and end month, run a generic query that returns all months, then let the code loop through the results and sum up the appropriate columns based on the start and end month.

No. This won't be possible and inconvenient in my work environment... because I am sending output values directly to a Report Designer which displays data in PDF/Word/Excel format in the UI finally....so have to do it in the Stored Procedure.

You could have the UI build the query too (if that is acceptable in your work environment) and run an ad-hoc query instead of a stored procedure. Either way, doing this in a stored procedure (in my opinion) is a mistake due to the restraints on the table design.

I am thinking like this ...

let the UI build a Select query and pass this query to the stored procedure....inside stored procedure I can use PREPARED STMT to run a query and get the output.

I still have the check though if this fits in the present set up

my comments in the above

cpradio
—
2012-10-25T16:47:11Z —
#10

Okay. Keep in mind you may be opening up a security issue with utilizing a stored procedure to accept a query. As a developer could send whatever query they would want through that stored procedure and depending on the role associated to the stored procedure may have more access than they should.

If you only have them send in the SUM equation, you could likely concatenate the rest of the query so the stored procedure limits the return data to your specific table(s).

gk53
—
2012-10-25T19:11:02Z —
#11

this is what you need I created just sample table and added test data into

;with t as (select 1 as mnth, jan as cnt from #Tunionselect 2 as mnth, feb as cnt from #Tunionselect 3 as mnth, Mar as cnt from #Tunionselect 4 as mnth, Apr as cnt from #Tunionselect 5 as mnth, May as cnt from #Tunionselect 6 as mnth, June as cnt from #Tunionselect 7 as mnth, July as cnt from #Tunionselect 8 as mnth, Aug as cnt from #Tunionselect 9 as mnth, Sept as cnt from #Tunionselect 10 as mnth, oct as cnt from #Tunionselect 11 as mnth, nov as cnt from #Tunionselect 12 as mnth, dec as cnt from #T)select SUM(cnt) FROM tWHERE mnth BETWEEN @startMonth AND @endMonth

I declare variables (you should have them as stored procedure parameters) and give them values just for test

that produce result

total849

which you looking for.

r937
—
2012-10-25T19:36:24Z —
#12

gk53 said:

this is what you need ...

discombobulating the badly designed table on the fly -- i love it

:award:

cpradio
—
2012-10-25T20:12:11Z —
#13

gk53 said:

this is what you need I created just sample table and added test data into

This process assumes SQL Server right? As I don't think Oracle or MySQL support CTEs (I could be wrong)

r937
—
2012-10-25T20:21:02Z —
#14

CTEs can always be re-written as POS (plain old sql)

gk53
—
2012-10-25T20:58:58Z —
#15

So, change with to

select SUM(cnt) FROM(select 1 as mnth, jan as cnt from #Tunionselect 2 as mnth, feb as cnt from #Tunionselect 3 as mnth, Mar as cnt from #Tunionselect 4 as mnth, Apr as cnt from #Tunionselect 5 as mnth, May as cnt from #Tunionselect 6 as mnth, June as cnt from #Tunionselect 7 as mnth, July as cnt from #Tunionselect 8 as mnth, Aug as cnt from #Tunionselect 9 as mnth, Sept as cnt from #Tunionselect 10 as mnth, oct as cnt from #Tunionselect 11 as mnth, nov as cnt from #Tunionselect 12 as mnth, dec as cnt from #T) tWHERE t.mnth BETWEEN @startMonth AND @endMonth