CODEWITHSUNDEEP
sqlsql-server-2008
david michell
david michell

Reputation: 71

Quarterly Date calculation

I have a problem on scheduling jobs on the first date of the last month of each quarter. This would be very easy as I can get the last month of every quarter and then append the first date to it like 

select START_DATE,
CONVERT(datetime,CAST(YEAR(START_DATE) as VARCHAR(4))+'/'+
                 CAST(DATEPART(Q,START_DATE)*3 as VARCHAR(2))+'/01 00:00:00',120
       ) as SCHEDULE_DATE_LAST_MONTH_OF_QUARTER
from JOB_SCHEDULE_CONFIGURATION

The stumbling block is the START_DATE. I will illustrate with an example

START_DATE SCHEDULE_DATE_LAST_MONTH_OF_QUARTER
2012/05/01 2012/06/01 --correct schedule
2012/02/15 2012/03/01 --correct schedule
2012/06/15 2012/06/01 ---problem at this line I will explain why

The start date happens to be 2012/06/15 and I cannot schedule a job prior to the start date ie 2012/06/01. I have to schedule it only after 2012/06/15 that means the job should be scheduled on the next quarter ie 2012/09/01

How should the query be modified? I think there might be some math involved and I really don't excel in math. Please help.

Thanks

David

Upvotes: 1

Views: 2294

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

declare @T table
(
  START_DATE date
)

insert into @T values
('2012-05-01'),
('2012-02-15'),
('2012-06-15')

select dateadd(month,
               2,
               dateadd(quarter,
                       datediff(quarter,
                                0,
                                dateadd(month,
                                        case when day(START_DATE) >= 15
                                          then 1
                                          else 0 
                                        end,
                                        START_DATE)),
                       0))
from @T

Update:

The main part of this query is to get the first date of a quarter and that is done by the dateadd/datediff trick.

select dateadd(quarter, datedifF(quarter, 0, START_DATE), 0)
from @T

Result:

-----------------------
2012-04-01 00:00:00.000
2012-01-01 00:00:00.000
2012-04-01 00:00:00.000

You wanted the last month of the quarter so we add a dateadd(month, 2

select dateadd(month, 2, dateadd(quarter, datedifF(quarter, 0, START_DATE), 0))
from @T

Result:

-----------------------
2012-06-01 00:00:00.000
2012-03-01 00:00:00.000
2012-06-01 00:00:00.000

That leaves only the part where dates later than the 15th of the last month should be in the end of the next quarter. day(START_DATE) will give you the day of the month so we can add 1 month to START_DATE if day(START_DATE) >= 15 using a case statement.

select dateadd(month, case when day(START_DATE) >= 15 then 1 else 0 end, START_DATE)
from @T

Result:

----------
2012-05-01
2012-03-15
2012-07-15

Putting it all together it will look like this.

select dateadd(month, 2, dateadd(quarter, datedifF(quarter, 0, dateadd(month, case when day(START_DATE) >= 15 then 1 else 0 end, START_DATE)), 0))
from @T

Result:

-----------------------
2012-06-01 00:00:00.000
2012-03-01 00:00:00.000
2012-09-01 00:00:00.000

Upvotes: 2

Related Questions