Stored procedure to update invoice numbers w/duplicate records

Transact-SQL
1

How would I write a query that will look for duplicate records with different bill amounts and then take the record with the greater amount(th_net_amount) and add a 'C' to the end of the invoice number(@th_PO) and the record with the lesser amount(th_net_amount) add an 'R' to the end of that invoice number(@th_PO)?

Below is what I have created so far.....

IF EXISTS

(SELECT*FROM FTP_INV,

WHERE @th_record_type = 'H'

COUNT(@th_PO) AS PurchaseOrderDUP

HAVING ( COUNT(@th_PO) > 1 )

WHERE < @th_net_amount

UPDATE FTP_INV

SET @th_PO = @th_PO + 'C'

AND

WHERE > @th_net_amount

UPDATE FTP_INV

SET @th_PO = @th_PO + 'R')

2

welcome to forum. Help us help you by providing us sample data or giving us direct access to your database

create table #captainkirk(po int, net_amount money)

insert into #captainkirk
select 87877, 45.33
3

Below is the entire procedure and the changes is for header record 'H'

USE [Test]
GO
/****** Object: StoredProcedure [dbo].[Get_INVOICES] Script Date: 03/31/2020 10:51:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Get_INVOICES]

AS
BEGIN
Set NOCOUNT ON

declare
@th_record_type char(1),
@th_vendor_num varchar(15),
@th_invoice_num varchar(30),
@th_invoice_date varchar(10),
@th_period char(6),
@th_terminal varchar(15),
@th_PO varchar(20),
@th_terms varchar(15),
@th_due_date varchar(10),
@th_gross_amount money,
@th_disc_amount money,
@th_net_amount money,
@th_invoice_type char(1),
@th_credit_rebill char(1),
@th_total_trip_miles float,
@th_ivh_hdrnumber int,
@th_invoicenumber varchar(12),
@th_ivh_invoicenumber varchar(12),
@td_record_type char(1),
@td_billoflading varchar(15),
@td_site varchar(15),
@td_product varchar(15),
@td_terminal varchar(15),
@td_carrier varchar(15),
@td_productbillingbasis char(1),
@td_grossunits float,
@td_netunits float,
@td_unitcost money,
@td_extendedcost money,
@StartDate as datetime,
@EndDate as datetime,
@ErrorCode varchar(10),
@ErrorDesc varchar(250),
@FS int,
@OLEResult int,
@FileID int,
@FileName varchar(255),
@FileName2 varchar(255),
@Text1 varchar(255),
@th_record1 char(1),
@th_record2 varchar(30),
@th_record3 varchar(30),
@th_record4 varchar(30),
@th_record5 varchar(30),
@th_record6 varchar(30),
@th_record7 varchar(30),
@th_record8 varchar(30),
@th_record9 varchar(30),
@th_record10 varchar(30),
@th_record11 varchar(30),
@th_record12 varchar(30),
@th_record13 varchar(30),
@th_record14 varchar(30),
@th_record15 varchar(30),
@th_record16 varchar(30),
@th_record17 varchar(30)

truncate table dbo.FTP_INV
--set @StartDate = GETDATE() - 14
--set @EndDate = GETDATE() - 7

Declare get_header_records cursor for

select 'H',ivh_revtype1,ivh_ref_number,	CONVERT(VARCHAR(10),ivh_billdate, 110), 
	'','',ord_hdrnumber,'','','','',ivh_totalcharge,'','',ivh_loaded_distance,ivh_hdrnumber,ivh_invoicenumber
from [invoiceheader] 
where ivh_billto = 'SAMPLECOMPANY'
and ivh_invoicenumber not in (select ivh_invoicenumber from InvoicesSent) 
--and ivh_deliverydate between @StartDate and @EndDate


open get_header_records 
fetch get_header_records 
into @th_record_type, @th_vendor_num, @th_invoice_num, @th_invoice_date,
	 @th_period, @th_terminal, @th_PO, @th_terms, @th_due_date, @th_gross_amount, @th_disc_amount, 
	 @th_net_amount, @th_invoice_type, @th_credit_rebill, @th_total_trip_miles, @th_ivh_hdrnumber, @th_ivh_invoicenumber
if @@FETCH_STATUS <> 0 	 	
	BEGIN 
	close get_header_records
	deallocate get_header_records
	goto EOJ
	END	
	
While @@fetch_status = 0 
BEGIN

	insert into FTP_INV
	select 'H', ivh_revtype1, ivh_ref_number, CONVERT(VARCHAR(10), ivh_billdate, 110), '', '', ord_hdrnumber, 
		'','', '','',cast(ivh_totalcharge as decimal(9,2)), '' ,'' ,ivh_loaded_distance,ivh_invoicenumber,''
	from [invoiceheader] 
	where ivh_billto = 'SAMPLECOMPANY' and ivh_hdrnumber = @th_ivh_hdrnumber

	insert into FTP_INV
		select 'D',  (select TOP 1[ref_number] FROM [referencenumber] R
			where R.ref_type = 'BL#'  and R.ref_table = 'freightdetail' and R.ord_hdrnumber =  D.ord_hdrnumber), 
			(substring(C.cmp_name, 10, 3) + SUBSTRING(C.cmp_name,14, 4)),
			D.cmd_code, H.ivh_originpoint, '', '',
			D.ivd_quantity, D.ivd_quantity , cast(D.ivd_rate as decimal(9,4)),
				cast(D.ivd_charge as decimal(9,2)), '','','','','',''
		FROM [invoicedetail] D
		inner join [invoiceheader] H on H.ivh_hdrnumber = D.ivh_hdrnumber 
		inner join [company] C on C.cmp_id = H.ivh_consignee
		where H.ivh_billto = 'SAMPLECOMPANY' and D.ord_hdrnumber = @th_PO and D.ivh_hdrnumber = @th_ivh_hdrnumber
		and D.fgt_number is not null
									
	
		insert into FTP_INV
		select 'I',  D.cht_itemcode, cast(D.ivd_charge as decimal(9,2)),'','','','','','','','','','','','','',''
		FROM [invoicedetail] D
		inner join [invoiceheader] H on H.ivh_hdrnumber = D.ivh_hdrnumber 
		inner join [company] C on C.cmp_id = H.ivh_consignee
		where H.ivh_billto = 'SAMPLECOMPANY' and D.ord_hdrnumber = @th_PO and D.ivh_hdrnumber = @th_ivh_hdrnumber
		and D.fgt_number is null
			
					
	fetch get_header_records into @th_record_type, @th_vendor_num, @th_invoice_num, @th_invoice_date,
	 @th_period, @th_terminal, @th_PO, @th_terms, @th_due_date, @th_gross_amount, @th_disc_amount, 
	 @th_net_amount, @th_invoice_type, @th_credit_rebill, @th_total_trip_miles, @th_ivh_hdrnumber, @th_ivh_invoicenumber

end
close get_header_records
deallocate get_header_records
-- Step 2 write the Invoice records from SQL to File CSV format.
set @ErrorCode = 0
set @ErrorDesc = ''

set @filename = 'C:\TEST\OUT\SAMPLECOMPANY'
set @filename = @filename + cast(DATEPART(YY,GETDATE())as varchar(30))
set @filename = @filename + Right('0' + cast(DATEPART(MM,GETDATE())as varchar(30)),2)
set @filename = @filename + Right('0' + cast(DATEPART(DD,GETDATE())as varchar(30)),2)
set @filename = @filename + Right('0' + cast(DATEPART(HH,GETDATE())as varchar(30)),2)
set @filename = @filename + Right('0' + cast(DATEPART(MI,GETDATE())as varchar(30)),2)
set @filename = @filename + Right('0' + cast(DATEPART(SS,GETDATE())as varchar(30)),2)
set @FileName = @FileName + '.csv'

execute @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
if @OLEResult <> 0 BEGIN
set @ErrorDesc = 'Execute Create Scripting.FileSystemObject Failed'
goto ERRORS
END

--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID Out, @FileName, 8, 1
if @OLEResult <> 0 BEGIN
set @ErrorDesc = 'Execute OpenTextFile Failed'
goto ERRORS
END

Declare Get_Invoices cursor for

select T.th_record1, T.th_record2, T.th_record3,
T.th_record4, T.th_record5, T.th_record6,
T.th_record7, T.th_record8, T.th_record9,
T.th_record10, T.th_record11, T.th_record12,
T.th_record13, T.th_record14, T.th_record15
from FTP_INVT

open Get_Invoices
Fetch Get_Invoices into @th_record1, @th_record2, @th_record3,
@th_record4, @th_record5, @th_record6,
@th_record7, @th_record8, @th_record9,
@th_record10, @th_record11, @th_record12,
@th_record13, @th_record14, @th_record15

While @@FETCH_STATUS = 0
BEGIN
set @Text1 = @th_record1 +',' + @th_record2 + ','+ @th_record3 + ',' + @th_record4 + ',' + @th_record5 + ',' + @th_record6
+ ',' + @th_record7 + ',' + @th_record8 + ',' + @th_record9 + ',' + @th_record10
+ ',' + @th_record11 + ',' + @th_record12 + ',' + @th_record13 + ',' + @th_record14 + ',' + @th_record15

execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
if @OLEResult <> 0 BEGIN
set @ErrorDesc = 'Execute WriteLine from Text1 Failed'
goto ERRORS
END

Fetch Get_Invoices into @th_record1, @th_record2, @th_record3,
@th_record4, @th_record5, @th_record6,
@th_record7, @th_record8, @th_record9,
@th_record10, @th_record11, @th_record12,
@th_record13, @th_record14, @th_record15
END
close Get_Invoices
deallocate Get_Invoices

insert into InvoicesSentEDI
(ivh_invoicenumber,ivh_ref_number,ivh_FTP_Date)
select H.ivh_invoicenumber,H.ivh_ref_number,GETDATE()
from invoiceheader as H
join FTP_INVas T on T.th_record16 = H.ivh_invoicenumber
and T.th_record1 = 'H'
where ivh_billto = 'SAMPLECOMPANY'
and ivh_invoicenumber not in (select ivh_invoicenumber from InvoicesSent)

goto EOJ
ERRORS:
Print @ErrorDesc
--set @ReturnCode = 1

EOJ:
END

4

Trekkie,

we need DDL, sample data and results. We don't have your schema, so pasting that long proc is not going to help. By the way, you could probably get rid of that cursor and perform a set based operation that would make things alot faster.

1 Like
5

:flushed::disappointed_relieved::tired_face: yikes. Thanks but that does not provide sample data?