Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# SQL query to yield additional records

Posted on 2016-10-06
Medium Priority
78 Views
Hi
I have a SQL table with a YearMonth column.
In this column are values eg; from 201001 to 201505.

I would like some code that will add 63 months to the highest value in the table (ie; 201505 + 63 months 202003)
and then return values from 201001 up to the new value 202003 and ALL yearmonth values in between this range

Thanks!!
0
Question by:fjkilken
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2
• +2

LVL 53

Expert Comment

ID: 41833108
201505 + 63 months 202003

isn't it 201505 + 63 months = 202008 instead?
1

Author Comment

ID: 41833113
Hi Ryan, thanks for the correction
0

LVL 53

Expert Comment

ID: 41833117
saw some examples online and I gonna propose this:

``````IF OBJECT_ID('tempdb..#tblYearMonth') IS NOT NULL
DROP TABLE #tblYearMonth

create table #tblYearMonth
(
YearMonth varchar(10)
);
insert into #tblYearMonth values ('201001'),('201002'),('201302'),('201505');

DECLARE @startYearMonth VARCHAR(10)
DECLARE @endYearMonth VARCHAR(10)

Select @startYearMonth = Min(YearMonth)+'01' from #tblYearMonth
Select @endYearMonth = Max(YearMonth)+'01' from #tblYearMonth

declare @dt datetime, @dtEnd datetime
set @dt = Convert(datetime, @startYearMonth, 112)

set @dtEnd = dateadd(month, 63, Convert(datetime, @endYearMonth, 112))

select convert(varchar(6), dateadd(month, number, @dt), 112)
from
(select distinct number from master.dbo.spt_values
where name is null
) n
where dateadd(month, number, @dt) < @dtEnd
``````
0

LVL 32

Expert Comment

ID: 41833165
Pls try this..

``````--

CREATE TABLE [dbo].[Years](
[YearMonth] [bigint] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201001)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201002)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201003)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201004)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201005)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201006)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201007)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201008)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201009)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201010)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201011)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201012)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201101)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201102)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201103)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201104)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201105)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201106)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201107)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201108)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201109)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201110)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201111)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201112)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201201)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201202)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201203)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201204)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201205)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201206)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201207)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201208)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201209)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201210)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201211)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201212)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201301)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201302)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201303)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201304)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201305)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201306)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201307)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201308)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201309)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201310)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201311)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201312)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201401)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201402)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201403)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201404)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201405)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201406)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201407)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201408)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201409)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201410)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201411)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201412)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201501)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201502)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201503)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201504)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201505)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201001)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201002)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201003)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201004)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201005)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201006)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201007)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201008)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201009)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201010)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201011)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201012)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201101)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201102)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201103)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201104)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201105)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201106)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201107)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201108)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201109)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201110)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201111)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201112)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201201)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201202)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201203)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201204)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201205)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201206)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201207)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201208)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201209)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201210)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201211)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201212)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201301)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201302)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201303)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201304)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201305)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201306)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201307)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201308)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201309)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201310)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201311)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201312)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201401)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201402)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201403)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201404)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201405)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201406)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201407)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201408)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201409)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201410)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201411)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201412)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201501)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201502)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201503)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201504)
GO
INSERT [dbo].[Years] ([YearMonth]) VALUES (201505)
GO

DECLARE @Ym AS BIGINT = '201001'
DECLARE @NoofMonthstoADD AS INT = 63

;WITH CTE AS
(
SELECT YearMonth , DATEFROMPARTS( LEFT(YearMonth,4) ,  RIGHT(YearMonth,2) , '01' ) dt , MAX(DATEFROMPARTS( LEFT(YearMonth,4) ,  RIGHT(YearMonth,2) , '01' )) OVER() Mx FROM Years
)
SELECT YearMonth FROM CTE
WHERE dt BETWEEN DATEFROMPARTS( LEFT(@Ym,4) ,  RIGHT(@Ym,2) , '01' ) AND DATEADD(m,@NoofMonthstoADD,mx)

--
``````

Enjoy !
0

Author Comment

ID: 41833190
hi Pawan
Thanks, I tried that but it only returns the 130 records from the table - the last value is 201505.
Thanks
Fergal
0

LVL 32

Expert Comment

ID: 41833247
can you post your data in the excel file?
0

LVL 13

Accepted Solution

Arifhusen Ansari earned 2000 total points
ID: 41833494

Assume that #test is your table and also YearMonth in Your design is INT.

If not then you just need to cast it to int first. As sorting for Varchar is different than INT.

Just modify

``````SELECT CAST(CONCAT(MIN(CAST(YearMonth AS INT)),'01') AS DATE) StartYearMonth ,DATEADD(M,63, CAST(CONCAT(MAX(CAST(YearMonth AS INT)),'01') AS DATE))  EndYearMonth  FROM #Test
``````

``````CREATE TABLE #Test
( YearMonth INT
)

INSERT INTO #Test
VALUES
( 201001)
,(201505)

;WITH CTE_GetYearMonth
AS
(

SELECT CAST(CONCAT(MIN(YearMonth),'01') AS DATE) StartYearMonth ,DATEADD(M,63, CAST(CONCAT(MAX(YearMonth),'01') AS DATE))  EndYearMonth  FROM #Test

UNION ALL

SELECT DATEADD (M,1,StartYearMonth) AS StartYearMonth, EndYearMonth FROM CTE_GetYearMonth

)

SELECT CONVERT(VARCHAR(6),StartYearMonth,112) AS YearMonthRange FROM CTE_GetYearMonth
OPTION ( maxrecursion 0)
``````
1

LVL 70

Expert Comment

ID: 41834010
You'll need a "standard" tally table of sequential numbers.  I've used a cte to generate the table inline, or you could also use your own physical tally table.  (But do not use a table in the master db!)

;WITH
cteTally10 AS (
SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Numbers(Number)
),
cteTally1000 AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.number) - 1 AS number
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
CROSS JOIN cteTally10 c3
)
SELECT CAST(CONVERT(varchar(6), DATEADD(MONTH, t.number, DateMin), 112) AS int) AS YearMonth
FROM (
SELECT MIN(YearMonth) AS YearMonthMin, MAX(YearMonth) AS YearMonthMax
FROM /*your_table_name*/  ( VALUES(201001),(201505) ) AS test_data(YearMonth)
) AS YearMonth_Min_Max
CROSS APPLY (
SELECT CAST(CAST(YearMonthMin * 100 + 01 AS varchar(8)) AS date) AS DateMin,
CAST(CAST(YearMonthMax * 100 + 01 AS varchar(8)) AS date) AS DateMax
) AS assign_alias_names
INNER JOIN cteTally1000 t ON t.number BETWEEN 0 AND DATEDIFF(MONTH, DateMin, DateMax) + 63
ORDER BY 1
0

Author Closing Comment

ID: 41834073
thanks for that Arif!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this â€¦
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
###### Suggested Courses
Course of the Month12 days, 4 hours left to enroll