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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

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 article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

860 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