?
Solved

SQL query to yield additional records

Posted on 2016-10-06
9
Medium Priority
?
82 Views
Last Modified: 2016-10-07
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
Comment
Question by:fjkilken
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 54

Expert Comment

by:Ryan Chong
ID: 41833108
201505 + 63 months 202003

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

Author Comment

by:fjkilken
ID: 41833113
Hi Ryan, thanks for the correction
0
 
LVL 54

Expert Comment

by:Ryan Chong
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

Open in new window

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 37

Expert Comment

by:Pawan Kumar
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)

--

Open in new window


Enjoy !
0
 

Author Comment

by:fjkilken
ID: 41833190
hi Pawan
Thanks, I tried that but it only returns the 130 records from the table - the last value is 201505.
Can you advise?
Thanks
Fergal
0
 
LVL 37

Expert Comment

by:Pawan Kumar
ID: 41833247
can you post your data in the excel file?
0
 
LVL 13

Accepted Solution

by:
Arifhusen Ansari earned 2000 total points
ID: 41833494
Try this hope it will help you.


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

Open in new window



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
WHERE DATEADD (M,1,StartYearMonth) <= EndYearMonth

)

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

Open in new window

1
 
LVL 70

Expert Comment

by:Scott Pletcher
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

by:fjkilken
ID: 41834073
thanks for that Arif!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question