| Author |
 Topic  |
|
|
2revup
Posting Yak Master
112 Posts |
 Posted - 04/19/2013 : 02:19:40
|
To explain this one its best I show you a working SQL query:
DECLARE @startdate datetime2 = '2013-01-01 00:00:00.0000000';
DECLARE @enddate datetime2 = GETDATE();
DECLARE @interval int = 30;
--Set @enddate = DATEADD(d, 1, @enddate) --comment this out if you do not want to include the end date
Set @startdate = DATEADD(d, -@interval, @enddate)
--print @startdate
Select Distinct UserLogin, cast(count(UserLogin) as decimal(38,0)) / @interval as 'DailyAvg'
From Correspondences_All
Where creation_date BETWEEN @startdate AND @enddate
group by UserLogin
So this takes the start date and minus 30 days and outputs a new date, and then with the NEW date range will calculate an AVG.
My problem is that I cant do this in report builder / RS query
Set @startdate = DATEADD(d, -@interval, @enddate)
because the startdate is defined by the user via the calendar drop down. How on EARTH can I get this DATEADD(d, -@interval, @enddate)in there to do my calculation.
I hope this makes sense, its a hard one to try an explain.
|
Edited by - 2revup on 04/19/2013 02:29:15
|
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 04/19/2013 : 02:23:23
|
I think what I might be trying to do might cancel out the users selected date out... THis could be pointless. Can someone validate that please.
|
Edited by - 2revup on 04/19/2013 02:47:15 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 04/19/2013 : 02:48:12
|
why not add it as parameter in report and use corresponding SSRS expression? like
DateAdd("d",-1 * Parameters!interval.value,Parameters!EndDate.value)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 04/19/2013 : 20:06:29
|
You gave me my answer once again - you are brilliant
Select UserLogin,Location, supervisor, count(UserLogin), cast(count(UserLogin) as decimal(38,0)) / @interval as 'DailyAvg'
From cases
where creation_date between
DateAdd("d",-1 * @interval ,@endDate) and @endDate
group by UserLogin, Location, supervisor |
|
|
| |
Topic |
|
|
|
HELP... setting variable in RS