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.the current output html table
LVL 33
Robberbaron (robr)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
PortletPaulEE Topic AdvisorCommented:
I' won't provide a full re-write of that, but you could do it with fewer queries I believe by counting the contents of CASE EXPRESSIONS
e.g.
SELECT 
  COUNT(*) as completed_BIFs
, COUNT(
        CASE WHEN DATEDIFF(Day,M.ActionedDate ,'" & TodaysDate & "') >= 7 
                  AND DATEDIFF(Day,M.ActionedDate ,'" & TodaysDate & "') >= 7 
                  AND DATEDIFF(Day,M.ActionedDate ,'" & TodaysDate & "') < 14 
                  AND Completed IS NULL
             THEN Q.BIF_ID
        END
        ) as Outstanding_LT7
, COUNT(
        CASE WHEN DATEDIFF(Day,M.ActionedDate ,'" & TodaysDate & "') >= 14 
                  AND DATEDIFF(Day,M.ActionedDate ,'" & TodaysDate & "') >= 7 
                  AND DATEDIFF(Day,M.ActionedDate ,'" & TodaysDate & "') < 21 
                  AND Completed IS NULL 
             THEN Q.BIF_ID
        END
        ) as Outstanding_LT14
FROM BIF_QM_Action Q
INNER JOIN BIF_Main M  ON Q.BIF_ID = M.BIF_ID 
WHERE M.Stage = 5 
AND Q.QC = '" & QC_Name & "' 
AND " & submitrange & ";"

Open in new window


So instead of having to re-run a query just to do a count, use a case expression. Note that COUNT() only increments if the item is not null.

A common alternate for this is SUM(), like like this:
, SUM(
        CASE WHEN DATEDIFF(Day,M.ActionedDate ,'" & TodaysDate & "') >= 7 
                  AND DATEDIFF(Day,M.ActionedDate ,'" & TodaysDate & "') >= 7 
                  AND DATEDIFF(Day,M.ActionedDate ,'" & TodaysDate & "') < 14 
                  AND Completed IS NULL
             THEN 1
             ELSE 0
        END
        ) as Outstanding_LT7

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robberbaron (robr)Author Commented:
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.
PortletPaulEE Topic AdvisorCommented:
:-)
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)Author Commented:
worked well. even able to paramatize query properly
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.