Solved

SQL query to yield additional records

Posted on 2016-10-06
9
68 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 52

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 52

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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 29

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 29

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 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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

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.
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

690 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