How can i show my result in months format.

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

SQL Server 2000 Forums

Transact-SQL (2000)

How can i show my result in months format.

Author

Topic

muzaffar_ali99
Starting Member

33 Posts

Posted - 2006-08-04 : 04:39:20

I have a table that contains Earned_Score,Possible_Score and Score_Date of date type.
like
Possible_Score Earned_Score Score Score_Date
100 40 01/05/2006(mm/dd/yyyy)
200 150 02/10/2006
100 60 03/15/2006
200 190 01/16/2006

Now what i need to do is calculate Percentage OF Score in different months in which it was entered like as follows

Jan Feb Mar Apr May Jun July Aug Sep Oct Nov Dec
76% 75% 60%

76%=(190+40)/(200+100)
75%=150/200
60%=60/100

Regards
Muzaffar Ali

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-04 : 04:54:13

[code]-- prepare test data
set dateformat mdy

declare @table table (Earned_Score int, Possible_Score int, Score_Date datetime)

insert @table
select 100, 40, '01/05/2006' union all
select 200, 150, '02/10/2006' union all
select 100, 60, '03/15/2006' union all
select 200, 190, '01/16/2006'

-- do the work
SELECT z.ScoreYear,
ISNULL(MAX(CASE WHEN z.ScoreMonth = 1 THEN z.Perc END), 0) Jan,
ISNULL(MAX(CASE WHEN z.ScoreMonth = 2 THEN z.Perc END), 0) Feb,
ISNULL(MAX(CASE WHEN z.ScoreMonth = 3 THEN z.Perc END), 0) Mar,
ISNULL(MAX(CASE WHEN z.ScoreMonth = 4 THEN z.Perc END), 0) Apr,
ISNULL(MAX(CASE WHEN z.ScoreMonth = 5 THEN z.Perc END), 0) May,
ISNULL(MAX(CASE WHEN z.ScoreMonth = 6 THEN z.Perc END), 0) Jun,
ISNULL(MAX(CASE WHEN z.ScoreMonth = 7 THEN z.Perc END), 0) Jul,
ISNULL(MAX(CASE WHEN z.ScoreMonth = 8 THEN z.Perc END), 0) Aug,
ISNULL(MAX(CASE WHEN z.ScoreMonth = 9 THEN z.Perc END), 0) Sep,
ISNULL(MAX(CASE WHEN z.ScoreMonth = 10 THEN z.Perc END), 0) Oct,
ISNULL(MAX(CASE WHEN z.ScoreMonth = 11 THEN z.Perc END), 0) Nov,
ISNULL(MAX(CASE WHEN z.ScoreMonth = 12 THEN z.Perc END), 0) Dec
FROM (
SELECT DATEPART(year, Score_Date) ScoreYear,
DATEPART(month, Score_Date) ScoreMonth,
CASE WHEN SUM(Earned_Score) = 0 THEN 0 ELSE 100 * SUM(Possible_Score) / SUM(Earned_Score) END Perc
FROM @Table
GROUP BY DATEPART(year, Score_Date),
DATEPART(month, Score_Date)
) z
GROUP BY z.ScoreYear[/code]

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-04 : 05:18:27

[code]declare @table table (Earned_Score int, Possible_Score int, Score_Date datetime)

insert @table
select 100, 40, '01/05/2006' union all
select 200, 150, '02/10/2006' union all
select 100, 60, '03/15/2006' union all
select 200, 190, '01/16/2006'

select year(Score_Period) as [Year],
sum(case when month(Score_Period) = 1 then Score else 0 end) as [Jan],
sum(case when month(Score_Period) = 2 then Score else 0 end) as [Feb],
sum(case when month(Score_Period) = 3 then Score else 0 end) as [Mar],
sum(case when month(Score_Period) = 4 then Score else 0 end) as [Apr],
sum(case when month(Score_Period) = 5 then Score else 0 end) as [May],
sum(case when month(Score_Period) = 6 then Score else 0 end) as [Jun],
sum(case when month(Score_Period) = 7 then Score else 0 end) as [Jul],
sum(case when month(Score_Period) = 8 then Score else 0 end) as [Aug],
sum(case when month(Score_Period) = 9 then Score else 0 end) as [Sep],
sum(case when month(Score_Period) = 10 then Score else 0 end) as [Oct],
sum(case when month(Score_Period) = 11 then Score else 0 end) as [Nov],
sum(case when month(Score_Period) = 12 then Score else 0 end) as [Dec]
from
(
select dateadd(month, datediff(month, 0, Score_Date), 0) as Score_Period,
sum(Possible_Score) * 100 / sum(Earned_Score) as Score
from @table
group by dateadd(month, datediff(month, 0, Score_Date), 0)
) d
group by year(Score_Period)

/* RESULT
Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2006 76 75 60 0 0 0 0 0 0 0 0 0
*/
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-04 : 07:57:34

Where is that "shoot the moose" icon when I need it?