January 25, 2005 at 12:32 pm
Hello Gentlemen
Below is a SQL Function that I use to create a report that returns all open calls minus all closed with a total by Company on one line.
This report also repeats this, for all companies showing open - closed =total sorting by company. At the end of this report all columns are added for grand totals.
Here's my problem, with this function call I can not get away from the seven days. I would like to be able to pick any time frame and get the same results.
Granted there are more views and functions that make this report work, but this one is the base, I'll gladly send any others you want to see.
Thank, Nathan
BEGIN
DECLARE @Year varchar(4);
DECLARE @Month varchar(2);
DECLARE @Day varchar (2);
DECLARE @Ret varchar(10);
SET @Year = CAST(DATEPART(yyyy, DATEADD(d, - 7, @Date)) AS varchar(4));
SET @Month = CAST(DATEPART(m, DATEADD(d, - 7, @Date)) AS varchar(2)) ;
SET @Day = CAST(DATEPART(d, DATEADD(d, - 7, @Date)) AS varchar(2));
IF @Month LIKE "_"
SET @Month = "0" + @Month;
IF @Day LIKE "_"
SET @Day = "0" + @Day;
SET @Ret = @Year + '-' + @Month + '-' + @Day;
RETURN @Ret
END
January 25, 2005 at 3:35 pm
Alright maybe it is me but it looks like you are going out of your way to do this
replace(convert(char(10),dateadd(d,-7,@date),102),'.','-')
or
convert(char(10),dateadd(d,-7,@date),120)
in your function.
and if you are wanting to be able to adjust the date we need to know the setup of how @date is set when function is used and how you plan to asjust the -7 range , could be another parameter you pass.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply