using grouping
-
when I run this:
select case when (GROUPING(ddtreasuryteam) = 1) THEN 'All Requests'
else isnull(ddtreasuryteam, 'n/a')
end as Team,
case when (GROUPING(CBOSTATUS) = 1) THEN 'Total Received'
else isnull(CBOSTATUS, 'n/a')
end as Status,
count(*) as Total,
left(min(txtRdate),11) as [Oldest Date]
from rrform
WHERE DDTREAsuryteam = 'alpharetta' or
ddtreasuryteam = 'epsg' or
ddtreasuryteam = 'headquarters' or
ddtreasuryteam = 'midwest'
Group By DDTreasuryTeam, CBOSTATUS with rollup ORDER BY DDTREASURYTEAM
I get this:
Team Status Total Oldest Date
All Requests Total Received 119021 Sep 27 2001
Alpharetta Closed 44399 Sep 27 2001
Alpharetta In Progress 2 Dec 17 2003
Alpharetta New 90 Dec 17 2003
Alpharetta Pending 113 Dec 3 2003
Alpharetta Total Received 44604 Sep 27 2001
Billing and Collections New 2 Nov 20 2003
Billing and Collections Pending 1 Sep 9 2003
Billing and Collections Total Received 3 Sep 9 2003
EPSG Closed 11218 Oct 2 2001
EPSG In Progress 9 Dec 8 2003
EPSG New 29 Dec 11 2003
EPSG Pending 47 Dec 6 2003
EPSG Total Received 11303 Oct 2 2001
MidAtlantic Closed 2967 Oct 20 2001
MidAtlantic In Progress 1 Dec 18 2003
MidAtlantic New 7 Dec 4 2003
MidAtlantic Pending 7 Dec 10 2003
MidAtlantic Total Received 2982 Oct 20 2001
On the lines that have 'Total Recieved' in the 'Total' column, I would like to be able to be able to add the word 'All' to the name of the the Team in the 'Team' column. If anyone has ANY idea how to do that please let me know. Thanks in advance to all you geniuses.
-
Try changing the first part of the select to
select case when (GROUPING(ddtreasuryteam) = 1) THEN 'All Requests'
else isnull(case when (GROUPING(CBOSTATUS) = 1) THEN 'All ' ELSE '' END + ddtreasuryteam, 'n/a')
end as Team,Edited by - davidburrows on 12/23/2003 03:31:36 AM
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks! That worked perfect!
But I'm having trouble formatting that 1st line. when I try and format the cell/line it gives me that cell twice.
if instr(thisfield,"All") then
%>
<td valign=top><font color=blue size = "2"><b><%=thisfield%></b></font></td>
<%end if%>
Note this method works fine when I format the other total lines i.e.
if instr(thisfield,"Total Received") then
%>
<tr bgcolor=tan><td valign=top><font color=white size = "2"><b><%=thisfield%></b></font></td>
<%else%>
<td valign=top class="confsub"><%=thisfield%></td>
<%end if%>
The best would be if I can possibly move that line to the bottom of the results or even better remove it completely. Wouldn't happen to have any ideas on that would you?
-
Try this
select case when (GROUPING(ddtreasuryteam) = 1) THEN 'All Requests'
ELSE isnull(ddtreasuryteam, 'n/a') END as Team,
case when (GROUPING(CBOSTATUS) = 1) THEN 'Total Received'
else isnull(CBOSTATUS, 'n/a')
end as Status,
count(*) as Total,
left(min(txtRdate),11) as [Oldest Date]
from rrform
WHERE DDTREAsuryteam = 'alpharetta' or
ddtreasuryteam = 'epsg' or
ddtreasuryteam = 'headquarters' or
ddtreasuryteam = 'midwest'
Group By DDTreasuryTeam, CBOSTATUS with rollup
HAVING NOT (ddtreasuryteam IS NOT NULL AND CBOSTATUS IS NULL)
ORDER BY (CASE WHEN ddtreasuryteam IS NULL THEN 1 ELSE 0 END),ddtreasuryteam
Far away is close at hand in the images of elsewhere.
Anon. -
you da bomb. can you come live at my desk? I'll get you one of those nice air matresses...
-
oops, spoke too soon. The rollup isn't working now. I get no errors though, it just acts like I didn't say 'with rollup', no totals
-
I'd just as soon not even show that line cause I still have the format trouble
-
I'm getting confused on what the problem is now. This query is based on your original plus 'All' in front of Team for Team totals and the data is ordered to put totals at the end of each Team and 'All Requests' total at the end.
Can you be more specific regarding the problem with duplicate cell/line?
select case when (GROUPING(ddtreasuryteam) = 1)
THEN 'All Requests'
else isnull(case when (GROUPING(CBOSTATUS) = 1)
THEN 'All ' ELSE '' END
+ ddtreasuryteam, 'n/a') end as Team,
case when (GROUPING(CBOSTATUS) = 1) THEN 'Total Received'
else isnull(CBOSTATUS, 'n/a')
end as Status,
count(*) as Total,
left(min(txtRdate),11) as [Oldest Date]
from rrform
WHERE DDTREAsuryteam = 'alpharetta' or
ddtreasuryteam = 'epsg' or
ddtreasuryteam = 'headquarters' or
ddtreasuryteam = 'midwest'
Group By DDTreasuryTeam, CBOSTATUS with rollup
ORDER BY (CASE WHEN ddtreasuryteam IS NULL THEN 1 ELSE 0 END),ddtreasuryteamEdited by - davidburrows on 12/23/2003 10:43:23 AM
Far away is close at hand in the images of elsewhere.
Anon. -
OK, I made the dupe thing moot by you helping me put it at the end of the recordset where it won't bother anyone. I truly appreciate your help. Now I'm tackling math which I can do but my co-workers and I are having trouble referencing the number in the 'Total' column in order to do some math on it and put the answer into another column. What I really want to say is
Total /(select count(*) from rrform where cbostatus <> 'closed') as [Num Requests Open]
where Total is the column that is output by the rollup function.
looks like this (see below). I just need to add a column to it.
Alpharetta In Progress 42 Dec 20 2001
Alpharetta New 354 Mar 13 2002
Alpharetta Pending 253 Nov 5 2001
Total Received Alpharetta 649 Nov 5 2001
EPSG In Progress 23 Dec 3 2001
EPSG New 313 Dec 29 2001
EPSG Pending 35 Jan 17 2002
Total Received EPSG 371 Dec 3 2001
Headquarters In Progress 1 Dec 20 2001
Headquarters Pending 1 Dec 20 2001
Total Received Headquarters 2 Dec 20 2001
Midwest In Progress 38 Jan 9 2002
Midwest New 429 Apr 22 2002
Midwest Pending 58 Feb 14 2002
Total Received Midwest 525 Jan 9 2002
All Requests 1547 Nov 5 2001
If I'm wearing out my welcome I understand but this has stumped my coworker and my boss.
-
SUM(CASE WHEN cbostatus <> 'closed' THEN 1 ELSE 0 END) as [Num Requests Open]
Will give you the count of open requests
(CASE WHEN SUM(CASE WHEN cbostatus <> 'closed' THEN 1 ELSE 0 END) = 0 THEN 0
ELSE (count(*) + 0.0) / SUM(CASE WHEN cbostatus <> 'closed' THEN 1 ELSE 0 END)
END) as [Num Requests Open]
Will give you total divided by number of open requests (which is meaningless other than for totals!)
quote:
If I'm wearing out my welcome I understandNot at all, this is what the forum is for. Most of the time the answer is simple but difficult to see.
Far away is close at hand in the images of elsewhere.
Anon. -
Thank you so much for all your help. Never really used case statements in SQL before. This definitely gave me some valuable experience.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply