• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 673
  • Last Modified:

How to make dynamic stored procedure that inserts into a table to compare the dates if an item number appear this month but did not appear last month in sql

I want to compare the last month's table to current month's table where if an item number appear this month but it didn't last month then flag as new item. Below is the sql code to where the stored procedure is run once a month and create a copy to history archive. Here are the tables:

History table = dbo.[ExcessInventory12MthReport'+@CopyDate+']
Current table = dbo.ExcessInventory12MthReport'

What I want to accomplish are:
1) Create a dynamic stored procedure to look an item number if exist this month then get it from Current table and but if it did not exist last month then get it from the history table. I don't want to change the date of history table every time I run the stored procedure like example of history tables: dbo.[ExcessInventory12MthReport-11-05-2007], .[ExcessInventory12MthReport-10-08-2007],.[ExcessInventory12MthReport-09-03-2007], etc. etc.

2) How to get the history table using the variable @copydate? Is that possible? I have no clue.

3) Can I get the item number from  history table with a variable in it and compared it to the current table?

Please help! Thanks.

See code below:
DECLARE @SQL as nvarchar(4000)
DECLARE @CopyDate as varchar(11)

--Create copies of tables for History Archives
select @CopyDate = '-'+ case when DatePart(mm,getdate()) <10 then '0' + Cast(DatePart(mm,getdate())as Char(1)) else Cast(DatePart(mm,getdate())as Char(2)) end +'-'+ case when DatePart(dd,getdate()) <10 then '0' + Cast(DatePart(dd,getdate())as Char(1)) else Cast(DatePart(dd,getdate())as Char(2)) end +'-'+ Cast(DatePart(yyyy,getdate()) as Char(4))

--Below is the table that makes a copy of last month's table and it puts a date after the table's name.
Set @SQL = 'select *
into dbo.[ExcessInventory12MthReport'+@CopyDate+']
from dbo.ExcessInventory12MthReport'
Exec (@SQL)

0
willhebert
Asked:
willhebert
  • 3
1 Solution
 
PaultheBrokerCommented:
If this was me, I would be thinking that it might be a common thing to want to access the immediately prior month.  So best way to do that might be to create a UDF (User Defined Function) which takes this month as an argument, and outputs a table.  You can then join on this function as if it was a table, to make your insert statements.

declare @lastmonth tinyint
set @lastmonth = datepart(m,dateadd(m,-1,getdate))

select blah
from dbo.ExcessInventory12MthReport' where blah...)
union select blah
from dbo.historytable(@lastmonth)
where not exists (select * from dbo.ExcessInventory12MthReport' where blah...)


and...
create function dbo.historytable(@lastmonth int)
as
returns @history table as
( field1 int, field2 varchar(20) etc....)

set @copydate = ....logic here as you have it above to get the date

..then put the dynamic bit here....
mysql = 'insert into @history select field1 ...from dbo.[ExcessInventory12MthReport'+@CopyDate+']

If the problem is that you don't actually know what the last months table was called becuase you don't know what day the backup was run, then you might have to look into the sysobjects table to find the right name to use.
0
 
PaultheBrokerCommented:
by the way,         convert(varchar(10),getdate(),110)          will return '11-30-2007' - this might be a bit simpler than your current logic !!!!!!!   :)
0
 
PaultheBrokerCommented:
select max(name) from dbo.sysobjects
where xtype = 'U' -- user table
and name like ('ExcessInventory12MthReport-' + right('0'+cast(@lastmonth as varchar(2)),2) +'%'
0
 
willhebertAuthor Commented:
Thank you PaultheBroker for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now