# BiMonthly SQL Query

Hi there,
I have requirement to create data entry points to update data on daily, weekly, bi-monthly, monthly etc bases.
I have managed to create the data points for daily, weekly, monthly, quarterly and yearly bases however struggling to create Bi-Monthly points.

For example my weekly data points look like this
``````IF @freq='W'

BEGIN
Set @Diff =datediff(WEEK,@StartDate,@DueDate) + 52

While (@Diff !=0)
Begin
Insert Into ObjValues (ObjID,MeasID,PeriodDate,Upd_Status)
Values (@ObjID,@MeasID,@FirstDate,'U')

Set @Diff = @Diff - 1

End
End
``````

Using this can you please advise on how to create Bi-Monthly values.

kindest regards,
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Senior .Net DeveloperCommented:
Bi monthly can be defined in a couple of different ways:
1) Every two weeks
2) the 1st and the 15th of the month
3) the 15th and the last day of the month

you might be better using your monthly datapoint and adding the 15th of the month.

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

SQL DeveloperAuthor Commented:
Thanks using your suggestion I have done it for the 15th and the last day of month using my month data point.

The code looks like this

Else IF @freq='BM'

BEGIN
Set @Diff =datediff(MONTH,@StartDate,@DueDate) + 12
Set @FirstDate = dbo.GetLastDayOfMonth(@StartDate)
Set @Fifteenday = dbo.GetFifteenOfMonth(@StartDate)

While (@Diff !=0)
Begin

Insert Into ObjValues (ObjID,MeasID,PeriodDate,Upd_Status)
Values (@ObjID,@MeasID,@Fifteenday,'U')

Insert Into ObjValues (ObjID,MeasID,PeriodDate,Upd_Status)
Values (@ObjID,@MeasID,@FirstDate,'U')

Set @Diff = @Diff - 1

End
End

My Fifteenday function looks like this

ALTER FUNCTION [dbo].[GetFifteenOfMonth] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

DECLARE @vOutputDate        DATETIME

-- this is calculating the first day of the month
SET @vOutputDate = CAST(FLOOR(CAST(@pInputDate AS DECIMAL(12, 5))) -
(DAY(@pInputDate) - 1) AS DATETIME)

--this is adding 15 days to the first day of the month to give us the 15th day of the month
SET @vOutputDate =cast(convert(varchar(10), dateadd(dd,datediff(dd,datepart(dd,@vOutputDate),15),@vOutputDate), 101) as datetime)

RETURN @vOutputDate

END

and the last daymonth function is

ALTER FUNCTION [dbo].[GetLastDayOfMonth] ( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

DECLARE @vOutputDate        DATETIME

-- this is calculating the first day of the month
SET @vOutputDate = CAST(FLOOR(CAST(@pInputDate AS DECIMAL(12, 5))) -
(DAY(@pInputDate) - 1) AS DATETIME)

--this is adding a month to the first day of the month to give us the last day of the month