Pirate X
Reputation: 3093
Populating actual dates in recursive function
I am trying to create a table which contains Fiscal day,month and year. However I want to add an actual date column in the give result as well.
My Query -
(FISCAL_DAY, BEGIN_DATE ,END_DATE ,FISCAL_MONTH,FISCAL_QUARTER,FISCAL_YEAR ) AS
(SELECT CAST(1 AS INT) ,begin_date,end_DATE,FISCAL_MONTH,FISCAL_QUARTER,FISCAL_YEAR FROM DB_NAME.ORIGINAL_FISCAL_TABLE
UNION ALL
SEL Fiscal_Day+1,begin_date,end_DATE,FISCAL_MONTH,FISCAL_QUARTER,FISCAL_YEAR
FROM TMP_FISCAL_DAY WHERE BEGIN_DATE<END_DATE AND FISCAL_DAY<END_DATE-BEGIN_DATE)
SEL * FROM TMP_FISCAL_DAY
Output
+------------+------------+------------+--------------+----------------+-------------+
| FISCAL_DAY | BEGIN_DATE | END_DATE | FISCAL_MONTH | FISCAL_QUARTER | FISCAL_YEAR |
+------------+------------+------------+--------------+----------------+-------------+
| 1 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 2 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 3 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 4 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 5 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 6 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 7 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 8 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 9 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 10 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 11 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 12 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 13 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 14 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 15 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 16 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 17 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 18 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 19 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 20 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 21 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 22 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 23 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 24 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 25 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 26 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 27 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 28 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 29 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 30 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 31 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 32 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 33 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
| 34 | 12/30/2017 | 02/02/2018 | 12 | 4 | 2,018 |
+------------+------------+------------+--------------+----------------+-------------+
Expected output
+------------+-------------+------------+----------+--------------+----------------+-------------+
| FISCAL_DAY | Actual Date | BEGIN_DATE | END_DATE | FISCAL_MONTH | FISCAL_QUARTER | FISCAL_YEAR |
+------------+-------------+------------+----------+--------------+----------------+-------------+
| 1 | 12/30/2017 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 2 | 12/31/2017 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 3 | 1/1/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 4 | 1/2/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 5 | 1/3/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 6 | 1/4/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 7 | 1/5/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 8 | 1/6/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 9 | 1/7/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 10 | 1/8/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 11 | 1/9/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 12 | 1/10/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 13 | 1/11/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 14 | 1/12/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 15 | 1/13/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 16 | 1/14/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 17 | 1/15/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 18 | 1/16/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 19 | 1/17/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 20 | 1/18/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 21 | 1/19/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 22 | 1/20/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 23 | 1/21/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 24 | 1/22/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 25 | 1/23/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 26 | 1/24/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 27 | 1/25/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 28 | 1/26/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 29 | 1/27/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 30 | 1/28/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 31 | 1/29/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 32 | 1/30/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 33 | 1/31/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
| 34 | 2/1/2018 | 12/30/2017 | 2/2/2018 | 12 | 4 | 2,018 |
+------------+-------------+------------+----------+--------------+----------------+-------------+
How do I put date in recursion such that actual dates show up ?
My Attempt (incorrect results)
WITH RECURSIVE TMP_FISCAL_DAY
(FISCAL_DAY, ACTUAL_DATE, BEGIN_DATE ,END_DATE ,FISCAL_MONTH,FISCAL_QUARTER,FISCAL_YEAR ) AS
(SELECT CAST(1 AS INT) ,cast(current_date as date), begin_date,end_DATE,FISCAL_MONTH,FISCAL_QUARTER,FISCAL_YEAR FROM DB_NAME.ORIGINAL_FISCAL_TABLE
UNION ALL
SEL Fiscal_Day+1,ACTUAL_DATE+FISCAL_DAY,begin_date,end_DATE,FISCAL_MONTH,FISCAL_QUARTER,FISCAL_YEAR
FROM TMP_FISCAL_DAY WHERE BEGIN_DATE<END_DATE AND FISCAL_DAY<END_DATE-BEGIN_DATE)
SEL * FROM TMP_FISCAL_DAY where CURRENT_DATE BETWEEN BEGIN_DATE AND END_DATE
Upvotes: 0
Views: 46
Answers (1)
dnoeth
Reputation: 60462
Assuming there's one row per fiscal month in your ORIGINAL_FISCAL_TABLE you should filter the current month before recursion and then use BEGIN_DATE instead of CURRENT_DATE:
WITH RECURSIVE TMP_FISCAL_DAY ( FISCAL_DAY, ACTUAL_DATE, BEGIN_DATE ,END_DATE ,FISCAL_MONTH,FISCAL_QUARTER,FISCAL_YEAR )
AS
(
SELECT
Cast(1 AS INT)
,BEGIN_DATE
,begin_date
,end_DATE
,FISCAL_MONTH
,FISCAL_QUARTER
,FISCAL_YEAR
FROM DB_NAME.ORIGINAL_FISCAL_TABLE
WHERE Current_Date BETWEEN BEGIN_DATE AND END_DATE
UNION ALL
SELECT
Fiscal_Day+1
,ACTUAL_DATE+1
,begin_date
,end_DATE
,FISCAL_MONTH
,FISCAL_QUARTER
,FISCAL_YEAR
FROM TMP_FISCAL_DAY
WHERE ACTUAL_DATE+1 < END_DATE
)
SELECT *
FROM TMP_FISCAL_DAY
As @RonBallard wrote there's no need for recursion, you can use EXPAND ON instead:
SELECT
ACTUAL_DATE - BEGIN_DATE + 1 AS Fiscal_Day, dt.*
FROM
(
SELECT Begin(pd) AS ACTUAL_DATE, t.*
FROM ORIGINAL_FISCAL_TABLE AS t
WHERE Current_Date BETWEEN BEGIN_DATE AND END_DATE
EXPAND ON PERIOD(BEGIN_DATE, END_DATE) AS pd
) AS dt
But finally there should be no need for any kind of calculation, every company should have a calendar table with pre-calculated data:
SELECT ...
FROM myCalendar
WHERE Current_Date BETWEEN FISCAL_MONTH_BEGIN_DATE AND FISCAL_MONTH_END_DATE
Upvotes: 2
Related Questions
- Dynamic Dates SQL Server
- How to pull the data based on two dates in sql
- T-SQL recursion, date shifting based on previous iteration
- SQL Server : Recursive function to get last working day
- How to use recursive with dates in Teradata?
- Having issues joing a table with a recursive function in Sqlite
- sql server date recursive
- MSSQL Recursive dateadd
- Need to write recursive date function in sql?
- SQL Recursive Calculated Date