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
663 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

724 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