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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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 article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

805 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