Year to Date query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Year to Date query

Author

Topic

konark
Yak Posting Veteran

60 Posts

Posted - 2009-04-08 : 18:22:55

Table MonthScore

Month RollNo Score
200701 132 80
200702 132 90
200703 132 80
200704 132 90
200705 132 70
200706 132 90
200707 132 80
200708 132 90
200709 132 80
200710 132 90
200711 132 70
200712 132 90
200801 132 80
200802 132 90
200803 132 80
200804 132 90
200805 132 70
200806 132 80
200807 132 90
200808 132 70
200809 132 80
200810 132 80
200811 132 90
200812 132 70
200901 132 80
200902 132 90
200903 132 70

Table WeekScore

WeekDate RollNo Score
20090220 132 80
20090227 132 90
20090306 132 80
20090313 132 90
20090320 132 50
20090327 132 70
20090403 132 90

Can you help me to find out a report like the below
Prior 2 year prior 1 year , months average till today and last 6 weeks score.

Example of headers of the report

RollNo 2007Average 2008Average Jan2009Avg Feb2009Avg 20090227 20090306 20090313 20090320 20090327 20090403

Chandragupta Mourya

konark
Yak Posting Veteran

60 Posts

Posted - 2009-04-08 : 18:51:15

The last 2 years and the passed months and the last 6 weeks columsn should come dynamically .

Chandragupta Mourya

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-09 : 05:45:34

I have 2 questions for you.

1. In the result set, you asked to give the passed months also. WHat you mean by that? all the months in that table or months in that year?

2. And you asked for monthly average? is monthly average means is the average of weeklytest in that month or score of that month in monthscore table?

konark
Yak Posting Veteran

60 Posts

Posted - 2009-04-09 : 12:49:26

1. Months passed means , it should dynamically extract months till that time of the year.

Suppose today is April , it should show Jan , Feb March.

2. Monthly Avarage means to Avarge of Monthly Score from Month_Score table.
Weekly Score will be just the score from Week_score table.

Am I clear to you?

Chandragupta Mourya

konark
Yak Posting Veteran

60 Posts

Posted - 2009-04-14 : 13:51:58

no reults?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources