if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[H_DeleteDuplicate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[H_DeleteDuplicate]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure H_DeleteDuplicate
@TransDate datetime -- in the format 'yyyy/mm/dd'
as
declare @Qry as varchar(2000)
declare @FileDate as varchar(6)
--use subaccounts
set @FileDate = replace(convert(varchar(10), @TransDate, 3), '/', '')
--Adding a new column TransId which will help in deleting the duplicate row
set @Qry = 'sp_columns @Table_Name = ''SosummaryB' + @FileDate + ''', @Column_Name = ''TransID'''
execute(@Qry)
if @@rowcount = 0
begin
set @Qry = 'Alter table SoSummaryB' + @FileDate
set @Qry = @Qry + ' ADD TransID numeric identity(1,1)'
execute(@Qry)
end
--Now deleting the duplicate row which has the same socode and same
--transdate in the table sosummaryBddmmyy
set @Qry = 'delete from SosummaryB' + @FileDate + ' where '
set @Qry = @Qry + ' TransID in '
set @Qry = @Qry + ' (Select max(TransID) from SosummaryB' + @FileDate
set @Qry = @Qry + ' group by SoCode, TransDate having count(SoCode) > 1)'
execute (@Qry)
--Adding a new column TransId which will help in deleting the duplicate row
set @Qry = 'sp_columns @Table_Name = ''SosummaryA' + @FileDate + ''', @Column_Name = ''TransID'''
execute(@Qry)
if @@rowcount = 0
begin
set @Qry = 'Alter table SoSummaryA' + @FileDate
set @Qry = @Qry + ' ADD TransID numeric identity(1,1)'
execute(@Qry)
end
--Now deleting the duplicate row which has the same socode and same transdate
--in the table sosummaryAddmmyy
set @Qry = 'delete from SosummaryA' + @FileDate + ' where '
set @Qry = @Qry + ' TransID in '
set @Qry = @Qry + ' (Select max(TransID) from SosummaryA' + @FileDate
set @Qry = @Qry + ' group by SoCode, TransDate having count(SoCode) > 1)'
execute (@Qry)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
READ ME AS FOLLOWS :
For Error in Daybegin or Additional Daily in SUBACCOUNTS Application (For Both SubOffice related and BranchOffice related)
-------------------------------------------------------------------
Solution for the problem "SubQuery returned more than one value...." and for Two OBs for same dates to same office under Opening Balance option of Supervisor.
Steps:
1. Download the Script file SUBQUERYPROBLEM.SQL from solution scripts folder
2. Open Query Analyser
3. Select SUBACCOUNTS database if error is coming while entering SO Additional Daily Account
else select BOSUBACCOUNTs if error is found in BO Additional Daily Account.
4. open the downloaded file and run it by pressing F5
5. open a new QUERY WINDOW
6. type the following statement which has the SOSUMMARY Date (NOT DAILY A/C DATE). Pls Note the date has to be in the same format of yyyy/mm/dd.
NOTE : If the error occurs for daybegin then the SOSUMMARY date should be the previous date for which Dayend is already done.
Execute H_DeleteDuplicate '2004/02/25'
7. Run it by pressing F5
8. Close Query Analyser and start working in SubAccounts Application
