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
9
Medium Priority
?
78 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
[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
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 53

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 53

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

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 32

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

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

636 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