For Each Quarter Between Two Dates, Add Rows Quarter by Quarter in SQL Server

For each quarter between two dates, add rows quarter by quarter in SQL SERVER

If you cannot make another reference table/etc, you can use DATEDIFF (and DATEPART) using quarters, and then some simple date arithmetic.

The logic below is simply to find, for each startdate, the first quarter and then the number of additional quarters to get to the maximum. Then do a SELECT where the additional quarters are added to the startdate, to get each quarter.

The hardest part of the query to understand imo is the WITH numberlist section - all this does is generate a series of integers between 0 and the maximum number of quarters difference. If you already have a numbers table, you can use that instead.

Key code part is below, and here's a full DB_Fiddle with some additional test data.

CREATE TABLE #yourtable (id int, startdate date, enddate date)
INSERT INTO #yourtable (id, startdate, enddate) VALUES
(1, '2019-04-02', '2020-09-17'),
(2, '2019-08-10', '2020-08-20')

; WITH number_list AS
        -- list of ints from 0 to maximum number of quarters
        (SELECT n 
            FROM (SELECT ones.n + 10*tens.n AS n
                    FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
                         (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n)
                ) AS a
            WHERE n <= (SELECT MAX(DATEDIFF(quarter,startdate,enddate)) FROM #yourtable)
        )
    SELECT  id,
            YEAR(DATEADD(quarter, number_list.n, startdate)) AS [Year],
            DATEPART(quarter, DATEADD(quarter, number_list.n, startdate)) AS [Quarter]
    FROM    (SELECT id, startdate, DATEDIFF(quarter,startdate,enddate) AS num_additional_quarters FROM #yourtable) yt
            CROSS JOIN number_list
    WHERE   number_list.n <= yt.num_additional_quarters 

DROP TABLE #yourtable

Calculate quarter for dates given an example end date and quarter number

Assuming that you have two input variables:

declare @quarter_end date = '2021-01-31';
declare @current_quarter int = 4;

You can calculate the first month of financial year:

declare @first_month_of_fy int = (month(@quarter_end) - @current_quarter * 3 + 12) % 12 + 1;
-- 2 i.e. February

And use that value to calculate the quarter and year for any date using some math:

select *
from (values
    ('2020-12-15'),
    ('2021-01-15'),
    ('2021-12-15'),
    ('2022-01-15')
) as t(testdate)
cross apply (select
    (month(testdate) - @first_month_of_fy + 12) % 12 + 1
) as ca1(month_of_fy)
cross apply (select
    (month_of_fy - 1) / 3 + 1,
    year(dateadd(month, 12 - month_of_fy, dateadd(day, - day(testdate) + 1, testdate)))
) as ca2(fy_quarter, fy_year)

DB<>Fiddle

Split dates into quarters based on start and end date

If I understand correctly, the recursive CTE would look like:

with cte as (
      select id, start_date, num_quarters
      from t
      union all
      select id, dateadd(month, 3, start_date), num_quarters - 1
      from cte
      where num_quarters > 1
     )
select *
from cte;

Here is a db<>fiddle.

How to create customized quarter based on a given date column sql sql server

you need to create lookup table

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=816974201a48ed0bdf6782c121e1f8db

then you will use this table and join it to using between ( start_q end_q ) to get your customized quarter.

Select Previous x Quarter dates

A recursive CTE is a viable approach:

WITH quarters AS (
      SELECT datefromparts(year(getdate()), 1 + month(getdate()) / 3, 1) as q, 1 as num
      UNION ALL
      SELECT dateadd(quarter, -1, q), num + 1
      FROM quarters
      WHERE num < 12
     )
SELECT q
FROM quarters;

Here is a db<>fiddle.

Get Quarter and Year between two dates

You can use a tally table to do this.

declare @start date='2015-12-25';
declare @end date = '2017-06-30';

select distinct year(dateadd(day,rnum,@start)) yr,
datepart(quarter,dateadd(day,rnum,@start)) qtr
from (select row_number() over(order by (select null)) as rnum 
      from master..spt_values) t
where dateadd(day,rnum,@start) <= @end;

How to find total quarters between two dates and copy data n-1 number of times?

This can be done in several ways using recursive CTE, tally table. This solution uses master.dbo.spt_values as tally table. I have changed calculation of column Qrts, check if that works.

with cte as (
    Select 
        ID,IssueDate,ExpirationDate,Principal,Interest
        , Qrts = ceiling((datediff(mm, IssueDate, ExpirationDate)) / 3.0)
        , (Principal*Interest) AS Calcs
    From 
        Reporting_Table
)
select
    c.*
from
    cte c
    join master.dbo.spt_values v on c.Qrts > v.number
where
    v.type = 'P'

---

Related Topics