|
Aishwarya
Starting Member
1 Post |
 Posted - 2010-02-16 : 16:55:56
|
| Hi,Could you please help me to sort this problem?We have aVB application which runs the stored procedure to get all offername, customer number and for particular month and year.The problem is i need to change the offername, but cannot change or update in the original table so my senior asked me to build the look up table to see what all table using this columnOR change the offername(6 half price to one off) by building the look up table.All I need is if name is 6 * $1.95 New I got to change to $1.95 New. But changes should not be in main table optin_lapsed.I have no clue how I can do it could you please help me with this. here is the SPUSE [BatchJobs] GO SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[LapsedSMS_GetList] @ModeID int=0 AS Declare@Month int,@Year int,@RowsAffected int,@WeekNo int, @ScriptName Varchar(64), @ScriptNumber int, @LapsedLoad varchar(32) DECLARE @RunDate DATETIME EXEC dbo.LapsedRunDate_Get @RunDate OUTPUT if @RunDate is null set @RunDate = getdate() Select @LapsedLoad=DateName(month,dateadd(mm,1,@RunDate)) + ' ' + convert(varchar(5),Year(dateadd(mm,1,@RunDate))) Set @ScriptNumber=100 Set @ScriptName='LapsedSMS_GetList' set nocount on Insert into LapsedLoad (LapsedLoad,LoadDate, ErrorStatus) Values (@LapsedLoad,@RunDate,0) If @ModeID=0 BEGIN Set @Month=Month(@RunDate) Set @Year=Year(@RunDate) Set @WeekNo=Datepart(Wk,@RunDate) Exec LapsedLoad_ActivityLog_insert @ScriptNumber,@ScriptName,'Creating crmLapsedSMSOptin_CurrentMonth',@RowsAffected if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[crmLapsedSMSOptin_CurrentMonth]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[crmLapsedSMSOptin_CurrentMonth] Select * into crmLapsedSMSOptin_CurrentMonth From TITAN.ezymarket.dbo.crmMailOptin WHERE Weekno=@WeekNo and YearNo=@Year and OptinFlag=1 and OfferID<>-1 and Status=1 and len(StoreID)=4 and storeID<>9999 and not GroupId is null and Via='SMS' and campaignCode='CRM Lapsed' Set @RowsAffected=@@RowCount Exec LapsedLoad_ActivityLog_insert @ScriptNumber,@ScriptName,'Inserted Crm Lapsed SMS Optins',@RowsAffected Select distinct c.StoreID, o.OfferName, c.WeekNo, c.YearNo,Barcode=o.SmsBarcode ,o.MonthNo,rt.StoreName From crmLapsedSMSOptin_CurrentMonth c inner join optin_Lapsed o on o.OptinOfferName COLLATE SQL_Latin1_General_CP1_CI_AS =c.OfferName COLLATE SQL_Latin1_General_CP1_CI_AS and o.MonthNo=@Month and o.YearNo=@year Inner join Mart.dbo.rt_stores rt on c.Storeid COLLATE SQL_Latin1_General_CP1_CI_AS =rt.Storeid COLLATE SQL_Latin1_General_CP1_CI_AS Inner join dbo.replicatedStores rs on rs.StoreId COLLATE SQL_Latin1_General_CP1_CI_AS =rt.Storeid COLLATE SQL_Latin1_General_CP1_CI_AS Where rs.LastReplicated>=@RunDate-8 ENDELSE If @ModeId=1 BEGIN Select distinct c.StoreID, o.OfferName, c.WeekNo, c.YearNo,Barcode=o.SmsBarcode ,o.MonthNo,rt.StoreName From crmLapsedSMSOptin_CurrentMonth c inner join optin_Lapsed o on o.OptinOfferName COLLATE SQL_Latin1_General_CP1_CI_AS =c.OfferName COLLATE SQL_Latin1_General_CP1_CI_AS and o.MonthNo=@Month and o.YearNo=@year Inner join Mart.dbo.rt_stores rt on c.Storeid COLLATE SQL_Latin1_General_CP1_CI_AS =rt.Storeid COLLATE SQL_Latin1_General_CP1_CI_AS Inner join dbo.replicatedStores rs on rs.StoreId COLLATE SQL_Latin1_General_CP1_CI_AS =rt.Storeid COLLATE SQL_Latin1_General_CP1_CI_AS Where rs.LastReplicated>=@RunDate-8 |
|
Lookup table?