asked on

Syntax for Setting a Variable to Sum of Column

Ok, so I have a table named Customer_Juros has a column named Doctype, InvoiceNo and a column Amount that I want information out of. How it works is there are 8 Doctype's:
1=invoice
2=cashpayment
3=insurance
ect...
In the Amount column are the corresponding amounts.

I want to set a variable:

set @DetailBalance = sum of the Amounts where the doctypes are 1's for Invoice #20545

is that possible or another way to do this?

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nathan Riley

ASKER

Well one other change I don't want it to point only to that invoice number. I was just using as an example. What I meant was invoice 20545 could have 3 doctype 1. That's why I want to sum the 3 amounts and that is what @detailbalance will represent, but I want it to represent the sums of doctype 1 for all of my invoices separately. If to confusing I added my code at the very bottom is where I'm using the variable in the cursor.


 
 
 
 
/******************************************************************************
Purpose: Load Juros
 
Maintenance History:
Date:		Name:			Action:	
-------------------------------------------------------------------------------
07/30/2008	Nathan Riley		Created
******************************************************************************/
CREATE procedure dbo.p_customer_Juros_load
 
as
 
set nocount on
 
--Table variables
declare @PatientID		varchar(255)
declare @LastName		varchar(255)
declare @FirstName		varchar(255)
declare @Address1 		varchar(255)
declare @Address2 		varchar(255)
declare @City 			varchar(255)
declare @State 			varchar(255)
declare @Zip 			varchar(255)
declare @InvoiceNo 		varchar(255)
declare @Phone		varchar(255)
declare @Employer		varchar(255)
declare @EmployerPhone	varchar(255)
declare @InvoiceDate 		datetime
declare @DOS			datetime
declare @DOB			datetime
declare @SSN 			varchar(255)
declare @Amount		money
declare @Type			varchar(20)
declare @LastPayment		money
declare @LastAdjustment	money
declare @Qty			varchar(255)
declare @DetailBalance		money
declare @ItemDesc		varchar(255)
 
--Local varaibles
declare @DebtorID 		int
declare @ServiceID 		int
declare @Today 		datetime
declare @OrgID 		int
declare @AccountID 		int
declare @SeriesID 		int
declare @AddressID 		int
declare @NewServiceID 	int
declare @EventDesc 		varchar(255)
declare @Count 		int
declare @Balance 		money
declare @Desc 			varchar(255)
declare @DebtorInvoiceID	int
declare @NextNum		int
--Initialize variables
set @Today = getdate()
set @OrgID = 2525
set @AccountID = 137
set @SeriesID = 1
set @Count = 0
set @Balance = 0
 
 
 
 
 
 
delete StrategicAR_Customers.dbo.Customer_Juros where InvoiceNo in 		--Change For New Customer
(
 
select I.InvoiceNo from StrategicAR_Customers.dbo.Customer_Juros I 			--Change For New Customer
inner join DebtorService DS on I.InvoiceNo = DS.InvoiceNo
inner join Debtor D on D.DebtorID = DS.DebtorID
and D.AccountNo = I.PatientID 
and D.OrgID = I.OrgID
--Changed to or so that it wouldn't duplicate records from and to or
--or I.HoldFromPrinting = 'True'
)
delete StrategicAR_Customers.dbo.Customer_Juros where LastName = ' '
 
declare C1 cursor static for
select 	PatientID,
	case when ltrim(rtrim(RSPLastName)) is null then LastName
	else ltrim(rtrim(RSPLastName)) end as LastName,
	case when ltrim(rtrim(RSPLastName)) is null then FirstName 
	else ltrim(rtrim(RSPFirstName)) end as LastName,
	ltrim(Substring(Address1,0,40)) as Address1,
	ltrim(Substring(Address2,0,40)) as Address2,
	ltrim(Substring(City,0,30)) as City,
	ltrim(Substring(State,0,3)) as State,
	ltrim(Substring(Zip,0,12)) as Zip,
	ltrim(rtrim(Phone)) as Phone,
	ltrim(rtrim(Employer)) as Employer,
	ltrim(rtrim(EmployerPhone)) as EmployerPhone,
	case isdate(InvoiceDate)when 1 then convert(datetime,InvoiceDate)
  	else null
	end as InvoiceDate,
	case isdate(DOS)when 1 then convert(datetime,DOS)
  	else null
	end as DOS,	
	case isdate(DOB)when 1 then convert(datetime,DOB)
  	else null
	end as DOB,
	SSN,
	(convert(money,Amount,0)) as Amount,
	J.InvoiceNo as InvoiceNo,
	case when dateadd(d,30,InvoiceDate) < getdate() 
		then 'Letter'
		when InvoiceDate > convert(datetime, '07/25/2008', 101)
     		then 'Invoice'
	end as Type,
	ltrim(Substring(ItemDescription,0,50)) as ItemDescription,
	ltrim(Substring(ItemQty,0,8)) as ItemQty
	
	
from StrategicAR_Customers.dbo.Customer_Juros J
--Selects only Invoices that have an amount due
-------------------------------------------------------------------------------------------------------------------------------------------------
left outer join 
(
	select InvoiceNo, AccountNo
	from Debtor D
	inner join DebtorService DS on D.DebtorID = DS.DebtorID
	where OrgID = 2525
) E on E.AccountNo = J.PatientID and E.InvoiceNo = J.InvoiceNo
where J.InvoiceNo not in
(
	select InvoiceNo
	from StrategicAR_Customers.dbo.Customer_Juros
	group by PatientID, InvoiceNo
	having 	sum(cast(Amount as money)) <= 0
)
 
-------------------------------------------------------------------------------------------------------------------------------------------------
OPEN C1
FETCH NEXT FROM C1
INTO 	@PatientID, @LastName, @FirstName, @Address1, @Address2, @City, @State, @Zip, @Phone, @Employer, @EmployerPhone, 
	@InvoiceDate, @DOS, @DOB, @SSN, @Amount, @InvoiceNo, @Type, @ItemDesc, @Qty
 
WHILE @@FETCH_STATUS = 0
BEGIN
if @Type = 'Letter' or @Type = 'Invoice'
  begin	
	
	--initialize @DebtorID 
	set @DebtorID = null
	if @Type = 'Letter'
	begin
		--set @DebtorID = DebtorID if the debtor already exists
		select @DebtorID = DebtorID
		from Debtor
		where OrgID = @OrgID
		and ltrim(rtrim(AccountNo)) = @PatientID
		and StatusID in (1,2,6,9) --New, Suspended, Bad Address, Letter Series
	end
	--07/11/2008 - Nathan Riley - Added to filter correctly and not pickup debtors in between dates above
	else if @Type = 'Invoice'
	begin
		--set @DebtorID = DebtorID if the debtor already exists
		select @DebtorID = DebtorID
		from Debtor
		where OrgID = @OrgID
		and ltrim(rtrim(AccountNo)) = @PatientID
		and StatusID in (6,19) --Bad Address, Invoice
	end
 
	--Initialize @ServiceID
	set @ServiceID = null
	--set @ServiceID = ServiceID if the service already exists anywhere in the Account
	select @ServiceID = ServiceID from DebtorService DS
				inner join Debtor D on D.DebtorID = DS.DebtorID
				inner join Organization O on D.OrgID = O.OrgID
				where InvoiceNo = @InvoiceNo
				--Sachin Sood: June 14, 2008 
				--MaxCare has more than one ORG ID owing to several branches
				--Select the correct ACCOUNT ID based on the ORG ID
				and O.AccountID = (select AccountID from Organization where OrgID = @OrgID)
				--and O.AccountID = @AccountID (**original **)
 
--ABOVE WORKING
 
	
	--if the debtor is new and the service is new then insert
	if @DebtorID is null and @ServiceID is null
	begin
		
		--insert Debtor
		insert into Debtor(	OrgID, SeriesID, TypeID, StatusID, LanguageID, PaidInFull, FirstName, LastName, AccountNo, SSN, 
					BirthDate, CreateDate, CreateUserID, StatusDate, PhoneNo) 
		values(	@OrgID, @SeriesID, 2, case when @Type = 'Letter' then 1 else 19 end, 1, 0, @FirstName, @LastName, @PatientID, 
			replace(@SSN,'-',''), @DOB, getdate(), 1, getdate(), @PHONE)
 
		--get the PK
		set @DebtorID = @@IDENTITY
 
		--log the events
		--Account Created
		set @EventDesc = @FirstName + ' ' + @LastName
		set @Today = getdate()
		exec P_INS_DEBTOREVENT @DebtorID, 6, 1, @Today, @SeriesID, null, @Today, 1, @EventDesc
 
		--Status Changed
		set @Today = getdate()
		exec P_INS_DEBTOREVENT @DebtorID, 10, 1, @Today, @SeriesID, null, @Today, 1
 
		if @Type = 'Invoice'
		begin
			set @Today = getdate()
			exec P_INS_DEBTOREVENT @DebtorID, 10, 19, @Today, @SeriesID, null, @Today, 1
		end
 
		--insert the debtor medical record
		 insert into DebtorMedical(DebtorID, IsDebtor, PatientFirstName, PatientLastName, CreateDate, CreateUserID)   
                        values (@DebtorID, case when @LastName = @Lastname and @FirstName = @FirstName then 1 else 0 end,        
                        case when @FirstName = NULL then ' ' else @FirstName end, @LastName, getdate(), 1)
		
		--insert the debtor detail record
		insert into DebtorDetail(DebtorID) 
		values (@DebtorID)
 
--ABOVE WORKING
		
		--insert the address record
		insert into Address(Address1, Address2, City, StateID, PostalCode, CountryID, CreateDate, CreateUserID) 
		values (@Address1, @Address2, @City, @State, @Zip, 'US', getdate(), 1)
		
		--Get the PK
		set @AddressID = @@IDENTITY
		insert into DebtorAddress(DebtorID, AddressID, SequenceNo, CreateDate, CreateUserID, IsActive)
		values (@DebtorID, @AddressID, 1, getdate(), 1, 1)
		
		--log the event
		--Address Added
		set @EventDesc = @Address1 + ' ' + @Address2 + ' ' + @City + ', ' + @State + '  ' + @Zip
		set @Today = getdate()
		exec P_INS_DEBTOREVENT @DebtorID, 4, 1, @Today, @SeriesID, null, @Today, 1, @EventDesc
		
		--Create the summary record
		insert into DebtorSummary(DebtorID, OriginalAmount, AmountDue, LastPayDate, DebtorSummaryTypeID, DebtorSummaryStatusID, CollAgencyID) 
		values (@DebtorID, 0, 0, '1/1/1900', 1, 1, 2)
		
		set @Count = @Count + 1
	end 
 
 
 --ABOVE WORKS
	--Create the service record
	if @ServiceID is null
	begin
		set @NewServiceID = 0
		exec @NewServiceID = P_INS_DEBTORSERVICE @DebtorID, @DOS, 0, '', @InvoiceNo, 0, '1/1/1900', 1, @InvoiceDate
 
		if @NewServiceID <> 0 
		begin
			if @Type = 'Letter'
			begin
				exec @DebtorInvoiceID = p_ins_DebtorInvoice 	
					@ServiceID = @NewServiceID,
					@OriginalInvoiceDate = 	@InvoiceDate,
					@Reprint = 0,
					@ReprintRequestDate = @Today,
					@LastPayment = @LastPayment,
					@LastAdjustment = @LastAdjustment,
					@ReprintRequestUserID = 1 /* System */
			end
			else if @Type = 'Invoice'
			begin
				exec @DebtorInvoiceID = p_ins_DebtorInvoice 	
					@ServiceID = @NewServiceID,
					@OriginalInvoiceDate = 	@InvoiceDate,
					@Reprint = 1,
					@ReprintRequestDate = @Today,
					@LastPayment = @LastPayment,
					@LastAdjustment = @LastAdjustment,
					@ReprintRequestUserID = 1 /* System */
			end		
 
			if @DebtorInvoiceID <> 0 
			begin
				set @Today = getdate()
				exec P_Ins_DebtorInvoiceDetail @DebtorInvoiceID, '1', @Qty, @DOS, @DetailBalance, @ItemDesc, @Today, 1
			end
		end
 
		set @Balance = @Balance + @Amount
	end
	else
	begin
		
		--update DebtorService set ServiceDesc = substring(ServiceDesc + '; ' + @Desc,1,255) where ServiceID = @ServiceID
		
		select @DebtorInvoiceID = isnull(DI.DebtorInvoiceID,0)
		from DebtorInvoice DI 
		inner join DebtorService DS on DS.ServiceID = DI.ServiceID
		where InvoiceNo = @InvoiceNo
		and DebtorID = @DebtorID
		
		set @Today = getdate()
		
		If @DebtorInvoiceID <> 0
		begin
			select @NextNum = max(cast(ItemNumber as int)) + 1 from DebtorInvoiceDetail where DebtorInvoiceID = @DebtorInvoiceID
			exec P_Ins_DebtorInvoiceDetail @DebtorInvoiceID, @NextNum, @Qty, @DOS, @DetailBalance, @ItemDesc, @Today, 1
		end
 
 
		
				
	end	
end --if @Type = 'Letter' or @Type = 'Invoice'
	FETCH NEXT FROM C1
	INTO 	@PatientID, @LastName, @FirstName, @Address1, @Address2, @City, @State, @Zip, @Phone, @Employer, @EmployerPhone, 
		@InvoiceDate, @DOS, @DOB, @SSN, @Amount, @InvoiceNo, @Type, @ItemDesc, @Qty 
END
CLOSE C1
DEALLOCATE C1
 
--Updates the amount due under the debt recorded box under account history				--Change For New Customer
exec p_upd_debtorevent 2525
 
SET NOCOUNT OFF
 
RETURN
GO

Open in new window

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial