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
			Set @FirstDate = dateadd(wk,datediff(wk,5,@StartDate),5) 

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

					Set @FirstDate = DateAdd(WEEK,1,@FirstDate)
					Set @Diff = @Diff - 1

				End
		End

Open in new window


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

kindest regards,
Ali ShahSQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Kyle AbrahamsSenior .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

Your issues matter to us.

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

Start your 7-day free trial
Ali ShahSQL 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 @Fifteenday = dbo.GetFifteenOfMonth(DateAdd(Month,1,@FifteenDay))
                              Set @FirstDate = dbo.GetLastDayOfMonth(DateAdd(MONTH,1,@FirstDate))
                              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
    SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))

    RETURN @vOutputDate

END
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.