|
|
Posted by Erland Sommarskog on 10/02/07 22:02
bobc (bcanavan@fmbnewhomes.com) writes:
> Activites(1-n) in a single grid are not all pulled from the same table
> or view, and some of the criteria would differ. So I don't believe a
> GROUP BY clause on a single dataset would fit the problem.
Nevertheless, the procedure you post has an input parameter. If you need
to retrieve data for more than one code, you need to make multiple calls,
which is not effecient.
> My "procedural language programmer" solution would be to open one
> connection to the db and grab all the information at once, retrieving
> all the activity rows in individual activity parameters. I could then
> parse each parameter value into the appropriate bins with vb behind
> the page.
Really why you go for your lists, I don't know. You could have a single
procedure that reads all tables and then returns multiple result sets
that you receive in a dataset of datatables. At least you minimize the
network roundtrips.
> I use bin variables @d1-@d5 rather than literals 1, 7, 20, 25, 60
> because I'm hoping there is a way to programmatically vary the number
> and values of bin boundaries, through input parameters or some other
> means, and make this thing elegant.
That's not that easy. A SELECT query returns a table, and a table has
fixed number of columns, and each describes a distinct entity. To have
a variable number of columns or variably named columns, you need to
engage in dynamic SQL. Which may not be a bad choice for this task,
presuming that you can deal with the performance issues. But running it
all in T-SQL is not that fun; VB .Net might be a better venue.
But there is another approach: don't return columns return rows. Here is
a simple example:
SELECT P.CommunityCode, b.d1,
SUM(CASE WHEN DATEDIFF(day, P.StatusChangeDate, CURRENT_TIMESTAMP)
BETWEEN b.d1 AND coalesce(b.d2, 10000000)
THEN 1
ELSE 0
END)
FROM Prospects P
CROSS JOIN (SELECT d1 = 1, d2 = 6
UNION ALL
SELECT 7, 19
UNION ALL
SELECT 20, 24
UNION ALL
SELECT 25, 59
UNION ALL
SELECT 60, NULL) AS b
WHERE P.ProspectStatus = 'L'
GROUP BY P.CommunityCode, b.d1
Here I have put the date intervals in a derived table, but I guess you
can see where this leads: put the intervals in a real table, and have
it configurable.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|