Reporting Query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Reporting Query question

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-10-06 : 07:15:25

I think I may be trying to do the impossible! I have some data in the format

Weeks Type
----------- --------------------
0 G
20 N
5 U
5 G
1 G
0 G
3 G
2 U
500 G
NULL N
-1 N

Weeks is the number of weeks it took to perform a function, the type of function being in the second column. There are only three types of "Type" possible - U,G, and N. The number of weeks could however be almost anything including negatives and nulls - I think the best thing to do with the Nulls is to change them to 0 (which I can do).

My problem is that I need this data in a specific format so that I can chart it and generate aggregates.

What I need is the data in rows by Type with the number of weeks as the columns and a count in the appropriate cells. So for the above data I would have

<0 0 1 2 3 ... n (in this case 5) >n Mean weeks
U 0 0 0 1 0 ... 1 0 3.5
G 0 2 1 1 3 ... 1 1 84.8
N 1 1* 0 0 0 ... 0 1 6.3

*This is the one that was null

Ideally I would like the value n to be a parameter. Also the <0 and >n are definitely not essentials but would be nice.

I've looked all over and can't see how the examples I found would apply to this or would achieve what I need. Though I'm sure I'm missing something really obvious. Anyone have any pointers?

On a secondary issue, how do I turn on HTML so that I can lay this out nicer?

Many thanks

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 07:27:17

Isn't this just a cross tab?

SELECT [<0] = SUM(CASE WHEN Weeks < 0 THEN 1 ELSE 0 END),
[0] = SUM(CASE WHEN COALESCE(Weeks, 0) = 0 THEN 1 ELSE 0 END),
[1] = SUM(CASE WHEN Weeks = 1 THEN 1 ELSE 0 END),
...
[>5] = SUM(CASE WHEN Weeks > 5 THEN 1 ELSE 0 END)

You could also do:

-- Baby bear
SELECT '<0', COUNT(*)
FROM MyTable
WHERE Weeks < 0
-- Daddy bear
UNION ALL
SELECT '>n', COUNT(*)
FROM MyTable
WHERE Weeks > @intMaxWeeks
-- Mummy bear
UNION ALL
SELECT CONVERT(varchar(20), Weeks), COUNT(*)
FROM MyTable
WHERE COALESCE(Weeks, 0) BETWEEN 0 AND @intMaxWeeks
GROUP BY CONVERT(varchar(20), Weeks)

and stick that in your dynamic crosstab!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources