Sol:
– Creating Tables
Create table dbo.Summary (SummaryId int identity (1,1), FundTypeId int, DepositAmount decimal(13,2), DepositDate datetime)
Create table dbo.FundType (FundTypeId int, FundName varchar(100))
– Inserting into FundType table
Insert into FundType (FundTypeId, FundName) Values (1, ‘First Mutual Fund’)
Insert into FundType (FundTypeId, FundName) Values (2, ‘Second Mutual Fund’)
Insert into FundType (FundTypeId, FundName) Values (3, ‘Third Mutual Fund’)
Insert into FundType (FundTypeId, FundName) Values (4, ‘Fourth Mutual Fund’)
Insert into FundType (FundTypeId, FundName) Values (5, ‘Fifth Mutual Fund’)
– Inserting into Summary table
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (1, 566.00, ’01/01/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (1, 45.00, ’02/02/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (1, 45.00, ’03/03/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (1, 56.00, ’04/04/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (1, 234.00, ’05/05/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (1, 5.00, ’06/06/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (1, 2346.00, ’07/07/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (1, 7.00, ’08/08/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (1, 56.00, ’09/09/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (1, 5.00, ’10/10/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (1, 7676.00, ’08/11/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (1, 6.00, ’09/12/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (1, 78.00, ’11/13/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (2, 2345.00, ’01/01/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (2, 24.00, ’02/02/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (2, 5.00, ’03/03/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (2, 56.00, ’04/04/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (2, 476.00, ’05/05/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (3, 347.00, ’06/06/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (3, 4745.00, ’07/07/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (3, 72.00, ’08/08/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (3, 768.00, ’09/09/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (3, 46.00, ’10/10/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (3, 856.00, ’08/11/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (4, 7.00, ’09/12/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (4, 2356.00, ’11/13/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (4, 456.00, ’01/01/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (4, 73.00, ’02/02/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (4, 5.00, ’03/03/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (5, 34.00, ’04/04/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (5, 763.00, ’05/05/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (5, 45.00, ’06/06/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (5, 6.00, ’07/07/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (5, 34.00, ’08/08/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (5, 56.00, ’09/09/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (5, 57.00, ’10/10/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (5, 6.00, ’08/11/08′)
Insert into Summary (FundTypeId, DepositAmount, DepositDate) values (5, 878.00, ’01/01/08′)
Create procedure dbo.Sp_DepositAmountRecords
As
/*
Exec Sp_DepositAmountRecords
*/
Set Nocount On
Declare @tblRecords table
(FundType int, FundDesc varchar(100),
Jan decimal, Feb decimal, Mar decimal, Apr decimal,
May decimal, Jun decimal, Jul decimal, Aug decimal,
Sep decimal, Oct decimal, Nov decimal, Dcm decimal)
– Insert all the Fund type records
Insert into @tblRecords
(FundType, FundDesc, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dcm)
Select
FundTypeId, FundName, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
from
FundType
– For January
Update tr set
tr.Jan =Isnull (Value.DepositAmount, 0)
from
@tblRecords tr
inner join
(Select
tr.FundType, Sum (Isnull (sm.DepositAmount, 0)) DepositAmount
from
@tblRecords tr
left outer join
Summary sm
on tr.FundType =sm.FundTypeId
where
Datepart (mm, sm.DepositDate) =1
group by
tr.FundType) Value
on tr.FundType =Value.FundType
– For February
Update tr set
tr.Feb =Isnull (Value.DepositAmount, 0)
from
@tblRecords tr
inner join
(Select
tr.FundType, Sum (Isnull (sm.DepositAmount, 0)) DepositAmount
from
@tblRecords tr
left outer join
Summary sm
on tr.FundType =sm.FundTypeId
where
Datepart (mm, sm.DepositDate) =2
group by
tr.FundType) Value
on tr.FundType =Value.FundType
– For March
Update tr set
tr.Mar =Isnull (Value.DepositAmount, 0)
from
@tblRecords tr
inner join
(Select
tr.FundType, Sum (Isnull (sm.DepositAmount, 0)) DepositAmount
from
@tblRecords tr
left outer join
Summary sm
on tr.FundType =sm.FundTypeId
where
Datepart (mm, sm.DepositDate) =3
group by
tr.FundType) Value
on tr.FundType =Value.FundType
– For April
Update tr set
tr.Apr =Isnull (Value.DepositAmount, 0)
from
@tblRecords tr
inner join
(Select
tr.FundType, Sum (Isnull (sm.DepositAmount, 0)) DepositAmount
from
@tblRecords tr
left outer join
Summary sm
on tr.FundType =sm.FundTypeId
where
Datepart (mm, sm.DepositDate) =4
group by
tr.FundType) Value
on tr.FundType =Value.FundType
– For May
Update tr set
tr.May =Isnull (Value.DepositAmount, 0)
from
@tblRecords tr
inner join
(Select
tr.FundType, Sum (Isnull (sm.DepositAmount, 0)) DepositAmount
from
@tblRecords tr
left outer join
Summary sm
on tr.FundType =sm.FundTypeId
where
Datepart (mm, sm.DepositDate) =5
group by
tr.FundType) Value
on tr.FundType =Value.FundType
– For June
Update tr set
tr.Jun =Isnull (Value.DepositAmount, 0)
from
@tblRecords tr
inner join
(Select
tr.FundType, Sum (Isnull (sm.DepositAmount, 0)) DepositAmount
from
@tblRecords tr
left outer join
Summary sm
on tr.FundType =sm.FundTypeId
where
Datepart (mm, sm.DepositDate) =6
group by
tr.FundType) Value
on tr.FundType =Value.FundType
– For July
Update tr set
tr.Jul =Isnull (Value.DepositAmount, 0)
from
@tblRecords tr
inner join
(Select
tr.FundType, Sum (Isnull (sm.DepositAmount, 0)) DepositAmount
from
@tblRecords tr
left outer join
Summary sm
on tr.FundType =sm.FundTypeId
where
Datepart (mm, sm.DepositDate) =7
group by
tr.FundType) Value
on tr.FundType =Value.FundType
– For August
Update tr set
tr.Aug =Isnull (Value.DepositAmount, 0)
from
@tblRecords tr
inner join
(Select
tr.FundType, Sum (Isnull (sm.DepositAmount, 0)) DepositAmount
from
@tblRecords tr
left outer join
Summary sm
on tr.FundType =sm.FundTypeId
where
Datepart (mm, sm.DepositDate) =8
group by
tr.FundType) Value
on tr.FundType =Value.FundType
– For September
Update tr set
tr.Sep =Isnull (Value.DepositAmount, 0)
from
@tblRecords tr
inner join
(Select
tr.FundType, Sum (Isnull (sm.DepositAmount, 0)) DepositAmount
from
@tblRecords tr
left outer join
Summary sm
on tr.FundType =sm.FundTypeId
where
Datepart (mm, sm.DepositDate) =9
group by
tr.FundType) Value
on tr.FundType =Value.FundType
– For October
Update tr set
tr.Oct =Isnull (Value.DepositAmount, 0)
from
@tblRecords tr
inner join
(Select
tr.FundType, Sum (Isnull (sm.DepositAmount, 0)) DepositAmount
from
@tblRecords tr
left outer join
Summary sm
on tr.FundType =sm.FundTypeId
where
Datepart (mm, sm.DepositDate) =10
group by
tr.FundType) Value
on tr.FundType =Value.FundType
– For November
Update tr set
tr.Nov =Isnull (Value.DepositAmount, 0)
from
@tblRecords tr
inner join
(Select
tr.FundType, Sum (Isnull (sm.DepositAmount, 0)) DepositAmount
from
@tblRecords tr
left outer join
Summary sm
on tr.FundType =sm.FundTypeId
where
Datepart (mm, sm.DepositDate) =11
group by
tr.FundType) Value
on tr.FundType =Value.FundType
– For December
Update tr set
tr.Dcm =Isnull (Value.DepositAmount, 0)
from
@tblRecords tr
inner join
(Select
tr.FundType, Sum (Isnull (sm.DepositAmount, 0)) DepositAmount
from
@tblRecords tr
left outer join
Summary sm
on tr.FundType =sm.FundTypeId
where
Datepart (mm, sm.DepositDate) =12
group by
tr.FundType) Value
on tr.FundType =Value.FundType
Select * from @tblRecords
Set Nocount Off
Return
