sql server Ceate view as required

Question

I have 2 tables for a school fee management project
students table which has student name class and other fields
and tbl_fee_rates is the table which keep information about class fee on the basis of rates each month rate should be calculated using tbl_fee_rates table

tbl_fee_rates
-------------------------------------
id    date       class    rate      |
-------------------------------------
1   31-12-2013    5th     200       |
2   31-12-2013    6th     500       |
                                    |
3   27-03-2013    5th     250       |
4   01-04-2013    6th     600       |
                                    |
5   29-05-2013    5th     300       |
                                    |
6   29-09-2013    5th     320       |
-------------------------------------

and another table is

tbl_Students
-----------------------
ID     Name     Class |
-----------------------
1      Alex      5th  |
1      Alex      6th  |
-----------------------

I need a view as following

tbl_Students
--------------------------------------------------------------------------------------
ID     Name     Class  JAN   FEB   MAR   APR  MAY  JUN  JUL  AUG  SEP  OCT  NOV  DEC |
--------------------------------------------------------------------------------------
1      Alex      5th   200   200   200   250  250  300  300  300  300  320  320  320 |
1      Alex      6th   500   500   500   500  500  500  500  600  600  600  600  600 |
--------------------------------------------------------------------------------------

in above view jan,feb...dec are months and its values are fee of respective students for for a month is calculated using table(tbl_fee_rates)

if it s not possible through, how can I get this, any alternative?

Answer 1

Very simple just copy.. below code or try fiddle link below.. Thank you..

http://sqlfiddle.com/#!3/72364/3/0

SELECT * FROM
(
        SELECT t2.ID,
               t2.Name,
               t1.CLASS,
               left(datename(month,t1.DATE),3)as [month],
               t1.RATE 
        FROM tbl_fee_rates t1 inner join tbl_Students t2 
        ON t1.Class = t2.class
) P
PIVOT 
(
SUM(RATE) FOR [month] IN (jan, feb, mar, apr,may, jun, jul, aug, sep, oct, nov, dec)
)
AS PVT

The result display as below..

ID          Name                                               CLASS                                              jan         feb         mar         apr         may         jun         jul         aug         sep         oct         nov         dec
----------- -------------------------------------------------- -------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           Alex                                               5TH                                                NULL        NULL        350         NULL        300         NULL        NULL        NULL        NULL        NULL        NULL        200
2           Alex                                               6TH                                                NULL        NULL        NULL        600         320         NULL        NULL        NULL        NULL        NULL        NULL        500

(2 row(s) affected)

Answer 2

Here's one way of accomplishing this:

create view Rates2013 as

with months as
(
  select monthStart = cast('01-jan-2013' as date)
  union all
  select monthStart = dateadd(month, 1, monthStart)
  from months
  where dateadd(month, 1, monthStart) < '01-jan-2014'
)
, rates as
(
  select s.ID
    , s.Name
    , s.Class
    , rateMonth = left(datename(month, m.monthStart), 3)
    , r.rate
  from months m
    cross join tbl_Students s
    cross apply (select top 1 rate
      from tbl_fee_rates r
      where m.monthStart >= r.[date]
        and s.Class = r.class
      order by [date] desc) r
)
select ID, Name, Class, [Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
from rates
pivot
(
  sum(rate)
  for rateMonth in ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
) p

SQL Fiddle with demo.

There are a number of caveats here. First, with your data, the first values are 2013 but should probably be 2012; the Fiddle has been updated to reflect this.

Also, I assume the rate with ID 4 should be applied in April, not August; my results show this.

Next, you are showing data for a year, but there is no concept of time periods in your data. For the purposes of this answer I have assumed to display for 2013 only; your may have to tailor for your requirements.

For your own solution you could consider a calendar table to create the months instead of a recursive CTE.

All the above points notwithstanding, this does give one option to generate your results, so hopefully it helps you find your solution.