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
 General SQL Server Forums
 New to SQL Server Programming
 incorrect results.

Author  Topic 

mukhan85
Starting Member

46 Posts
Posted - 2008-07-21 : 15:07:48
Hi, I have written the following query, but for some reason, I have incorrect output.
select Label.am_value1,
Label.am_label1,
ISNULL(Detail.am_value1, 0)
from(
select '1' as am_value1, 'Successful' as am_label1 union all
select '2' as am_value1, 'Successful' as am_label1 union all
select '3' as am_value1, 'Successful' as am_label1 union all
select '4' as am_value1, 'Successful' as am_label1 union all
select '5' as am_value1, 'Successful' as am_label1 union all
select '6' as am_value1, 'Successful' as am_label1 union all
select '7' as am_value1, 'Successful' as am_label1 union all
select '8' as am_value1, 'Successful' as am_label1 union all
select '9' as am_value1, 'Successful' as am_label1 union all
select '10' as am_value1, 'Successful' as am_label1 union all
select '11' as am_value1, 'Successful' as am_label1 union all
select '12' as am_value1, 'Successful' as am_label1 union all

select '1' as am_value1, 'Failed' as am_label1 union all
select '2' as am_value1, 'Failed' as am_label1 union all
select '3' as am_value1, 'Failed' as am_label1 union all
select '4' as am_value1, 'Failed' as am_label1 union all
select '5' as am_value1, 'Failed' as am_label1 union all
select '6' as am_value1, 'Failed' as am_label1 union all
select '7' as am_value1, 'Failed' as am_label1 union all
select '8' as am_value1, 'Failed' as am_label1 union all
select '9' as am_value1, 'Failed' as am_label1 union all
select '10' as am_value1, 'Failed' as am_label1 union all
select '11' as am_value1, 'Failed' as am_label1 union all
select '12' as am_value1, 'Failed' as am_label1 union all

select '1' as am_value1, 'Canceled' as am_label1 union all
select '2' as am_value1, 'Canceled' as am_label1 union all
select '3' as am_value1, 'Canceled' as am_label1 union all
select '4' as am_value1, 'Canceled' as am_label1 union all
select '5' as am_value1, 'Canceled' as am_label1 union all
select '6' as am_value1, 'Canceled' as am_label1 union all
select '7' as am_value1, 'Canceled' as am_label1 union all
select '8' as am_value1, 'Canceled' as am_label1 union all
select '9' as am_value1, 'Canceled' as am_label1 union all
select '10' as am_value1, 'Canceled' as am_label1 union all
select '11' as am_value1, 'Canceled' as am_label1 union all
select '12' as am_value1, 'Canceled' as am_label1 union all

select '1' as am_value1, 'SWP' as am_label1 union all
select '2' as am_value1, 'SWP' as am_label1 union all
select '3' as am_value1, 'SWP' as am_label1 union all
select '4' as am_value1, 'SWP' as am_label1 union all
select '5' as am_value1, 'SWP' as am_label1 union all
select '6' as am_value1, 'SWP' as am_label1 union all
select '7' as am_value1, 'SWP' as am_label1 union all
select '8' as am_value1, 'SWP' as am_label1 union all
select '9' as am_value1, 'SWP' as am_label1 union all
select '10' as am_value1, 'SWP' as am_label1 union all
select '11' as am_value1, 'SWP' as am_label1 union all
select '12' as am_value1, 'SWP' as am_label1 union all

select '1' as am_value1, 'Unsuccessful' as am_label1 union all
select '2' as am_value1, 'Unsuccessful' as am_label1 union all
select '3' as am_value1, 'Unsuccessful' as am_label1 union all
select '4' as am_value1, 'Unsuccessful' as am_label1 union all
select '5' as am_value1, 'Unsuccessful' as am_label1 union all
select '6' as am_value1, 'Unsuccessful' as am_label1 union all
select '7' as am_value1, 'Unsuccessful' as am_label1 union all
select '8' as am_value1, 'Unsuccessful' as am_label1 union all
select '9' as am_value1, 'Unsuccessful' as am_label1 union all
select '10' as am_value1, 'Unsuccessful' as am_label1 union all
select '11' as am_value1, 'Unsuccessful' as am_label1 union all
select '12' as am_value1, 'Unsuccessful' as am_label1 ) AS Label
LEFT OUTER JOIN
(select
(CASE when Status_Reason = 1000 then 'Successful'
when Status_Reason = 2000 then 'Failed'
when Status_Reason = 3000 then 'Canceled'
when Status_Reason = 10000 then 'SWP'
when Status_Reason = 11000 then 'Unsuccessful'
else 'No Closure Stat'
end) as am_label1,

//CALCULATES PERCENTAGE OF EACH STATUS_REASON FOR ONE MONTH
((cast(sum((case when Status_Reason = 1000 then 1
when Status_Reason = 2000 then 1
when Status_Reason = 3000 then 1
when Status_Reason = 11000 then 1
when Status_Reason = 10000 then 1 else 0 end)) as float)*100)/(cast(sum(count(*)) as float))) as am_value1,

month(DATEADD(second, End_Date, '1969-12-31 8:00:00 PM')) as myMonth

from Task
YEAR(DATEADD(second, End_Date, '1969-12-31 8:00:00 PM'))=YEAR(getdate()) AND
Product_Cat_Tier_1 IN ('Network') AND
Product_Cat_Tier_2 IN ('RFC') AND
Status = 6000 AND
Product_Cat_Tier_3 NOT IN ('Voice AND Video', 'Installation')
group by Status_Reason, month(DATEADD(second, End_Date, '1969-12-31 8:00:00 PM'))
) as Detail
ON ((cast(Label.am_value1 as int) = Detail.myMonth) and (Detail.am_label1 = Label.am_label1) )
-----------------------
THE OUTPUT OF THAT QUERY:
am_value1 am_label1
--------- --------- --------------------
1 Successful 100.0
2 Successful 100.0
3 Successful 100.0
4 Successful 100.0
5 Successful 100.0
6 Successful 100.0
7 Successful 100.0
8 Successful 0.0
9 Successful 0.0
10 Successful 0.0
11 Successful 100.0
12 Successful 0.0
1 Failed 100.0
2 Failed 100.0
3 Failed 100.0
4 Failed 100.0
5 Failed 100.0
6 Failed 100.0
7 Failed 100.0
8 Failed 0.0
9 Failed 0.0
10 Failed 0.0
11 Failed 0.0
12 Failed 0.0
1 Canceled 100.0
2 Canceled 100.0
3 Canceled 100.0
4 Canceled 100.0
5 Canceled 100.0
6 Canceled 100.0
7 Canceled 100.0
8 Canceled 0.0
9 Canceled 0.0
10 Canceled 0.0
11 Canceled 0.0
12 Canceled 0.0
1 SWP 0.0
2 SWP 0.0
3 SWP 0.0
4 SWP 0.0
5 SWP 0.0
6 SWP 100.0
7 SWP 100.0
8 SWP 0.0
9 SWP 0.0
10 SWP 0.0
11 SWP 0.0
12 SWP 0.0
1 Unsuccessful 0.0
2 Unsuccessful 0.0
3 Unsuccessful 0.0
4 Unsuccessful 0.0
5 Unsuccessful 100.0
6 Unsuccessful 100.0
7 Unsuccessful 100.0
8 Unsuccessful 0.0
9 Unsuccessful 0.0
10 Unsuccessful 0.0
11 Unsuccessful 0.0
12 Unsuccessful 0.0

------------------------------------
WHILE THE OUTPU OF THE SECOND PART OF THE QUERY, "Detail" part
(select
(CASE when Status_Reason = 1000 then 'Successful'
when Status_Reason = 2000 then 'Failed'
when Status_Reason = 3000 then 'Canceled'
when Status_Reason = 10000 then 'SWP'
when Status_Reason = 11000 then 'Unsuccessful'
else 'No Closure Stat'
end) as am_label1,

//CALCULATES PERCENTAGE OF EACH STATUS_REASON FOR ONE MONTH
((cast(sum((case when Status_Reason = 1000 then 1
when Status_Reason = 2000 then 1
when Status_Reason = 3000 then 1
when Status_Reason = 11000 then 1
when Status_Reason = 10000 then 1 else 0 end)) as float)*100)/(cast(sum(count(*)) as float))) as am_value1,

month(DATEADD(second, End_Date, '1969-12-31 8:00:00 PM')) as myMonth

from Task
YEAR(DATEADD(second, End_Date, '1969-12-31 8:00:00 PM'))=YEAR(getdate()) AND
Product_Cat_Tier_1 IN ('Network') AND
Product_Cat_Tier_2 IN ('RFC') AND
Status = 6000 AND
Product_Cat_Tier_3 NOT IN ('Voice AND Video', 'Installation')
group by Status_Reason, month(DATEADD(second, End_Date, '1969-12-31 8:00:00 PM'))
) as Detail

returns the following results:

am_label1 am_value1 myMonth
--------- -------------------- -----------
No Closure Stat 0.0 5
Successful 13.318681318681319 1
Successful 11.296703296703297 2
Successful 11.956043956043956 3
Successful 13.450549450549451 4
Successful 12.307692307692308 5
Successful 12.43956043956044 6
Successful 6.197802197802198 7
Successful .02197802197802198 11
Failed .5714285714285714 1
Failed .17582417582417584 2
Failed .37362637362637363 3
Failed .8571428571428571 4
Failed .4175824175824176 5
Failed .48351648351648352 6
Failed .065934065934065936 7
Canceled 2.9450549450549453 1
Canceled 2.3736263736263736 2
Canceled 2.6373626373626373 3
Canceled 2.5494505494505493 4
Canceled 2.197802197802198 5
Canceled 1.7582417582417582 6
Canceled .65934065934065933 7
SWP .39560439560439559 6
SWP .26373626373626374 7
Unsuccessful .02197802197802198 5
Unsuccessful .15384615384615385 6
Unsuccessful .087912087912087919 7

Thank you. I appreciate your help.
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site