?
Solved

Business Dates for Previous Month

Posted on 2006-05-16
7
Medium Priority
?
304 Views
Last Modified: 2011-10-03
Good Morning Experts,

I have searched the database for a solution to this problem and have not found an answer so I am asking for your help.  I have a procedure that calulates the business days, exluding weekends and holidays from a holiday table.  The days are calculated from either the current date or a user input date for the EndDate and the First day of the month for the StartDate.  This gives me the number of business days between StartDate and EndDate.   I want to determine the business dates for  previous months.  For Example; If there are 12 business days currently in May, I want to know the end date in March and April that would equal 12 business days excluding the weekends and holidays.

@StartDate = May 1
@EndDate = May 16
@BusinessDays = 12

@LastMonthStartDate = April 1
@BusinessDays = 12
@LastMonthEndDate = ????      

Etc for previous six months.  

I am using this to compare sales with the same number of business days at any point in the month to determine if we are ahead or behind based on a business day and to determine trends for the previous six months.  

Thank you for your help.

0
Comment
Question by:tglucca
  • 3
  • 3
7 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16691747
Could you please define the schema for your holiday table?
0
 

Author Comment

by:tglucca
ID: 16692579
Table: tblHoliday
Field: sdtHoliday - SmallDateTime

All Weekend Dates and Holidays are in this field.
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 16696307
First, create a function to get maxdate

Create Function dbo.getmaxdate (@date smalldatetime,@num int)
Returns datetime
As
BEGIN
Declare @saledate datetime
select @saledate=max(saledate) from yourtable where
datepart(month,yourtable.saledate)=datepart(month,@date)
and datepart(year,yourtable.saledate)=datepart(year,@date)
 and datepart(day,saledate)-
(select count(*) from tblHoliday where tblHoliday.sdtHoliday between
dateadd(day,-datepart(day,yourtable.saledate),yourtable.saledate) and yourtable.saledate)
<=@num
return @saledate
END
GO

and try create sp

CREATE PROCEDURE calculate @enddate smalldatetime=null
AS
declare @startdate smalldatetime,@busdays int
if @enddate is null
begin
 set @enddate=getdate()
end
set @startdate=dateadd(day,-datepart(day,@enddate),@enddate)
select @busdays=datediff(day,@startdate,@enddate)-(select count(*)
from tblHoliday where sdtHoliday between @startdate and @enddate)

select TOP 6 datepart(month,saledate),SUM(salequantity) from yourtable
where saledate <dbo.getmaxdate(saledate,@busdays)
group by datepart(month,saledate)
order by datepart(month,saledate) DESC

GO
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 17

Expert Comment

by:HuyBD
ID: 16696335
If you dont want to count quantity of products sale at holidays and weekend, try to add a codition

and saledate not in(select sdtHoliday from tblHoliday)

CREATE PROCEDURE calculate @enddate smalldatetime=null
AS
declare @startdate smalldatetime,@busdays int
if @enddate is null
begin
 set @enddate=getdate()
end
set @startdate=dateadd(day,-datepart(day,@enddate),@enddate)
select @busdays=datediff(day,@startdate,@enddate)-(select count(*)
from tblHoliday where sdtHoliday between @startdate and @enddate)

select TOP 7 datepart(month,saledate),SUM(salequantity) from yourtable
where saledate <dbo.getmaxdate(saledate,@busdays)
and saledate not in(select sdtHoliday from tblHoliday)
group by datepart(month,saledate)
order by datepart(month,saledate) DESC

GO

Change to "TOP 7" to select current month and 6 previous months

Hope this help
0
 

Author Comment

by:tglucca
ID: 16700909
This works as designed and almost gives me what I need.  The date part works great.  I forgot to mention in my original message that I want this to group by Sales Rep.  So I need the group by to be SaleRep, Month for the previous 6 months.  How do I change the TOP 6 part to do that.

Thanks for your help.  
0
 
LVL 17

Accepted Solution

by:
HuyBD earned 1000 total points
ID: 16705272
Add codition that get date between 6 previous months

CREATE PROCEDURE calculate @enddate smalldatetime=null
AS
declare @startdate smalldatetime,@busdays int
if @enddate is null
begin
 set @enddate=getdate()
end
set @startdate=dateadd(day,-datepart(day,@enddate),@enddate)
select @busdays=datediff(day,@startdate,@enddate)-(select count(*)
from tblHoliday where sdtHoliday between @startdate and @enddate)

select SaleRep,datepart(month,saledate),SUM(salequantity) from yourtable
where saledate <dbo.getmaxdate(saledate,@busdays)
and saledate not in(select sdtHoliday from tblHoliday) and saledate between dateadd(month,-6,@startdate) and @startdate
group by SaleRep,datepart(month,saledate)
order by SaleRep,datepart(month,saledate)

GO
0
 

Author Comment

by:tglucca
ID: 16710947
Thank you very much for your help on this.  It is just what I need.  
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

839 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