SSRS Rerport filter data between current Week

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

SQL Server 2005 Forums

Transact-SQL (2005)

SSRS Rerport filter data between current Week

Author

Topic

yomi
Starting Member

25 Posts

Posted - 2014-05-10 : 02:41:44

Hi All,
I want to create report that will show currnt week data.
for that i want to applay date filter.
But date filter will be like,
If today is sunday then it will show only sunday data for current week.
If today is Monday then it will show only sunday and monday data for current week.
If today is tuesday then it will show only sunday and monday and tueday data for current week.
and so on

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-05-11 : 09:04:34

Maybe not the most ideal method but I hope this helps and gives you an idea.

Declare @Date Datetime -- Date Parameter from Report
declare @RunToDate Datetime

--set @Date = GETDATE()

If DATENAME (dw,@Date) = 'SUNDAY'
set @RunTodate = @DATE --
Begin
Select
*
from TABLE
WHERE
CONVERT(varchar(10), TABLE.FIELD,103) BETWEEN
convert (varchar (10),@RunTodate,103) and convert (varchar (10),@Date,103)
end

If DATENAME (dw,@Date) = 'Monday'
set @RunTodate = DATEADD (dw,-1, @DATE)
Begin
Select
*
from TABLE
WHERE
CONVERT(varchar(10), TABLE.FIELD,103) BETWEEN
convert (varchar (10),@RunTodate,103) and convert (varchar (10),@Date,103)
end

If DATENAME (dw,@Date) = 'Tuesday'
set @RunTodate = DATEADD (dw,-2, @DATE)
Begin
Select
*
from TABLE
WHERE
CONVERT(varchar(10), Q_Viewdate,103) BETWEEN
convert (varchar (10),@RunTodate,103) and convert (varchar (10),@Date,103)
end

If DATENAME (dw,@Date) = 'Wednsday'
set @RunTodate = DATEADD (dw,-3, @DATE)
Begin
Select
*
from TABLE
WHERE
CONVERT(varchar(10), TABLE.FIELD,103) BETWEEN
convert (varchar (10),@RunTodate,103) and convert (varchar (10),@Date,103)
end

Marcus

I learn something new everyday.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources