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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

777 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