March 23, 2011 at 8:41 am
Using this table:
--===== If the test table already exists, drop it =====--
IF OBJECT_ID('TempDB..#jobtran','U') IS NOT NULL DROP TABLE #jobtran
GO
--===== Create the test table =====--
CREATE TABLE #jobtran
(
emp_numvarchar(10),
APP DECIMAL(21,2),
CLN DECIMAL(21,2),
SAF DECIMAL(21,2),
SAT DECIMAL(21,2),
TA DECIMAL(21,2),
WHK DECIMAL(21,2)
)
--===== Insert the test data into the test table =====--
INSERT INTO #jobtran
(emp_num, APP, CLN, SAF, SAT, TA, WHK)
SELECT 'D32', 1, 2.0, 0, 2.5, 0, 7.5 UNION ALL
SELECT 'D53', 3, 3.5, 0, 4.0, 0, 5.5 UNION ALL
SELECT 'D78', 2, 4.0, 0, 5.0, 0, 3.0 UNION ALL
SELECT 'D97', 1, 8.0, 0, 6.5, 0, 2.0 UNION ALL
SELECT 'S17', 5, 5.0, 0, 1.0, 0, 2.5 UNION ALL
SELECT 'S23', 3, 4.5, 0, 1.0, 0, 2.0 UNION ALL
SELECT 'S37', 2, 5.5, 0, 2.0, 0, 3.0 UNION ALL
SELECT 'S39', 2, 7.0, 0, 3.5, 0, 5.5 UNION ALL
SELECT 'S43', 1, 3.0, 0, 4.5, 0, 6.5
--==== SELECT the records ====--
SELECT'Totals' Totals,
SUM(APP) APP,
SUM(CLN) CLN,
SUM(SAF) SAF,
SUM(SAT) SAT,
SUM(TA) TA,
SUM(WHK) WHK
FROM #jobtran
We see that columns SAF and TA sum up to 0. What I am trying to do is only select the columns that sum up to greater than zero, like this:
Totals APP CLN SAT WHK
------ ------ ------ ------ ------
Totals 20.00 42.50 30.00 37.50
Is there a way to do this with T-SQL?
Thank you.
Steve
March 23, 2011 at 9:46 am
I'm sure there are more elegant/complete ways of doing this, but it gets you close. If you combine this with PIVOT it would work.
;
WITH Totals ( TheSum, ColName )
AS ( SELECT SUM(APP) ,
'APP'
FROM #jobtran
UNION ALL
SELECT SUM(CLN) ,
'CLN'
FROM #jobtran
UNION ALL
SELECT SUM(SAF) ,
'SAF'
FROM #jobtran
UNION ALL
SELECT SUM(SAT) ,
'SAT'
FROM #jobtran
UNION ALL
SELECT SUM(TA) ,
'TA'
FROM #jobtran
UNION ALL
SELECT SUM(WHK) ,
'WHK'
FROM #jobtran
)
SELECT ColName ,
TheSum
FROM Totals
WHERE TheSum > 0
March 23, 2011 at 9:49 am
You're probably better off doing this in the presentation layer rather than in T-SQL. For instance, this is easy to do in SSRS by specifying the visibility property of each column.
In T-SQL you'll have to write dynamic SQL to get these results.
Since you haven't specified how you are presenting the results, it's difficult to tell you the best approach to use.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 23, 2011 at 10:43 am
Pam, thank you again. I will see if I can incorporate this into my code.
Drew, you have given me a very good idea. I am indeed trying to incorporate this code into a report. Unfortunately, we don't use SSRS here, yet. We use Crystal Reports, which I am not as familiar with. I will see if there is a similar parameter there to accomplish what I need.
Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply