Building a Stored Procedure - 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

Old Forums

CLOSED - General SQL Server

Building a Stored Procedure

Author

Topic

sqlwanab
Starting Member

22 Posts

Posted - 2002-10-15 : 10:13:55

I have had some experience building sp's over the last year but have come across a level complexity that I would need some help on.

I have a table that contains 7 mos of data. The columns involved in this query include:
Component
Activity
Hours

What I want to be able to do is build a query that will capture the data in the following format:


Component   AN
-------------------------------------------------------------
Activity                Total Records               Hours
 ADM                        21                       165
 CUT                         7                       172
 INSP-CODE                   1                         1
 MAINT                      17                       140
-------------------------------------------------------------
                                                SUM  478

I also need to create a loop so that the output loops through all of the components in this fashion.

Can anyone help me on how to assemble my stored procedure or point me in the right direction?

Thank you very much.

Tim

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-15 : 10:20:47

Loop? ... no ... set based is the true path ...


select
    activity,
    count(*) as 'Total Records',
    sum(hours) as hours
from
    sqlwanab
group by
    activity
compute
    sum(sum(hours))

Jay White
{0}

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-15 : 10:23:43

Take a look at ROLLUP and CUBE in Books Online. These are T-SQL extenstions that generate summary data for groups. Something like this should work:

SELECT Component, Activity, Count(*) AS [Total Records], Sum(Hours) AS Hours
FROM MyTable
GROUP BY Component, Activity WITH ROLLUP
ORDER BY Component, Activity

You'll get the following:

Component    Activity     Total Records     Hours
AN           ADM                     21       165
AN           CUT                      7       172
AN           INSP-CODE                1         1
AN           MAINT                   17       140
AN           NULL                    46       478

You really can't get the output exactly like you described using only SQL, and it's really not worth the effort to try. That kind of formatting is best done using a report generator.

sqlwanab
Starting Member

22 Posts

Posted - 2002-10-15 : 13:03:27

I noticed that when I run the SP you provided (which is awesome, thanks), I get 2 Null rows on the top. Like this:

[code]
Component Activity Records Total Hours
------------------------------------------------------------------
<NULL> 58 696
<NULL> 58 696
1 1 21 165
1 2 5 108
1 3 12 87
-------------------------------------------------------------------

I know I need the total value displayed by the null column, but the problem is that I can't seem to get the result to show up without them on the top.

Any ideas. Perhaps this is what you mean by needing to use a report writer or something to handle display issues.

Thanks for your thgoughts.

Tim

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-15 : 14:22:24

It's because of its GROUP BY on two columns, each Null represents a ROLLUP of each group. Since there only appears to be one component value the two rows summarize the same way. There is a function called GROUPING() that allows you to identify the summary rows, and you can eliminate the excess ones using a HAVING clause:

SELECT Component, Activity, Count(*) AS [Total Records], Sum(Hours) AS Hours
FROM MyTable
GROUP BY Component, Activity WITH ROLLUP
HAVING GROUPING(Component)=0 --This will eliminate the grand total for all component values
ORDER BY Component, Activity

Books Online has some examples of ROLLUP and GROUPING, play around with them to get a feel for how they work. You can do some fancy stuff with it, and combined with a subquery you can probably get very close to the kind of output you want. It's just that SQL is not a formatting/reporting language so it's not well suited to generating fancy output. Using Access or Crystal Reports will allow you to get what you want far more easily.

sqlwanab
Starting Member

22 Posts

Posted - 2002-10-16 : 11:44:39

Rob,

Is there a way I can insert summary rows for each grouping of components rather than having just a grand total?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-16 : 11:52:10

Yeah, it's a matter of using the GROUPING function on either the Component or Activity column. Use the HAVING clause to restrict its values (might want to add GROUPING to the SELECT list, so you can see which rows are summary rows (1) or not (0)) If you see a Null in those columns it is probably a summary row, but GROUPING will tell you for certain.

Like I said, if you play around with it you'll get a better appreciation for how it works; just try testing various combinations. It's a difficult function to explain unfortunately, it's a lot easier to show/see than to tell.

sqlwanab
Starting Member

22 Posts

Posted - 2002-10-17 : 13:11:13

Rob,

I managed to get a good result with the following SP:
SELECT
Case GROUPING(LIEF_tbl.Activity)WHEN 0
THEN LIEF_tbl.Activity ELSE 20 END AS Activity,
Count(*)AS[Total Records], SUM(LIEF_tbl.Hours) AS Hours
FROM LIEF_tbl
WHERE Component=1
Group By LIEF_tbl.Activity With Rollup

Here is the result:


Activity   Records   Hours
1	     22	      168
3	      5	      108
7	      1	        1
12	      1	        3
15	     16	      109
16	     13	      298
19	      1	       12
20	     59	      699

However, I can't seem to figure out how to get my Inner Joins to work. Here is the SP with Inner Joins:


SELECT Activity.Abbrev As Activity, 
  Case GROUPING(LIEF_tbl.Activity)WHEN 0
THEN LIEF_tbl.Activity ELSE 20 END AS Activity,     
  Count(*)AS[Total Records], SUM(LIEF_tbl.Hours) AS Hours
FROM LIEF_tbl INNER JOIN Activity ON LIEF_tbl.Activity = Activity.ID
WHERE Component=1
Group By LIEF_tbl.Activity With Rollup

As you can most likely guess I get an error yelling about the fact that Activity.Abbrev is not contained within the Group By Clause. But if I put it in there I get redundant NULL columns as well and 2 Activity columns:


ADM	1	22	168
NULL	1	22	168
CUT	3	5	108
NULL	3	5	108
INSP-CODE	7	1	1
NULL	7	1	1
INSP-PUBS	12	1	3
NULL	12	1	3
MAINT-S	15	16	109
NULL	15	16	109
RES	16	13	298
NULL	16	13	298
TEST-I	19	1	12
NULL	19	1	12
NULL	20	59	699

Do you know how I can get my inner joins to work so I can translate the integer values from the primary table to the text values from the child table?

Thanks for all your help.

Sincerely,

Tim

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-17 : 13:56:23

Can you show me some sample data from both tables (maybe 10 rows each) and show the exact output you want? I think the following will work but I can't guarantee it:

SELECT Activity.Abbrev As Activity,
Case GROUPING(LIEF_tbl.Activity)WHEN 0
THEN LIEF_tbl.Activity ELSE 20 END AS Activity,
Count(*)AS[Total Records], SUM(LIEF_tbl.Hours) AS Hours
FROM LIEF_tbl INNER JOIN Activity ON LIEF_tbl.Activity = Activity.ID
WHERE Component=1
Group By Activity.Abbrev, LIEF_tbl.Activity With Rollup
HAVING GROUPING(Activity.Abbrev)=0

If that doesn't work, try changing the red part to use GROUPING(LIEF_tbl.Activity)=0. You will get multiple summary rows but the HAVING/GROUPING clause allows you to eliminate them.

sqlwanab
Starting Member

22 Posts

Posted - 2002-10-17 : 14:28:32

Rob,

Here is data from the Activity Table:


ID	Abbrev
1	ADM
2	BUILD
3	CUT
4	DES
5	FIX
6	IFE
7	INSP-CODE
8	INSP-FL
9	INSP-FS
10	INSP-IFE
11	INSP-LS
12	INSP-PUBS
13	INSP-TP TESTPLAN
14	MAINT-I
15	MAINT-S
16	RES
17	TEST-A
18	TEST-F
19	TEST-I
20	TEST-S
21	Total

Here is data from the LIEF_tbl:


ID	UID	WED	LineItem	Component	Activity	Hours
2583	99569	10/11/2002	14	3	5	2
2585	99450	10/11/2002	14	3	16	32
2586	99450	10/11/2002	14	3	1	8

The columns don't line up above but I'm sure you can figure it out.

I tried the solutions you provided but niehter produced the result I was looking for.

Here is a copy of the reult I am attempting to achieve:

Activity Records Hours
ADM 22 168
CUT 5 108
INSP-CODE 1 1
INSP-PUBS 1 3
MAINT-S 16 109
RES 13 298
TESTi 1 12
(Total) 59 699

Thanks again for your help!!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-17 : 14:42:22

SELECT IsNull(A.Abbrev, 'Total') As Activity,
Count(*) AS Records,
Sum(L.Hours) AS Hours
FROM LIEF_tbl L INNER JOIN Activity A ON L.Activity = A.ID
WHERE L.Component=1
GROUP BY A.Abbrev WITH ROLLUP
ORDER BY GROUPING(A.Abbrev), A.Abbrev

That should do it, or get you damn close. If not, post the output it does give you and I'll try again.

sqlwanab
Starting Member

22 Posts

Posted - 2002-10-17 : 14:54:15

Unbelievable! You are the man!

Thank you so very much for hanging in there with me.

Sincerely,

Tim

[/quote]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources