asked on

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,

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ali Shah

ASKER

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