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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data 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 discu…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

895 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now