Solved

SQL query to yield additional records

Posted on 2016-10-06
9
62 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 51

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 51

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 28

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 28

Expert Comment

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

Accepted Solution

by:
Arifhusen Ansari earned 500 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 69

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 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