Function to Check Date Exists in Which Quarter where fiscal Year Starts other than 1 January in SQL

Function to Check Date Exists in Which Quarter where  fiscal Year Starts other than 1 January  in SQL

Yesterday i have a requirement like this where i have to calculate Numbers of Product Sold  in

Current Quarter,Previous Quarter and  Total Year.

I thought it was simple 

Using DateName function of SQL i can extract Quarters and Group by based on that.

But the condition was that in some countries the financial Year started from October 1 and ends at September 30.

so first condition is to use October 1 as First Date of Year and 30 September as Last Date of Year

and second condition was like that if the Current Date Falls in any of Quarter then that Quarter will be Considered  as current Quarter....

so for this i have to manually create quarters using DateAdd and DateDiff Functions  to check 

1-Current Date in which quarter--To get Product sold in Current Quarter 

2-Based on Current Quarter Calculating Previous Quarter

2-Based on that  will have count of Product sold in between current Quarter Previous Quarter 

and This Year i.e 1 oct to 30 sep

For this I have created a function

Input Parameter Date

Output Table with below columns

QuarterNo StartDate LastDate

Function Code

--function to calculate Current and Previous Quarters Starting and Ending Date Based on Current Date

Create FUNCTION DateinQuarter(@Date Datetime)

RETURNS @DateInQuarters TABLE

 (

   QuarterNo  nvarchar(50)      NOT NULL,--Quarter Detail Current and Previous

   StartingDate   datetime     NOT NULL,

   LastDate datetime      NOT NULL

 ) 

AS

BEGIN

declare @firstdateofyear datetime,@EndOfYear datetime,@EndofFirstQuarter  datetime,@EndofSecondQuarter datetime ,@EndofThirdQuarter datetime,@LastQuarter datetime

select @firstdateofyear=dateadd(mm,-3,DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, 0, GetDate())), 0)),--First Date of Year i.e. October 1

@EndOfYear=dateadd(day,-1,dateadd(yy,1,dateadd(mm,-3,DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, 0, GetDate())), 0)))), --last Date of Year i.e.30 Sept

@EndofFirstQuarter=dateadd(day,-1,dateadd(mm,3,dateadd(mm,-3,DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, 0, GetDate())), 0)))) ,--31 Dec

@EndofSecondQuarter=dateadd(day,-1,dateadd(mm,6,dateadd(mm,-3,DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, 0, GetDate())), 0))))--31 Mar

 ,

@EndofThirdQuarter=dateadd(day,-1,dateadd(mm,9,dateadd(mm,-3,DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, 0, GetDate())), 0)))),--30 June

@LastQuarter=dateadd(day,-1,dateadd(mm,12,dateadd(mm,-3,DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, 0, GetDate())), 0)))) --30 Sep

Insert into Table Based on Checking of  Current Date

--checking Date between 1 oct to 31 Dec

if (@Date between @firstdateofyear and @EndofFirstQuarter)

   INSERT INTO @DateInQuarters (QuarterNo, StartingDate, LastDate)

   values('CurrentQuarter',@firstdateofyear,@EndofFirstQuarter),

   ('PreviousQuarter',@LastQuarter+1,@firstdateofyear)

   --checking Date between 1 jan to 31 mar

 if (@Date between @EndofFirstQuarter+1 and @EndofSecondQuarter)

   

   INSERT INTO @DateInQuarters (QuarterNo, StartingDate, LastDate)

   values('CurrentQuarter',@EndofFirstQuarter+1,@EndofSecondQuarter),

   ('PreviousQuarter',@firstdateofyear,@EndofFirstQuarter)

  --checking Date between 1 apr to 30 June

if (@Date between @EndofSecondQuarter+1 and @EndofThirdQuarter)

   

   INSERT INTO @DateInQuarters (QuarterNo, StartingDate, LastDate)

   values('CurrentQuarter',@EndofSecondQuarter+1,@EndofThirdQuarter),

    ('PreviousQuarter',@EndofFirstQuarter+1,@EndofSecondQuarter)

--Checking Date Between 1 July to 30 Sep  

 if (@Date between @EndofThirdQuarter+1 and @LastQuarter)

   

   INSERT INTO @DateInQuarters (QuarterNo, StartingDate, LastDate)

   values('CurrentQuarter',@EndofThirdQuarter+1,@LastQuarter),

    ('PreviousQuarter',@EndofSecondQuarter+1,@EndofThirdQuarter)

   

   

   RETURN;

END;

select * from dbo.DateinQuarter(getdate())

Output

QuarterNo           StartingDate                                  LastDate

CurrentQuarter 2013-07-01 00:00:00.000 2013-09-30 00:00:00.000

PreviousQuarter 2013-04-01 00:00:00.000 2013-06-30 00:00:00.000

Now Notice that the current Date i.e 29-Aug-2013 Falls in Last Quarter assuming as 1 October is first Date of Year i.e 29-Aug-2013 is the last Quarter which starts from 2013-07-01 to 2013-09-30 and Previous Quarter before the Current Quarter Starts.

So i Used this Function in my Query to get Count of  Product Sold in Current Quarter,Previous Quarter and Current Year

Keep in Mind that we are assuming that our fiscal Years starts from 1 oct to sep 30

--In order to have first and Last Date of year I am Using the Above Variable 

two Variables @firstdateofyear,@EndOfYear

declare @firstdateofyear datetime,@EndOfYear datetime, @CQstartingDate datetime, @CQEndingDate datetime,@PQstartingDate Datetime,@PQEndingDate datetime 

select @firstdateofyear=dateadd(mm,-3,DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, 0, GetDate())), 0)),

@EndOfYear=dateadd(day,-1,dateadd(yy,1,dateadd(mm,-3,DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, 0, GetDate())), 0)))) 

  

--Capturing Current Quarter  Starting and Ending Date given by Function DateinQuarter

select @CQstartingDate=startingDate,@CQEndingDate=LastDate  from dbo.DateinQuarter(GETDATE()) where quarterno like 'currentquarter'

--Capturing Previous Quarter  Starting and Ending Date given by Function DateinQuarter

select @PQstartingDate=startingDate,@PQEndingDate=LastDate  from dbo.DateinQuarter(GETDATE()) where quarterno like 'previousquarter' 

--Here I have Used Distinct to get count of only distinct ProductSold.

 Select  

Count(distinct CASE WHEN [ProductSoldDate] between @firstdateofyear and @EndOfYear THEN  ProductName END) AS ThisYear,

Count( distinct CASE WHEN [ProductSoldDate] between @CQstartingDate and @CQEndingDate  THEN  ProductName END) AS ThisQuarter,

Count(distinct CASE WHEN [ProductSoldDate]  between @PQstartingDate and @PQEndingDate THEN  ProductName END) AS LastQuarter

FROM Products.

Output

ThisYear ThisQuarter LastQuarter

123            115                        96

Note: You can GetQuarter Names Using Datepart Functions also but here we are Using 1 october as first date of years thats why we have created a Function

The Reason I have Blogged on this as i want to save developer time So to use this function as a reference where they are facing Same Issue like me.