Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
311 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1200 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 60

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 400 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

636 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