April 13, 2009 at 1:22 pm
Is it possible to order columns based upon their total? I first need to add a row totaling the columns and then I need to order the columns based upon their total.
Current output:
Fund, Central, Midwest, Northeast, Northwest, Southeast, Southwest
Fund1,310000.00,0.00,25000.00,140000.00,0.00,0.00
Fund2,50000.00,50000.00,100000.00,0.00,0.00,50000.00
Desired output:
Fund, Central, Northeast,Midwest, Northwest, Southeast, Southwest
Fund1,310000.00,25000.00,0.00,140000.00,0.00,0.00
Fund2,50000.00,100000.00,50000.00,0.00,0.00,50000.00
TOTAL, 360000.00, 60000.00, 50000.00,140,000.00,0.00,50,000.00
SQL:
SELECT
Daily.fund,
isnull(territories.Central, 0) as [Central],
isnull(territories.Midwest, 0) as Midwest,
isnull(territories.Northeast, 0) as Northeast,
isnull(territories.Northwest, 0) as Northwest,
isnull(territories.Southeast, 0) as Southeast,
isnull(territories.Southwest, 0) as Southwest
FROM
(SELECT DISTINCT
ISNULL(t.fund, f.description) AS fund,
f.isOpen
FROM tblfunds AS f LEFT OUTER JOIN
tblDascTransActions AS t ON f.description = t.fund AND
(DATEPART(m, t.createDate) = datepart(m,getdate())) and
(DATEPART(yy, t.createDate) = datepart(yy,getdate())) AND t.status <> 'deleted'
WHERE (f.isOpen = 0) AND (f.description NOT LIKE '%qualified%'))
AS Daily
LEFT OUTER JOIN
(
SELECT t.fund,
SUM(CASE WHEN t .territory = 'Northwest' THEN t .ticketamt ELSE '0.00' END) AS Northwest,
SUM(CASE WHEN t.territory = 'Southwest' THEN t .ticketamt ELSE '0.00' END) AS Southwest,
SUM(CASE WHEN t.territory = 'Midwest' THEN t .ticketamt ELSE '0.00' END) AS Midwest,
SUM(CASE WHEN t.territory = 'Central' THEN t .ticketamt ELSE '0.00' END) AS Central,
SUM(CASE WHEN t.territory = 'Northeast' THEN t .ticketamt ELSE '0.00' END) AS Northeast,
SUM(CASE WHEN t.territory = 'Southeast' THEN t .ticketamt ELSE '0.00' END) AS Southeast
FROM tblfunds AS f INNER JOIN
tblDascTransActions AS t ON f.description = t.fund INNER JOIN
tblTerritory AS r ON t.territory = r.territory
WHERE
(DATEPART(m, t.createDate) = datepart(m,getdate())) and
(DATEPART(yy, t.createDate) = datepart(yy,getdate())) AND (t.status <> 'deleted')
GROUP BY t.fund
) AS territories ON Daily.fund = territories.fund
April 13, 2009 at 2:32 pm
Hi
You can use CUBE or ROLLUP to do the sum of all the rows.
Check out the below link
http://databases.about.com/od/sql/l/aacuberollup.htm
April 13, 2009 at 2:32 pm
Question:
Who is going to view this data?? and in what form? In Report or Front-end application.
April 13, 2009 at 2:49 pm
Hi
You can use a CTE. Include a UNION ALL for your data and their SUM values. In addition add a sort column to ensure that the Totals are always on bottom of the result:
DECLARE @t TABLE
(
Fund VARCHAR(30),
Central MONEY,
Northeast MONEY,
Middlewest MONEY,
Northwest MONEY,
Southeast MONEY,
Southwest MONEY
)
INSERT INTO @t
SELECT 'Fund1', '310000.00', '25000.00', '0.00', '140000.00', '0.00', '0.00'
UNION ALL SELECT 'Fund2', '50000.00', '100000.00', '50000.00', '0.00', '0.00', '50000.00'
; WITH
result (Fund, Central, Northeast, Middlewest, Northwest, Southeast, Southwest, sort) AS
(
SELECT Fund, Central, Northeast, Northwest, Middlewest, Southeast, Southwest, 1
FROM @t
UNION ALL
SELECT 'Total', SUM(Central), SUM(Northeast), SUM(Northwest), SUM(Middlewest), SUM(Southeast), SUM(Southwest), 2
FROM @t
)
SELECT *
FROM result
ORDER BY sort
Greets
Flo
April 13, 2009 at 3:19 pm
Hi Florian, thanks for the response. So now that I have the total, how to I order the columns according to highest total first, etc. for example
Northwest, Southeast, Southwest, Central, Midwest,Northeast
Total 610, 510, 300, 200, 100, 50
April 13, 2009 at 3:48 pm
Hi
I'm not sure if i got you. This?:
ORDER BY sort, Central, Northeast, Middlewest, Northwest, Southeast, Southwest
Greets
Flo
April 13, 2009 at 4:04 pm
Hello Flo, I'm not sure if I understand. I put the order by in
ORDER BY sort, Central, Northeast, Middlewest, Northwest, Southeast, Southwest
and still the same order of columns comes over
Current output:
Fund, central, northeast, middlewest, northwest, southeast, southwest, sort
Fund250000.00100000.000.0050000.000.0050000.001
Fund1310000.0025000.00140000.000.000.000.001
Total360000.00125000.00140000.0050000.000.0050000.002
Desired output:
fund, Central, middlewest, northeast, northwest, southwest, southeast
fund1,310000.00,140000.00,25000.00,0.00,0.00,0.00
fund2,50000.00,0.00,100000.00,50000.00,50000.00,0.00
Total,360000.00,140000.00,125000.00,50000.00,50000.00,0.00
April 13, 2009 at 4:10 pm
[font="Verdana"]I think the problem is that you want to order the columns not the rows.
Hmmm.
The only way I can think of off-hand is to unpivot, order, then pivot back up.
[/font]
April 13, 2009 at 4:26 pm
Bruce W Cassidy (4/13/2009)
[font="Verdana"]I think the problem is that you want to order the columns not the rows.Hmmm.
The only way I can think of off-hand is to unpivot, order, then pivot back up.
[/font]
:-D:-D:-D
Thanks for opening my eyes. I didn't notice that columns have to be sorted.
So another question to mnicholas:
Why do the order of the columns matter? You can get the totals row and use this information to order in front-end.
Greets
Flo
April 13, 2009 at 4:29 pm
Hi Flo, good point and that's exactly where I want to go w/ this query is to Reporting Services report.
I guess how in MS Reporting Services do I order the columns from highest Total to lowest?
April 13, 2009 at 4:37 pm
mnicholas (4/13/2009)
I guess how in MS Reporting Services do I order the columns from highest Total to lowest?
[font="Verdana"]Heh. Do it in SQL Server? 😛
Seriously, I'd be hard pressed to figure out how to do it in reporting services without using some sort of custom code (inline VB function). So I'll stay tuned and see what the experts suggest!
Er... I do know there's a matrix thingie that you can drop on the page. Maybe you can change the sort order going across with that?
[/font]
April 13, 2009 at 5:43 pm
The problem is going to be which row do you want to use to order the columns? And then what happens when you add a region, or additional funds?
If you really need it totaled left to right - then you might want to consider modifying the resultset to return as:
Region Fund1 Fund2 Total
Central 310000.00 50000.00 360000.00
Middlewest 140000.00 0.00 140000.00
...
Now, you can use this result in the report using a matrix and pivot the results. Not sure if the matrix will order the matrix according to your results, but if that doesn't work - then it should be very simple to add a row_number() function, then map the results so you have in your matrix a Region1 mapped to row 1, Region2 mapped to row2, etc...
In either case, adding new regions or funds is going to require a change to the code and the report to make it work.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 13, 2009 at 5:45 pm
I think what I'm after is a row based sort solution, where I'm sorting on by the total row.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply