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  Development Tools  Reporting Services Development  How to List all the dates since 10years as a date

Author  Topic  technet Starting Member 3 Posts Posted - 2011-01-13 : 14:03:22 Could any one pls help in how to list 10years back to current date as a date parameter in SSRS ? . I am using a Stored Proc to create a report. I am using a seperate dataset ( stored proc) to create a list of dates as a report parameter.. Here is the main procedure whish I used as one of the dataset to create a report where @dtCashHoldings is declared as VARCHAR(10).Pls help me with the stored proc for dates to assign it to @dtCashHoldings CREATE PROCEDURE dbo.i_spExcessiveAccountCashHoldings @dtCashHoldings VARCHAR(10), @PercGT decimal(6,3) = 15, --Percent Greater Than @PercLT decimal(6,3) = 100, --Percent Less Than @Age int = 90 --Aging AS DECLARE @EligibleAccounts TABLE (Entity_id VARCHAR(8), AccountActivationDate DATETIME ) DECLARE @Results TABLE (ShortName VARCHAR(10), LegalName VARCHAR(200), CustodianAccountNumber VARCHAR(15), AccountActivationDate DATETIME, ProductCode VARCHAR(10), Cash_Value DECIMAL(15,2), Total_Market_Value DECIMAL(15,2), CashPercentage DECIMAL(9,2) ) INSERT INTO @EligibleAccounts SELECT DISTINCT Entity.Entity_ID, EntityDate.Date FROM Rules..Entity AS Entity INNER JOIN Rules..PRP_Entity_Dates AS EntityDate ON Entity.Entity_ID = EntityDate.Entity_ID AND EntityDate.Date_Type = 'Account Activation Date' WHERE Entity.User_Field5 not in('SPC') --Entity.User_Field5=FirmReference code. --Don't use Entity.User_field2 to elimiate the firms. And Entity.User_field7 NOT IN ('SEC','RAM','CSH','TAP','CHD','MT3','BO2') --Entity.User_Field7=ManagerID --Entity.User_field4=Product Reference Code. --Extract the product Reference Code from --Entity_product table --using Manager ID or Entity.User_field7 And Entity.User_field4 NOT IN ( 'AKI11','CSH01','RAM01','SEC01','SEC02','SEC03', 'SEC04','SEC05','SEC06','SEC07','SEC08','SEC09', 'SEC10','SEC11','SEC12','SEC13','SEC14','SEC15', 'SEC16','SEC17','SEC18','SEC19','SEC20','SEC21', 'SEC22','SEC88','SEC98','SEC99','TAP01','TAP02', 'TAP03','TAP04','TAP05','TAP06' ) AND Entity.User_Field10 NOT IN (90,99,98) --Entity.User_Field10=RR Code AND EntityDate.Date < CONVERT(VARCHAR(10), DATEADD(DAY, -@Age, GETDATE()), 101) INSERT INTO @Results (ShortName, LegalName, CustodianAccountNumber, AccountActivationDate, ProductCode, Cash_Value, Total_Market_Value, CashPercentage ) SELECT Entity.Entity_ID, Entity.Legal_Name, Entity.Custody_Bank, Accounts.AccountActivationDate, Entity.User_Field4, SUM(LotLevelPosition.Market_Value_Income), Position.Total_Market_Value_Income, CASE WHEN ISNULL(Position.Total_Market_Value_Income,0) = 0 THEN 0 ELSE ((SUM(LotLevelPosition.Market_Value_Income) / Position.Total_Market_Value_Income) * 100) END FROM Rules..Entity AS Entity INNER JOIN @EligibleAccounts Accounts ON Entity.Entity_id = Accounts.Entity_id INNER JOIN Holding..Position AS Position ON Accounts.Entity_id = Position.Entity_ID INNER JOIN Holding..Lot_Level_Position AS LotLevelPosition ON Position.Position_ID = LotLevelPosition.Position_ID AND Position.Effective_Date = @dtCashHoldings AND LotLevelPosition.Security_Alias = 10409 GROUP BY Entity.Entity_ID, Entity.Legal_Name, Entity.Custody_Bank, Accounts.AccountActivationDate, Entity.User_Field4, Position.Total_Market_Value_Income SELECT ShortName AS 'Short Name', LegalName AS 'Legal Name', CustodianAccountNumber AS 'Custodian Account Number', AccountActivationDate AS 'Account Activation Date', ProductCode AS 'Product Code', Cash_Value AS 'Cash_Value', Total_Market_Value AS 'Total_Market_Value', CashPercentage AS 'Cash Percentage' FROM @Results WHERE CashPercentage >= @PercGT AND CashPercentage <= @PercLT