asked on

best way to create output from multiple queries

SQLServer2008 output using ASP.NET 4.5

I have inherited a internal web site app from 2002 and am in the process of updating it and then adding functionality.

a page currently displays the following table output correctly but is a pain to produce the output as it is 14 different queries for each row.

any thoughts on the best way to output the data for presentation. speed of processing is not an issue as only 500 records or so for each report. ease of rewriting the output cleanly is more important at this stage.

i have trimmed out the majority of fluff code.... each query is output individually using the same response.write code. QC_Name is a result of the top level query.

                        'Stage - Actions Required but not copmleted by QCs
                        SQLquery = "SELECT COUNT(*) as maxcount FROM BIF_QM_Action Q, BIF_Main M WHERE Q.BIF_ID = M.BIF_ID AND M.Stage >= 4 AND Q.QC='" & QC_Name & "' AND " & submitRange & ";"
                        recordset3 = GetFormsRS(SQLquery)
                        Response.Write("<td>" & DBStr(recordset3.Fields.Item("maxcount"))) & "</td>
                        recordset3.Close()

                        'Stage 5 - Completed BIFs
                        SQLquery = "SELECT COUNT(*) as maxcount FROM BIF_QM_Action Q, BIF_Main M WHERE Q.BIF_ID = M.BIF_ID AND M.Stage = 5 AND Q.QC = '" & QC_Name & "' AND " & submitrange & ";"

	
                        'ACTION OUTSTANDINGS <7
                        SQLquery = "SELECT COUNT(*) as maxcount FROM BIF_QM_Action B, BIF_Main M " & "WHERE B.BIF_ID = M.BIF_ID AND B.QC ='" & QC_Name & "' AND DATEDIFF(Day,M.ActionedDate ,'" & TodaysDate & "') < 7 " & "AND Completed IS NULL AND " & submitRange & ";"

                        'ACTION OUTSTANDINGS <14
                        SQLquery = "SELECT COUNT(*) as maxcount FROM BIF_QM_Action B, BIF_Main M " & "WHERE B.BIF_ID = M.BIF_ID AND B.QC ='" & QC_Name & "' " & "AND DATEDIFF(Day,M.ActionedDate ,'" & TodaysDate & "') >= 7 AND DATEDIFF(Day,M.ActionedDate ,'" & TodaysDate & "') < 14 " & "AND Completed IS NULL AND " & submitRange & ";"

                        'ACTION OUTSTANDINGS <21
                        SQLquery = "SELECT COUNT(*) as maxcount FROM BIF_QM_Action B, BIF_Main M " & "WHERE B.BIF_ID = M.BIF_ID AND B.QC ='" & QC_Name & "' " & "AND DATEDIFF(Day,M.ActionedDate ,'" & TodaysDate & "') >= 14 AND DATEDIFF(Day,M.ActionedDate ,'" & TodaysDate & "') < 21 " & "AND Completed IS NULL AND " & submitRange & ";"
	
                        'ACTION OUTSTANDINGS >21
                        SQLquery = "SELECT COUNT(*) as maxcount FROM BIF_QM_Action B, BIF_Main M " & "WHERE B.BIF_ID = M.BIF_ID AND B.QC ='" & QC_Name & "' AND DATEDIFF(Day,M.ActionedDate ,'" & TodaysDate & "') >= 21 " & "AND Completed IS NULL AND " & submitRange & ";"

Open in new window

I have thought of creating a temp table with the base query , then a SELECT FROM ( SELECT , select .. each query list) so that I get a proper multirow recordset with all the desired columns.Rather than doing cell by cell.

all thoughts and criticisms worthwhile as one option is to leave the code as is... it still works just hard to follow and change over 6 reports.

Ryan Chong

it seems that all of your queries are from tables:

BIF_QM_Action B
BIF_Main M

?

if yes, I'm thinking it can be grouped into just one single SQL statement.

if you want to keep the daily stats as a historical snapshot, then you probably can saved the data into a summary table, and then when you need it, just query to that table from your ASP.NET page.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robberbaron (robr)

ASKER

thanks for comments Paul.

that is the way i was thinking also though hadnt used the Case statements in that way.

It will still end up as a large query (13 case statements) but is easier to test and alter.

PortletPaul

:-)
there was a reason I didn't volunteer for the whole re-write

but you avoid 12 scans of the data, so it's worth it for sure.

Robberbaron (robr)

ASKER

worked well. even able to paramatize query properly