Solved

# Business Dates for Previous Month

Posted on 2006-05-16
Medium Priority
304 Views
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

@LastMonthStartDate = April 1
@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.

0
Question by:tglucca
• 3
• 3

LVL 34

Expert Comment

ID: 16691747
0

Author Comment

ID: 16692579
Table: tblHoliday
Field: sdtHoliday - SmallDateTime

All Weekend Dates and Holidays are in this field.
0

LVL 17

Expert Comment

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
<=@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
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

LVL 17

Expert Comment

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

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.

0

LVL 17

Accepted Solution

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

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

## Featured Post

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
Course of the Month14 days, 16 hours left to enroll