Bizarre, same code, different out put
-
This is killing me. I wrote this statement below, it works great. wanted to run the same query on a different table that has the same type of info just different column names and it comes back ordered differently?!? Same dang code!
OK, here is the sql statement pulling from the rrform table:
--research request
select case when (GROUPING(rr.ddtreasuryteam) = 1)
THEN 'All Requests'
else isnull(case when (GROUPING(rr.CBOSTATUS) = 1)
THEN 'Total Received ' ELSE '' END
+ rr.ddtreasuryteam, 'n/a') end as Team,
case when (GROUPING(rr.CBOSTATUS) = 1)
THEN ' ' else isnull(rr.CBOSTATUS, 'n/a') end as Status,
count(*) as Total,
left(min(txtRdate),11) as [Oldest Date],
COUNT(tab1.Total) /
(SELECT CAST(COUNT(*) AS decimal) FROM RRForm
WHERE ddtreasuryteam = 'alpharetta' or ddtreasuryteam = 'midwest' or ddtreasuryteam = 'west'
and cboStatus <> 'closed'
and txtRDate >= '10/10/2003' and txtRDate <='10/20/2004 23:59:00') * 100
AS [Percent Received] from
(select * from rrform where cbostatus <> 'closed') as rr
JOIN (SELECT ddtreasuryteam, COUNT(*) AS [Total] FROM RRForm
WHERE cbostatus <> 'closed'
GROUP BY ddtreasuryteam ) AS [tab1]
ON rr.ddtreasuryteam = tab1.ddtreasuryteam
WHERE rr.ddtreasuryteam = 'alpharetta' or rr.ddtreasuryteam = 'midwest' or rr.ddtreasuryteam = 'west'
and rr.txtRDate >= '10/10/2003' and rr.txtRDate <='10/20/2004 23:59:00'
Group By rr.DDTreasuryTeam, rr.CBOSTATUS with rollup
ORDER BY (CASE WHEN rr.ddtreasuryteam IS NULL THEN 1 ELSE 0 END),rr.ddtreasuryteam
gives me results like this:
Alpharetta New 74 Jan 21 2004 .1248924068792087800
Alpharetta Pending 113 Dec 29 2003 .1907140807750080100
Total Received Alpharetta 187 Dec 29 2003 .3156064876542168000
Midwest In Progress 28 Jan 9 2004 .0472565863867276500
Midwest New 16 Jan 19 2004 .0270037636495586500
Midwest Pending 61 Jan 14 2004 .1029518489139423800
Total Received Midwest 105 Jan 9 2004 .1772121989502286800
West In Progress 1 Jan 20 2004 .0016877352280974100
West New 53 Jan 17 2004 .0894499670891630500
West Pending 25 Jan 13 2004 .0421933807024354000
Total Received West 79 Jan 13 2004 .1333310830196958700
All Requests 371 Dec 29 2003 .6261497696241413600
notice I get a total for each group then I get the total for the whole thing. I took the code and replaced some of the column names for the refund table:
--for refund
select case when (GROUPING(rf.team) = 1)
THEN 'All Requests'
else isnull(case when (GROUPING(rf.STATUS) = 1)
THEN 'Total Received ' ELSE '' END
+ rf.team, 'n/a') end as Team,
case when (GROUPING(rf.STATUS) = 1)
THEN ' ' else isnull(rf.STATUS, 'n/a') end as Status,
count(*) as Total,
left(min(RequestDateTime),11) as [Oldest Date],
COUNT(tab1.Total) /
(SELECT CAST(COUNT(*) AS decimal) FROM refund_form
WHERE Team = 'Northeast' or Team = 'Nashville-TSS' or Team = 'Morristown-TSS' or Team = 'MidAtlantic' or Team = 'EPSG' or Team = 'Billing and Collections' or Team = 'Alpharetta'
and Status <> 'closed'
and RequestDateTime >= '10/10/2003' and RequestDateTime <='10/20/2004 23:59:00') * 100
AS [Percent Received] from
(select * from refund_form where status <> 'closed') as rf
JOIN (SELECT team, COUNT(*) AS [Total] FROM refund_form
WHERE status <> 'closed'
GROUP BY team ) AS [tab1]
ON rf.team = tab1.team
WHERE rf.Team = 'Northeast' or rf.Team = 'Nashville-TSS' or rf.Team = 'Morristown-TSS' or rf.Team = 'MidAtlantic' or rf.Team = 'EPSG' or rf.Team = 'Billing and Collections' or rf.Team = 'Alpharetta'
and rf.RequestDateTime >= '10/10/2001' and rf.RequestDateTime <='10/20/2002 23:59:00'
Group By rf.team, rf.STATUS with rollup
ORDER BY (CASE WHEN rf.team IS NULL THEN 1 ELSE 0 END),rf.team
and I get this:
Alpharetta In Progress 1 Jan 14 2004 .0060731203692457100
Alpharetta New 22 Jan 15 2004 .1336086481234058000
Alpharetta Pending 7 Jan 15 2004 .0425118425847200200
Midwest In Progress 15 Jan 14 2004 .0910968055386857700
Midwest New 5 Jan 14 2004 .0303656018462285900
Midwest Pending 44 Jan 12 2004 .2672172962468116100
Total Received Alpharetta 30 Jan 14 2004 .1821936110773715500
Total Received Midwest 64 Jan 12 2004 .3886797036317259800
Total Received West 17 Jan 14 2004 .1032430462771772100
West New 16 Jan 14 2004 .0971699259079314900
West Pending 1 Jan 21 2004 .0060731203692457100
All Requests 111 Jan 12 2004 .6741163609862747400
not only are all the totals grouped together but West is the last group. It looks like it is sorting in Alpha order. But WHY is it different!!!! I'm using this to produce reports in asp but I need the output to be the same no matter which table I pull from so that I can format it. The 1st one is ordered correctly.
-
I think that SQL Server is getting confused as to what rf.team means. The query is acting like it is treating that whole case statement as rf.team since that is what you have aliased it as.
Run the following two queries in pubs database:
select a.au_id,
a.au_lname , t.title_id, t.title
from
dbo.titleauthor ta inner join dbo.authors a
on a.au_id = ta.au_id inner join dbo.titles t
on t.title_id = ta.title_id
order by a.au_id
select a.au_lname as au_id, t.title_id, t.title
from
dbo.titleauthor ta inner join dbo.authors a
on a.au_id = ta.au_id inner join dbo.titles t
on t.title_id = ta.title_id
order by a.au_id
I got different orders, which shouldn't happen.
In the first, a.au_id is going to be treated as au_id i the results, whereas in the second a.au_lastname is treated as au_id. I would have thought that the order by would use the value in a.au_id (ie the value in the database).
I'm not sure but this sure looks like a bug to me.
Russel Loski, MCSD
Russel Loski, MCSE Business Intelligence, Data Platform
-
You're right, SQL was getting confused. The column I named "Team" was giving me the strange result because I the column name in the refund_form table was also name "Team". It is called ddTreasuryTeam in the RRForm table. I have no idea what exactly it was doing but I corrected it by changing the column name from "Team" to "Treasury Team" in my result set. Thank you so much for looking into this for me!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply