Add Parameter into the report and pass it to a function

[kate8]

Hi All,

I need add a parameter which it is the year into the report, then pass it to a stored procedure, then sp passes it to a function. In the function, base on the Year user selects, for example, Year 2014, value is 14, the set up the start date and end date for each quarter (the company's Year is not calendar year), so if the @Year is 2014, then the first quarter start date should be set to '10/1/@Year-1', enddate = '12/31/@Year-1', second quarter startDate='1/1/@Year', endDate = '3/31/@Year' and so on...
When I create sp, I pass the parameter, also in function, but when I pass it to function, how can I set it up to those quarter startdate and enddate?

CREATE PROCEDURE [dbo].[sp_Test] @Year int = 14
select *
from Fn_Test(@Year int)
...

CREATE function[dbo].[Fn_Test](@Year)
returns table
as
return
...
Thank you so much for any helps and suggestions!!!

 

[SQLScholar]

Hi,

I am not sure if you even need a table function for this.

Can you explain what the report does? So it takes in a year - but then it has to work out the quarters for what reason?

I am not trying to be awkward but it sounds like you are thinking about this in the wrong way. For a starting point you can find the quarter that a day falls into by using the datepart function on the date.

Perhaps the best way to do this is if you show an example of how your data looks like, and then what you want on the report.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[kate8]

Dan,
Thank you so much for your response!!
You are right, the way I tried to get the quarter day is not the efficient way to do it.The report was ran for a year which was set in the report before, and I was asked to modify it to let users select the year to run.
Now I use DATEADD() to do it, when user select year 2014, the report will set quarter 1 (company's year is not the same as calendar year)start date 10/1/2013 as: DATEADD(dd,9,DATEADD(yy,@Year-1901,0)), end date as 12/31/2013: DATEADD(mm,11,DATEADD(yy,@Year-1901,30)), so on. The report provides the result for each quarter in year 2014. I believe there are other ways to work it around.
Again, thank you.