Comparing date range quarters sql server
- by CR41G14
I have a policies in a system
PolRef Start End
POL123 22/11/2012 23/12/2014
POL212 24/09/2012 23/10/2012
POL214 23/08/2012 29/09/2012
I am asking a user for a reporting date, the user enters 24/10/2012 this becomes @StartDate
From this I derive what the quarter is by the month:
set @currentMonth = Month(@StartDate)
if @currentMonth = 1 or @currentMonth = 2 or @currentMonth = 3 begin set @startmonth = 1 set @endmonth = 3 end
if @currentMonth = 4 or @currentMonth = 5 or @currentMonth = 6 begin set @startmonth = 4 set @endmonth = 6 end
if @currentMonth = 7 or @currentMonth = 8 or @currentMonth = 9 begin set @startmonth = 7 set @endmonth = 9 end
if @currentMonth = 10 or @currentMonth = 11 or @currentMonth = 12 begin set @startmonth = 10 set @endmonth = 12 end
I then get a date range:
@quarterStartDate = CAST(CAST(YEAR(@StartDate) AS varchar) + '-' + CAST(@startMonth AS varchar) + '-' + '01') AS Date)
@quarterEndDate = CAST(CAST(YEAR(@EcdDate) AS varchar) + '-' + CAST(@endMonth AS varchar) + '-' + '31') AS Date)
This will give me 01-10-2012 and 31-12-2012. Basically I need a script to only bring back the policies that are in this quarter. The policy doesn't have to span the entire quarter date range, just exist in the quarter date range.
The results expected would be
PolRef Start End
POL123 22/11/2012 23/12/2014
POL212 24/09/2012 23/10/2012
Pol123 appears because it spans over the quarterly date range. Pol212 is there because it expires in that quarter date range. Pol214 does not appear because it neither spans, expires or starts in this quarter.
Any help would be greatly appreciated
