Get the list of year values based on the gap and year value in the table

Related searches

Scenario: I have a table with Year and Gap columns. What I need the output as, starting from the given year value it incremented up to the value in the gap column.

i.e., If the YearVal is 2001, and Gap is 3, I need the output as

Result
--------
2001
2002
2003

What I have tried:

DECLARE @ResultYears TABLE (Gap INT, YearVal INT);
INSERT INTO @ResultYears (Gap, YearVal) VALUES (3, 2001);

;WITH FinalResult AS (
     SELECT YearVal AS [YR] FROM @ResultYears
     UNION ALL
     SELECT [YR] + 1 FROM FinalResult 
     WHERE [YR] + 1 <= (SELECT YearVal + (Gap -1) FROM @ResultYears)
)

SELECT * FROM FinalResult;

db<>fiddle demo with one entry in the table.

Using the query above, I can achieve the expected result. But if the table have more than one entry, the query is not working.

i.e., If I have the entries in the table as below:

DECLARE @ResultYears TABLE (Gap INT, YearVal INT);

INSERT INTO @ResultYears (Gap, YearVal) VALUES
(3, 2001), (4, 2008), (1, 2014), (2, 2018);

How can I modify the query to achieve my expected result?

db<>fiddle demo with more than one entry in the table.

Is this what you're after?

DECLARE @ResultYears TABLE (Gap INT, YearVal INT);

INSERT INTO @ResultYears (Gap, YearVal) VALUES
(3, 2001), (4, 2008), (1, 2014), (2, 2018);

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS I
    FROM N N1, N N2), --100 is more than enough
Years AS(
    SELECT RY.YearVal + T.I AS [Year],
           RY.Gap,
           RY.YearVal
    FROM @ResultYears RY
         JOIN Tally T ON RY.Gap > T.I)
SELECT *
FROM Years Y
ORDER BY Y.YearVal;

Personally I prefer a tally table over a rCTE; they are far quicker, especially with large datasets, or where the rCTE would have to do a high volume of recursion.

Demo on db<>fiddle

How to Find a Gap in a Sequence using SQL SERVER, Solve this puzzle to learn how to find a gap in a sequence using SQL Server. I thought about using joins and comparing the joined values to see if there were next value and then comparing it to the current, I used a common table expression. Kris Wenzel has been working with databases over the past 28 years as a� DECLARE @ResultYears TABLE (Gap INT, YearVal INT ); INSERT INTO @ResultYears (Gap, YearVal) VALUES (3, 2001), (4, 2008), (1, 2014), (2, 2018); Sql Query to get the expected result using CROSS APPLY SELECT R.Gap,dt.Years FROM @ResultYears R CROSS APPLY [dbo].[ufn_GetYears](R.Gap,R.YearVal) AS dt

Initially Create one user defined table type function which return the Gap years

CREATE FUNCTION [dbo].[ufn_GetYears]
 (
 @i_Gap INT,@Year INT
 )
RETURNS @Temp TABLE
(
    Years INT
)
AS
BEGIN
    ;WITH CTE
    AS
    (
    SELECT 1 AS Seq,DATEFROMPARTS ( @Year,01,01) AS Years
    UNION ALL
    SELECT seq +1,DATEADD(YEAR,1,Years)
    FROM Cte 
    WHERE Seq < @i_Gap
    )
    INSERT INTO @Temp
    SELECT DATEPART(YEAR,Years )
    FROM CTE
RETURN
END

Sample Data

DECLARE @ResultYears TABLE 
    (Gap INT,
     YearVal INT
     );

INSERT INTO @ResultYears (Gap, YearVal) VALUES
(3, 2001), (4, 2008), (1, 2014), (2, 2018);

Sql Query to get the expected result using CROSS APPLY

SELECT R.Gap,dt.Years
FROM @ResultYears R
CROSS APPLY [dbo].[ufn_GetYears](R.Gap,R.YearVal) AS dt

Result

Gap Years
---------
3   2001
3   2002
3   2003
4   2008
4   2009
4   2010
4   2011
1   2014
2   2018
2   2019

Gap analysis to find missing values in a sequence � Advanced SQL , SQL Server: Date truncation for custom time periods like year, quarter, month, etc. create table gap (counter integer); insert into gap (counter) values (1); insert list of checks from your bank's transaction log, and you want to find missing By filtering out the rows that have a null for the joined value, we get what we want: The gap year has become a hot topic in college transitions. The New York Times , Time Magazine , Forbes , MSNBC , and countless other media outlets have covered this growing trend.

If for a reason, you prefer recursive CTE (which is definetly slower)

DECLARE @ResultYears TABLE (Gap INT, YearVal INT);

INSERT INTO @ResultYears (Gap, YearVal) VALUES (3, 2001), (4, 2008), (1, 2014), (2, 2018);

;WITH FinalResult AS (
     SELECT YearVal, Gap, YearVal [YR] FROM @ResultYears
     UNION ALL
     SELECT YearVal, Gap, [YR] + 1 
     FROM FinalResult 
     WHERE [YR] + 1 <= YearVal + (Gap -1)
)
SELECT * FROM FinalResult
ORDER BY [YR];

You need to keep original row parameters in the recursive part. this way recursion runs as desired.

Excel formula: Find missing values, To find out what values in one list are missing from another list, you can use a simple formula based on the COUNTIF function. In this case, if a value is found, a positive number is returned by COUNTIF, which evaluates to TRUE, Also note that we only give VLOOKUP a single column (column B) for the table array. Add Color field Values area, rename to "Count" Add Date field to Columns area, group by Year; Change value field settings to show count if needed; Notes. Any non-blank field in the data can be used in the Values area to get a count. When a text field is added as a Value field, Excel will display a count automatically.

EXTRACT components from a date or time in MySQL , SQL's EXTRACT provides access to the parts of timestamps and dates (year, month, day, hour, minute, second) SQL extract — Get a Field from a Date or Time Value value. For second , it also includes fractions.0 The following table lists the extract fields defined by the SQL as decimal mark based on the current locale. The following formula may help you to get the total value based on month and year from another column, please do as follows: Please enter this formula into a blank cell where you want to get the result: =SUMPRODUCT((MONTH(A2:A15)=1)*(YEAR(A2:A15)=2016)*(B2:B15)), (A2:A15 is the cells contain the dates, B2:B15 contains the values that you want to sum, and the number 1 indicates the month

Calculate year-to-date values-Power BI Tips, Learn how to use DAX in Power BI Desktop to calculate year-to-date values needed for The 'CALENDARAUTO' function returns a table with a single column called set of dates, calculated automatically based on data in the model. on the little circled arrows at the top-left corner of each chart to get this:. Such virtual list (i.e. the List subquery) contains all the values between 1 and the highest Customer_ID. Then, an Not Exists clause is used to look for the values that are missing in the Customers table. In such example, values 1, 3, 8 and 11 are missing in the Customer_ID column.

@Jonathan, This code is independent of a table. This code prints "a list of years as a recordset starting with 2004 to current year (in desc order), without writing a stored procedure". While I believe that printing the values was the wrong choice, my solution does the same an returns the result set – scottm Mar 9 '09 at 19:38

Comments
  • Is ResultYear always going to be unique in the @ResultYears table, or could a given year have more than one Gap entry?
  • @saritonin There will be no possible of the year overlap, so in the result the year will be unique
  • Perfect, it return my expected result. Let me wait for some time for any other answer.
  • You're not going to find a faster one I wouldn't expect @Arulkumar