CODEWITHSUNDEEP
sqlsql-server
Ramdeo angh
Ramdeo angh

Reputation: 179

Find records between two Quarters in SQL Server?

I want to find records between two-quarters of different years in SQL Server.

SELECT    ('Q'+cast(DATEPART(QUARTER,calldate) as varchar(3))+'-'+cast(YEAR(calldate) as varchar(4)))
period,providerid,volume as volume,type  
FROM  table V    
where DATEPART(QUARTER,calldate) between @Q1 and @Q2 and                             
datepart(year,calldate) in (@Y1,@Y2)  and providerid=@carrierID

Here Q1=4 and Q2=3 and @Y1=2014,@Y2=2016

 Now,I have only records from Q2-2016, So it should return available records      
 but I am getting blank rows.
 if I change the parameter like this 
 Here Q1=3 and Q2=4 and @Y1=2014,@Y2=2016 then i am getting records.

I want all records between these two-quarters like (Q3-2014 and Q2-2016).?

Upvotes: 0

Views: 412

Answers (2)

Cato
Cato

Reputation: 3701

could try something like this

SELECT    
 ('Q'+cast(DATEPART(QUARTER,calldate) as varchar(3))+'-'+cast(YEAR(calldate) as varchar(4)))
period,providerid,volume as volume,type  
FROM  table V    
where DATEPART(QUARTER,calldate) + datepart(year,calldate) *4  between @Q1 + @Y1 * 4  and @Q2 + @Y2 * 4

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Here is one method:

where datename(year, calldate) + datename(quarter, calldate)
          between @Y1 + @Q1 and @Y2 + @Q2;

This assumes that the variables are actually strings. You could also do this using numbers:

where datepart(year, calldate) * 10 + datename(quarter, calldate)
          between @Y1 * 10 + @Q1 and @Y2 * 10 + @Q2;

And, here is a way that would use indexes:

where calldate >= datefromparts(@Y1, @Q1*3 - 2, 1) and
      calldate < (case when @Q2 = 4
                       then datefromparts(@Y2 + 1, 1, 1)
                       else datefromparts(@Y2, @Q2*3 - 2 + 3, 1)
                  end)

Upvotes: 2

Related Questions