Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2007-11-30
4
Medium Priority
?
670 Views
Last Modified: 2010-04-21
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
Comment
Question by:willhebert
  • 3
4 Comments
 
LVL 6

Accepted Solution

by:
PaultheBroker earned 1000 total points
ID: 20383779
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
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20383810
by the way,         convert(varchar(10),getdate(),110)          will return '11-30-2007' - this might be a bit simpler than your current logic !!!!!!!   :)
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20383840
select max(name) from dbo.sysobjects
where xtype = 'U' -- user table
and name like ('ExcessInventory12MthReport-' + right('0'+cast(@lastmonth as varchar(2)),2) +'%'
0
 

Author Closing Comment

by:willhebert
ID: 31411955
Thank you PaultheBroker for your help.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

876 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