Dynamic Temp Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic Temp Table

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-04-10 : 08:58:29

Hello -

I am trying to create a temp table for dates to be used with claims data to ensure that if the claims data shows 0 in claims that I still get that month.

My problem is that I dont know how to create the temp table dynamically or if I can even do that. I am trying to create the table that will be based on the users input. Currently I have variables declared so that you choose a date range - @IncurredStart and @IncurredEnd. So you can see all the claims that fall between a specified incurred period.

Next I want to create a temp table to house incurred dates (2014-01, 2014-02........). So it will be something like @IncurredStart, (@IncurredStart+(month(@IncurredStart)+1),(@IncurredStart+(month(@IncurredStart)+2)......The issue I have is that I am not sure if that will work and how do you know when to stop - the user typically requests 12 months of data but can request 24? Is there a way to stop at the @IncurredEnd?

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-10 : 09:42:50

DECLARE @IncurredStart datetime = '1/1/2014'
DECLARE @IncurredEnd datetime ='12/1/2014'

-- you need to decide how much of a limit you want to give them

IF DATEDIFF(mm,@IncurredStart,@IncurredEnd) +1 > 12 -- limits to the tewlve months, change end date to 2015 and it will not work
BEGIN
Select 'You cannot enter a period longer than 12 months'
return;
END

IF OBJECT_ID('tempdb.dbo.#MyTable') IS NOT NULL BEGIN DROP TABLE #MyTable END

;WITH MyCTE
AS
(

SELECT DATEADD(mm,0,@IncurredStart) DT
UNION ALL
SELECT DATEADD(mm,1,DT) DT
FROM MyCTE
WHERE DATEADD(mm,1,DT) <=@IncurredEnd
)

SELECT * INTO #MyTable FROM MyCTE
OPTION (MAXRECURSION 24) -- set to handle only 24 months 0 - would be endless, so don't do that - default ia 100

SELECT * FROM #MyTable

The temp table part is fairly straight forward. you could define the table prior and just do an insert into as well

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-04-10 : 09:53:07

Great, thank you.....Im still a little confused though.
Will this work?
declare @DOS date(max)
set @DOS = create table ##DOS ([Dates] date(10))

insert into @DOS (Dates) values
if datediff(mm,@IncurredDateStart,@IncurredDateEnd) +1 > 24

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-04-10 : 09:54:01

I will call the temp table later in my code....

quote:
Originally posted by jcb267

Great, thank you.....Im still a little confused though.
Will this work?
declare @DOS date(max)
set @DOS = create table ##DOS ([Dates] date(10))

insert into @DOS (Dates) values
if datediff(mm,@IncurredDateStart,@IncurredDateEnd) +1 > 24

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-10 : 11:02:49

quote:
Originally posted by jcb267

Great, thank you.....Im still a little confused though.
Will this work?
declare @DOS date(max)
set @DOS = create table ##DOS ([Dates] date(10))

insert into @DOS (Dates) values
if datediff(mm,@IncurredDateStart,@IncurredDateEnd) +1 > 24

No that won't work. You need Dynamic SQL

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-10 : 11:28:40

If you want to use a table variable

DECLARE @IncurredStart datetime = '1/1/2014'
DECLARE @IncurredEnd datetime ='12/1/2014'
DECLARE @DOS TABLE(dt datetime)

-- you need to decide how much of a limit you want to give them

IF DATEDIFF(mm,@IncurredStart,@IncurredEnd) +1 > 12 -- limits to the tewlve months, change end date to 2015 and it will not work
BEGIN
Select 'You cannot enter a period longer than 12 months'
return;
END

;WITH MyCTE
AS
(

SELECT DATEADD(mm,0,@IncurredStart) DT
UNION ALL
SELECT DATEADD(mm,1,DT) DT
FROM MyCTE
WHERE DATEADD(mm,1,DT) <=@IncurredEnd
)

INSERT INTO @DOS
SELECT * FROM MyCTE
OPTION (MAXRECURSION 24) -- set to handle only 24 months 0 - would be endless, so don't do that - default ia 100

SELECT * FROM @DOS

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-04-10 : 11:37:45

so it will only work for 12 months in the same year? It is very highly likely that the period will be from 2 years like 2014-03 through 2015-02......maybe I will just make a static temp table and refer to it in my code?

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-10 : 11:43:11

you can change it to allow as much as you want. change the following to allow 24 months, but you will need to decide on something as a cap if you want a cap. And you probably do.

IF DATEDIFF(mm,@IncurredStart,@IncurredEnd) +1 > 24-- limits to the tewlve months, change end date to 2015 and it will not work

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-04-10 : 12:39:08

Hi Michael -

Thanks for helping me out with this!

if
@IncurredStart = 1/1/2013,
@IncurredEnd = 12/31/2014

and

IF DATEDIFF(mm,@IncurredStart,@IncurredEnd) +1 > 24

Will you get 12 2013 records and 12 2014 records?

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-10 : 12:50:52

yes - you should if I wrote it correctly. the CTE uses recursion to do your inserts of your dates. you can play with the ranges and see what works best for you

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-04-10 : 13:23:13

Sorry for being a pain, Michael. I can't get this to work though. Here is what I have:

create table @DOS (IncurMonth date(10))
IF DATEDIFF(mm,@IncurredDateStart,@IncurredDateStart) +1 > 24 -- limits to the tewlve months, change end date to 2015 and it will not work
BEGIN
Select 'You cannot enter a period longer than 12 months' - I HAVE IT SELECTING THE DATES HERE, I PASTED THIS ON ACCIDENT
return;
END

;WITH MyCTE
AS
(

SELECT DATEADD(mm,0,@IncurredDateStart) DT
UNION ALL
SELECT DATEADD(mm,1,DT) DT
FROM MyCTE
WHERE DATEADD(mm,1,DT) <=@IncurredDateEnd
)

INSERT INTO @DOS
SELECT * FROM MyCTE
OPTION (MAXRECURSION 24) -- set to handle only 24 months 0 - would be endless, so don't do that - default is 100

SELECT * FROM @DOS

one other question, can this temp table be referred to later in the code of a stored procedure?

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-10 : 13:32:48

You are not declare the table variable correctly . are you using TSQL?

I have corrected.

DECLARE @IncurredStart datetime = '1/1/2014'
DECLARE @IncurredEnd datetime ='12/1/2015'
DECLARE @DOS TABLE(dt datetime)

IF DATEDIFF(mm,@IncurredStart,@IncurredEnd) +1 > 24 -- limits to the tewlve months, change end date to 2015 and it will not work
BEGIN
Select 'You cannot enter a period longer than 12 months' -- I HAVE IT SELECTING THE DATES HERE, I PASTED THIS ON ACCIDENT
return;
END

;WITH MyCTE
AS
(

SELECT DATEADD(mm,0,@IncurredStart) DT
UNION ALL
SELECT DATEADD(mm,1,DT) DT
FROM MyCTE
WHERE DATEADD(mm,1,DT) <=@IncurredEnd
)

INSERT INTO @DOS
SELECT * FROM MyCTE
OPTION (MAXRECURSION 24) -- set to handle only 24 months 0 - would be endless, so don't do that - default is 100

SELECT * FROM @DOS

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-04-10 : 13:36:19

2008R2?

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-10 : 13:52:26

This will work on 2005 on up.

I don't know what this is : create table @DOS (IncurMonth date(10))

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2015-04-10 : 14:17:33

crap, Im not having any luck

In your select statement:
Select 'You cannot enter a period longer than 12 months'

I am pulling dates, is that wrong??

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-10 : 14:40:48

sure you can - I set that last one for 24 and just tested. Results follow

2014-01-01 00:00:00.000
2014-02-01 00:00:00.000
2014-03-01 00:00:00.000
2014-04-01 00:00:00.000
2014-05-01 00:00:00.000
2014-06-01 00:00:00.000
2014-07-01 00:00:00.000
2014-08-01 00:00:00.000
2014-09-01 00:00:00.000
2014-10-01 00:00:00.000
2014-11-01 00:00:00.000
2014-12-01 00:00:00.000
2015-01-01 00:00:00.000
2015-02-01 00:00:00.000
2015-03-01 00:00:00.000
2015-04-01 00:00:00.000
2015-05-01 00:00:00.000
2015-06-01 00:00:00.000
2015-07-01 00:00:00.000
2015-08-01 00:00:00.000
2015-09-01 00:00:00.000
2015-10-01 00:00:00.000
2015-11-01 00:00:00.000
2015-12-01 00:00:00.000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources