Sql Server Various Testings

USE [dams]
GO
/****** Object:  StoredProcedure [damsdata].[sp_DashBoardAsPerDateUpdated]    Script Date: 12/11/2013 17:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [damsdata].[sp_DashBoardAsPerDateUpdated]
(
@FranchiseCode varchar(10),
@Session nvarchar(10),
@FirstDay varchar(20),
@SevenDay varchar(20),
@ThirtyDay varchar(20),
@sixtyDay varchar(20),
@NintyDay varchar(20)
)
as
begin

Declare @DayChange varchar(20),

@TotalStudentsAsPer varchar(max),
@NewStudentsAsPer varchar(max),
@CollectionAsPer decimal(20,2),

@ServiceTaxAsPer decimal(20,2),
@ChequeReturnAsPer decimal(20,2),
@FeeConcessionAsPer decimal(20,2),
@FeeRefundAsPer decimal(20,2),
@BadDebtAsPer decimal(20,2),
@SmsSentAsPer bigint,
@RunningBatchesAsPer varchar(max)

DECLARE @DateRange table (DRange varchar(20))
insert into @DateRange (DRange) values (@NintyDay),(@sixtyDay),(@ThirtyDay),(@SevenDay),(@FirstDay)

if OBJECT_ID('tempdb..#AsPerDateFinal') is not null
drop table #AsPerDateFinal

create Table #AsPerDateFinal(A varchar(max),B varchar(max),
C varchar(max),D varchar(max),E varchar(max),F varchar(max)
,G varchar(max),H varchar(max),I varchar(max),J varchar(max))

Declare C1 Cursor for select DRange from @DateRange
open C1
fetch next from C1 into @DayChange
while(@@FETCH_STATUS=0)
begin

 set @TotalStudentsAsPer=(Select Count(*) as TotalStudents FROM STUDENTMASTER WHERE FranchiseCode=@FranchiseCode and  
 ADMNDATE <  CONVERT(VARCHAR(15),@DayChange,103))

 SET @NewStudentsAsPer=(Select count(*) as NewAdmissions from STUDENTMASTER where FranchiseCode=@FranchiseCode AND Session=@Session  
 and ADMNDATE < CONVERT(VARCHAR(15),@DayChange,103))

 set @CollectionAsPer=(select SUM(CurrentPaid) AS Collection from FeeReceipt where FranchiseCode=@FranchiseCode  
 and ReceiptDate < CONVERT(VARCHAR(15),@DayChange,103))

 set @ServiceTaxAsPer=(select SUM(STax) AS ServiceTax from FeeReceipt where FranchiseCode=@FranchiseCode  
and ReceiptDate < CONVERT(VARCHAR(15),@DayChange,103))

  set @ChequeReturnAsPer=(select SUM(AMOUNT) as  ChequeReturn from CHEQUERETURN where FranchiseCode=@FranchiseCode  
and vDATE < CONVERT(VARCHAR(15),@DayChange,103))

  set @FeeConcessionAsPer=(select SUM(AMOUNT) as  FeeConcession from FeeConcession where FranchiseCode=@FranchiseCode 
 and VDATE < CONVERT(VARCHAR(15),@DayChange,103))

  set @FeeRefundAsPer=(select SUM(AMOUNT) as  FeeRefund from FeeRefund where FranchiseCode=@FranchiseCode 
 and DATE < CONVERT(VARCHAR(15),@DayChange,103))

  set @BadDebtAsPer=(select SUM(AMOUNT) as  BadDebt from BadDebt where FranchiseCode=@FranchiseCode 
 and VDATE < CONVERT(VARCHAR(15),@DayChange,103))

 set @SmsSentAsPer=(select Count(*) as  SmsSent from SMSSentHistory where FranchiseCode=@FranchiseCode 
 and MDate < CONVERT(VARCHAR(15),@DayChange,103))

 set @RunningBatchesAsPer=(select Count(*) as  RunningBatches from BatchMaster where FranchiseCode=@FranchiseCode and BatchCompleteFlag='N' 
 and StartDate < CONVERT(VARCHAR(15),@DayChange,103))


 insert into #AsPerDateFinal (A,B,C,D,E,F,G,H,I,J)
        values (@TotalStudentsAsPer,@NewStudentsAsPer,@CollectionAsPer,@ChequeReturnAsPer,
                @FeeConcessionAsPer,@FeeRefundAsPer,@BadDebtAsPer,@SmsSentAsPer,@ServiceTaxAsPer,@RunningBatchesAsPer)
       

fetch next from C1 into @DayChange
end
close C1
deallocate C1

--select * from #AsPerDateFinal


;with mycte
 as
 (
SELECT rn,cols,val
FROM   (SELECT  row_number() over(order by (select(1))) rn ,A,B,C,D,E,F,G,H,I,J
        FROM  #AsPerDateFinal) AS src1
UNPIVOT (val FOR cols
IN ( A,B,C,D,E,F,G,H,I,J)) AS unpvt
 )
 
SELECT [1] as NintyDays ,[2] as SixtyDays ,[3] as ThirtyDays,[4] as SevenDays,[5] as Today
FROM   (SELECT rn,cols,val
        FROM   mycte) AS src2 PIVOT
( Max(val) FOR rn IN ([1], [2], [3],[4],[5])) AS pvt

end