Solved

How to Correlate Last weekday of Previous Month and First Weekday of Current Month in T-SQL for SSRS ?

Posted on 2010-09-17
4
304 Views
Last Modified: 2012-05-10
Hi friends,

below is my detail description.

I woulld like to show busines users Previous Months Last Weekday in Drop Down List as a Parameter.

Now, if they select Previous Months Last Weekday, then i need to pass First weekday of Current month in Store Procedure.

How can i correlate this things ?

if you need more information, let me know.



Thanks.
0
Comment
Question by:SQLNewBieUSA
  • 2
4 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 300 total points
ID: 33706308
SQLNewBieUSA,

If you already have the code to generate the Previous Month's Last WeekDay, then I would just leave that on the report end.  When you get the DateTime value in your procedure, you can just adjust to the 1st of the next month using datetime trick and then use loop or recursive cte (shown) to find the first day that is a weekday in the month.
declare @PrvMthLstDay datetime

set @PrvMthLstDay = '2010-07-30';



;with cte(dt) as (

   select dateadd(mm, datediff(mm, 0, @PrvMthLstDay)+1, 0)

   union all 

   select dt+1

   from cte 

   where datename(dw, dt) like 'S%'

) 

select dt

from cte

where not datename(dw, dt) like 'S%'

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33706318
If you don't have a function to find the last day, you can use a similar technique to the above, but go backwards.  You can see some other code relating to finding a specific day of the week in a month at the following Article:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_1639-Date-of-the-n-th-Day-in-a-Month.html

That is based on knowing the day of the week you are looking for, but with the CTE code I showed it should be a relatively easy task to customize it to your needs to find the first and last business day!
0
 
LVL 4

Assisted Solution

by:timexist
timexist earned 100 total points
ID: 33713802
I think you need to prepare the data from the first step.

1,you need to prepare the parameter table, in this table, you insert all the parameters that will show in the dropdown list.

2, in the stored procedure, based on the parameter, you can return the different dataset to report.
something like
create procedure dbo.mysp
@para int
as
begin
...

...
if @para= 1
begin
insert into myreturnTable
select * from #tmptable1
end
if @para=2
begin
insert into myreturnTable
select * from #tmptable1
end
...

select * from myreturnTable

end

3, the report always target the myreturnTable.


0
 

Author Closing Comment

by:SQLNewBieUSA
ID: 33824782
Solved
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now