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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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 a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

737 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