hi,
i am getting duplicate data when executing the SP. Please help me with that.
CREATE PROCEDURE [dbo].[BMCOveragesuUpdate]
AS
BEGIN
CREATE TABLE #BMCOveragesuUpdate ( AccountID INT, StartDate DATETIME, EndDate DATETIME, TransactionCount DECIMAL(15,3) )
CREATE TABLE #AccountUsers
(
[new_accountid] [int] NOT NULL,
[name] [nvarchar](400) NULL,
[new_companyname] [nvarchar](200) NULL,
[new_contractid] [nvarchar](100) NULL,
[new_contractstartdate] [date] NULL,
[new_contractenddate] [date] NULL,
[new_vlenrollmentnumber] [nvarchar](100) NULL,
[new_vlenrollmentstartdate] [date] NULL,
[new_vlenrollmentenddate] [date] NULL,
[new_ownertypename] [nvarchar](4000) NULL,
[new_ownertype] [int] NULL,
[new_email] [nvarchar](400) NULL,
[new_unitspurchased] [nvarchar](200) NULL
)
INSERT INTO #AccountUsers (new_accountid, name, new_companyname, new_contractid, new_contractstartdate,
new_contractenddate, new_vlenrollmentnumber, new_vlenrollmentstartdate, new_vlenrollmentenddate,
new_ownertypename, new_ownertype, new_email, new_unitspurchased)
SELECT new_accountid, name, new_companyname, new_contractid, new_contractstartdate,
new_contractenddate, new_vlenrollmentnumber, new_vlenrollmentstartdate, new_vlenrollmentenddate,
new_ownertypename, new_ownertype, new_email, new_unitspurchased
FROM BingMapsPlatform_MSCRM.dbo.FilteredNew_accountuser FAU WITH (NOLOCK)
INNER JOIN BingMapsPlatform_MSCRM.dbo.Filterednew_new_accountuser_account FAUA WITH (NOLOCK)
ON FAU.new_accountuserid = FAUA.new_accountuserid
INNER JOIN BingMapsPlatform_MSCRM.dbo.FilteredAccount FA WITH (NOLOCK)
ON FAUA.accountid = FA.accountid
WHERE FA.new_ownertype IN (2,7,8,10,11)
DECLARE @new_accountid int , @Startdate DATETIME, @EndDate DATETIME
DECLARE @TempEndDate DATETIME
DECLARE account_cursor CURSOR FOR
SELECT new_accountid, new_contractstartdate, new_contractenddate
FROM #AccountUsers
ORDER BY new_accountid
OPEN account_cursor;
FETCH NEXT FROM account_cursor
INTO @new_accountid, @startdate, @enddate
DECLARE @CurrentYear DATETIME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CurrentYear = @startdate
WHILE @CurrentYear < @enddate
BEGIN
SET @TempEndDate = DATEADD(YYYY, DATEDIFF(yyyy,0,@CurrentYear)+ 1, 0)
INSERT INTO #BMCOveragesuUpdate
EXEC BingMapsPlatform_Staging.dbo.GetAccountBillableUsage @new_accountid, @CurrentYear, @TempEndDate
SET @CurrentYear = @TempEndDate
END
IF EXISTS(select 1 from #BMCOveragesuUpdate)
BEGIN
INSERT INTO BMCOverages (new_accountid, name, new_companyname, new_contractid, new_contractstartdate, new_contractenddate, new_vlenrollmentnumber, new_vlenrollmentstartdate, new_vlenrollmentenddate, new_ownertypename, new_ownertype, new_email, new_unitspurchased, TransactionCount, StartDate, EndDate, LastUpdated)
SELECT AU.new_accountid,AU.name , AU.new_companyname, AU.new_contractid, AU.new_contractstartdate,
AU.new_contractenddate, AU.new_vlenrollmentnumber,AU.new_vlenrollmentstartdate, AU.new_vlenrollmentenddate,
AU.new_ownertypename, AU.new_ownertype, new_email, AU.new_unitspurchased, BMC.TransactionCount, BMC.StartDate, BMC.EndDate, GETDATE()
FROM #BMCOveragesuUpdate BMC
INNER JOIN #AccountUsers AU ON BMC.AccountID = AU.new_accountid
WHERE BMC.TransactionCount is not null
END
TRUNCATE TABLE #BMCOveragesuUpdate
FETCH NEXT FROM account_cursor
INTO @new_accountid, @startdate, @enddate
END
CLOSE account_cursor;
DEALLOCATE account_cursor;
drop table #AccountUsers
END
GO