0

I have a recipe with about 30 values and need to keep a log of anytime the product is made using a recipe where any attribute is different than the last time the recipe was changed. Here is my sample code:

if not exists 
(select 1 from 
     --find most recent version of this recipe:
    (select top 1 * from tbl_recipehistory 
        where catalogno='ipe' order by dts desc) as r 
where r.value1=1 and r.value2=1 and r.value3=2, r.value(N-1)=1, value(n)=1)
begin
    --the recipe has changed so save a copy with current time stamp
    insert tbl_recipehistory (catalogno,dts,value1,value2,value3, value(n-1), value(n))
      values('ipe',getdate(),1,1,2,...,1,1 )
end

Is this the most efficient method or is there a better way? There are about 100 recipes and updates will be made every few weeks.

SQL Server version is SQL Server 2012 Express.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JPoole
  • 305
  • 3
  • 13
  • Sample data and desired results would really make your question much more understandable. – Gordon Linoff Apr 22 '18 at 01:12
  • Each recipe has two key fields (CatalogNo, timestamp) and 30 columns (real1, real2, ... real30). the data is sent to SQL from another system and I need to save it to the SQL history table only if it is different than the last entry in SQL for that CatalogNo. – JPoole Apr 23 '18 at 18:41

0 Answers0