SQLTeam.com | Weblogs | Forums

Procedure t sql .. help urgency

sql-server-2008
ssms

#1

I need row count of all headers and details to be displayed in trailer .. how do i do it .

column name sequence number
header 1
detail 2
detail 3
detail 4
header 1
detail 2
detail 3
trailer should be showing as 7 , but now it showing 0

here is the code .

ALTER proc [dbo].[STMT_EOR]
as
begin

WITH with_stmt_bills as (
SELECT
BarAcct_Main.VisitID,
led_bills.LedInsurance_MisInsID,
led_bills.LedInsuranceBillNumberID,
BarAcctBill_Main.TotalCharges,
led_bills.LedInsuranceBillPaymentTotal as TotalInsurancePayments,
led_bills.LedInsuranceBillAdjustmentTotal as TotalInsuranceAdjustments,
led_bills.LedInsuranceBillBalance as EstimatedInsuranceDue,
FORMAT(BarStatement_Main.CreatedDate, 'yyyyMMdd') as StatementGeneratedDate,
BarStatement_AcctBills_BillMsgText.TextLine,
BarStatement_AcctBills_BillMsgText.TextID,
FORMAT(BarStatement_Main.DueDate, 'yyyyMMdd') as StatementDueDate,
CASE
WHEN BarContract_Main.ContractTotalAmount IS NULL
THEN BarAcct_Balances.PatientBalance
ELSE BarContract_Main.ContractTotalAmount
END as PatientStatementAmountDue,
BarAcct_Balances.LastTransactionNumber as BalanceLastTransactionNumber,
BarAcct_Balances.Balance as AccountBalance,
BarAcctBill_Main.CollectionAgency_BarCollAgencyID as CollectionAgency,
FORMAT(led_bills.LedDateID, 'yyyyMMdd') as LedBillDateID

FROM livefdb.dbo.BarAcct_Main

INNER JOIN livefdb.dbo.BarAcctBill_Main ON
(BarAcctBill_Main.SourceID = BarAcct_Main.SourceID)
AND (SUBSTRING(BarAcctBill_Main.BarAcctBillID, 0, CHARINDEX('!', BarAcctBillID)) = BarAcct_Main.VisitID)

INNER JOIN livefdb.dbo.BarAcct_LedInsuranceBills led_bills ON
led_bills.VisitID = BarAcct_Main.VisitID
AND led_bills.LedInsuranceBillNumberID = RIGHT(BarAcctBillID, 1)

LEFT JOIN livefdb.dbo.BarStatement_AcctBills on
led_bills.VisitID = BarStatement_AcctBills.VisitID
AND led_bills.LedInsuranceBillNumberID = BarStatement_AcctBills.BillNumberID

LEFT JOIN livefdb.dbo.BarStatement_Main on
BarStatement_AcctBills.BarStatementID = BarStatement_Main.BarStatementID

LEFT JOIN livefdb.dbo.BarStatement_AcctBills_BillMsgText on
BarStatement_AcctBills_BillMsgText.VisitID = BarStatement_AcctBills.VisitID
and BarStatement_AcctBills_BillMsgText.BarStatementID = BarStatement_AcctBills.BarStatementID
AND BarStatement_AcctBills_BillMsgText.BillNumberID = led_bills.LedInsuranceBillNumberID
And BarStatement_AcctBills_BillMsgText.TextLine is not null

INNER JOIN livefdb.dbo.BarAcct_Balances on
BarAcct_Balances.VisitID = BarAcct_Main.VisitID
AND FORMAT(BarAcct_Balances.PostDateID, 'yyyyMMdd') = FORMAT(BarAcctBill_Main.PostDate, 'yyyyMMdd')

LEFT JOIN livefdb.dbo.BarContract_Main ON
BarContract_Main.VisitID = BarAcct_Main.VisitID

--SELECTS BY FACILITY

WHERE BarAcct_Main.Facility_MisFacID = 'EOR'
AND BarStatement_Main.CreatedDate >= DATEADD(day, DATEDIFF(day, 0, getDate()) -1, 0)
AND BarStatement_Main.CreatedDate < DATEADD(day, DATEDIFF(day, 0, getDate()), 0)
-- AND FORMAT(BarStatement_Main.CreatedDate,'yyyyMMdd') BETWEEN '20180427' AND '20180428'
-- WHERE BarAcct_Main.Facility_MisFacID = 'EOR' --EORH
),

--/////////// WITH STATEMENT 2
full_stmt_bills as (
SELECT
VisitID,

							(SELECT top 1 StatementGeneratedDate FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
									AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
									order by StatementGeneratedDate desc) as StatementGeneratedDate,
					
							(SELECT top 1 TotalCharges FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
									AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
									order by TotalCharges desc) as TotalCharges,
					
							(SELECT top 1 TotalInsurancePayments FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
								--	AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
									and with_stmt_bills.LedInsurance_MisInsID != 'SP'
									order by TotalInsurancePayments desc) as TotalInsurancePayments,

							(SELECT top 1 TotalInsuranceAdjustments FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
									--	AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
										AND with_stmt_bills.LedInsurance_MisInsID != 'SP'
									ORDER by TotalInsuranceAdjustments desc) as TotalInsuranceAdjustments,

							(SELECT top 1 TotalInsurancePayments FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
									--	AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
										AND with_stmt_bills.LedInsurance_MisInsID = 'SP'
									ORDER by TotalInsurancePayments desc) as TotalPatientPayments,

							(SELECT top 1 TotalInsuranceAdjustments FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
									--	AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
										AND with_stmt_bills.LedInsurance_MisInsID = 'SP'
									ORDER by TotalInsuranceAdjustments desc) as TotalPatientAdjustments,

							(SELECT top 1 EstimatedInsuranceDue FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
									--	AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
										AND with_stmt_bills.LedInsurance_MisInsID = 'SP'
									ORDER by BalanceLastTransactionNumber desc) as EstimatedInsuranceDue,

							(SELECT top 1 PatientStatementAmountDue FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
										AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
									ORDER BY BalanceLastTransactionNumber desc) as StatementAmountDue,

							(SELECT top 1 TextLine FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
										AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
									ORDER by TextID desc) as StatementMessage,

							(SELECT top 1 StatementDueDate FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
										AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
									ORDER by StatementDueDate desc) as StatementDueDate,

							(SELECT top 1 AccountBalance FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
										AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
																) as AccountBalance,

							(SELECT top 1 CollectionAgency FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
										AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
								
									) as CollectionAgency,
							LedBillDateID
							 FROM with_stmt_bills internal_table
				),

--/////// WITH STATEMENT 3 (USES WITH STATEMENT 2 (with_stmt --> full_stmt_bills --> all_data

all_data AS (
SELECT DISTINCT
'EOR' as ClientCode,
RegAcct_Main.AccountNumber as PatientAccountNumber,
RegAcct_Main.Facility_MisFacID as FacilityID,
HimRec_MedicalRecordNumbers.PrefixMedicalRecordNumber as PatientMedicalRecordNumber,
HimRec_Main.NameFirst as PatientFirstName,
HimRec_Main.NameLast as PatientLastName,
subs_guar.NameFirst as GuarantorFirstName,
subs_guar.NameLast as GuarantorLastName,
subs_guar.Address1 as GuarantorAddress1,
subs_guar.Address2 as GuarantorAddress2,
subs_guar.City as GuarantorCity,
subs_guar.State_MisStateProvID as GuarantorState,
REPLACE(subs_guar.Zip, '-','') as GuarantorZIP,
full_stmt_bills.StatementGeneratedDate,
FORMAT(RegAcct_Main.AdmitDateTime,'yyyyMMdd') as AdmitDate,
FORMAT(BarAcct_Main.DischargeDate,'yyyyMMdd') as DischargeDate,
full_stmt_bills.AccountBalance,
full_stmt_bills.TotalCharges,
full_stmt_bills.TotalInsurancePayments,
full_stmt_bills.TotalInsuranceAdjustments,
full_stmt_bills.TotalPatientPayments,
full_stmt_bills.TotalPatientAdjustments,
full_stmt_bills.EstimatedInsuranceDue,
full_stmt_bills.StatementMessage,
full_stmt_bills.StatementAmountDue,
full_stmt_bills.StatementDueDate,
full_stmt_bills.CollectionAgency as CollectCode, --Lol I totally don't know what this is
NULL as StatementNumber,
BarAcct_Main.CurrentFinancialClass_MisFinClassID as CurrentFinancialClass,
BarAcct_Txns.TransactionNumberID as TxnNumber,
BarTxnType_Main.BarTxnTypeID as TxnDescriptionCode,
BarCdm_Main.Name as TxnDescription,

CONVERT(VARCHAR, BarAcct_Txns.TransactionAmount) as DollarAmount

FROM livefdb.dbo.RegAcct_Main
-----REG tables------
LEFT JOIN livefdb.dbo.RegAcct_Guarantor ON
(RegAcct_Main.SourceID = RegAcct_Guarantor.SourceID)
AND (RegAcct_Main.VisitID = RegAcct_Guarantor.VisitID)

------HIM tables-------
INNER JOIN livefdb.dbo.HimRec_MedicalRecordNumbers ON
HimRec_MedicalRecordNumbers.PatientID = RegAcct_Main.PatientID

INNER JOIN livefdb.dbo.HimRec_Main ON
HimRec_Main.PatientID = RegAcct_Main.PatientID

LEFT JOIN livefdb.dbo.HimSubs_Main subs_guar ON
(subs_guar.SourceID = RegAcct_Main.SourceID)
AND (subs_guar.HimSubsID = RegAcct_Guarantor.GuarantorOid_HimSubsID)

--=====BAR tables=====--
INNER JOIN livefdb.dbo.BarAcct_Main on
(BarAcct_Main.SourceID = RegAcct_Main.SourceID)
AND (BarAcct_Main.VisitID = RegAcct_Main.VisitID)

INNER JOIN livefdb.dbo.BarAcct_Txns ON
BarAcct_Txns.VisitID = RegAcct_Main.VisitID
INNER join BarTxnType_Main on
BarTxnType_Main.BarTxnTypeID = BarAcct_Txns.TransactionType_BarTxnTypeID
AND BarTxnType_Main.BarTxnTypeID IN ('ADJ','CHG','PAY')

LEFT JOIN livefdb.dbo.BarCdm_Main ON
BarAcct_Txns.TransactionChargeCode_BarCdmID = BarCdm_Main.BarCdmID

INNER JOIN full_stmt_bills ON
(full_stmt_bills.VisitID = BarAcct_Main.VisitID)

---SELECTS BY FACILITY

WHERE BarAcct_Main.Facility_MisFacID = 'EOR' --EORH
	)

--END WITH STATEMENTS

--HEADER
SELECT DISTINCT
'STMT HEADER' as RecordType,
ClientCode,
1 as SequenceNumber,
PatientAccountNumber,
PatientMedicalRecordNumber,
PatientFirstName,
PatientLastName,
GuarantorFirstName,
GuarantorLastName,
GuarantorAddress1,
GuarantorAddress2,
GuarantorCity,
GuarantorState,
GuarantorZIP,
StatementGeneratedDate,
AdmitDate,
DischargeDate,
AccountBalance,
TotalCharges,
TotalInsurancePayments,
TotalInsuranceAdjustments,
TotalPatientPayments,
TotalPatientAdjustments,
EstimatedInsuranceDue,
StatementMessage,
StatementAmountDue,
StatementDueDate

-- CollectCode,
-- StatementNumber,
-- CurrentFinancialClass

into STMT_EX
FROM all_data Statement_Header

Where PatientMedicalRecordNumber like 'MR%'

UNION 

SELECT DISTINCT
'STMT DETAIL' as RecordType,
ClientCode,
DENSE_RANK() OVER (PARTITION BY PatientAccountNumber, StatementGeneratedDate 
		ORDER BY TxnNumber, DollarAmount) + 1 as SequenceNumber,
PatientAccountNumber,
PatientMedicalRecordNumber,
CASE TxnDescriptionCode
	WHEN 'ADJ' THEN 'ADJUSTMENT DETAIL'
	WHEN 'PAY' THEN 'PAYMENT DETAIL'
	WHEN 'CHG' THEN 'CHARGE SUMMARY BY REVENUE CODE'
	END as 'TxnDescriptionCode',
TxnDescription,
DollarAmount,
NULL, --9
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL

-- NULL,
-- NULL,
-- NULL --30

FROM all_data Statement_Detail
Where PatientMedicalRecordNumber like 'MR%'

UNION 

SELECT
'TRAILER' as RecordType,
'EOR' as ClientCode,
CONVERT(VARCHAR, @@ROWCOUNT) as Recordcount, --Rowcount gets all rows All Stmt Headers + Stmt Details
CONVERT(varchar, SUM(StatementAmountDue), 0) as StatementCount,
FORMAT(CURRENT_TIMESTAMP, 'yyyyMMdd') as FileDate,
--FILE NAME IS

.
--HH and MM can be 00; NN is 00
CONCAT( 'OVM', --TLS
'EOR', --LLS
DATEPART(yyyy, CURRENT_TIMESTAMP), --YYYY
DATEPART(MM, CURRENT_TIMESTAMP), --MM
DATEPART(dd, CURRENT_TIMESTAMP), --DD
'00', --HH
'00', --MM
'00', --NN
'.', -- .
'STMT' --TYPE
) as 'FileName',
NULL, --7
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
-- NULL,
-- NULL,
-- NULL --30

FROM all_data Statement_Trailer

order by PatientAccountNumber DESC, PatientMedicalRecordNumber DESC, SequenceNumber ASC


#2

Can you post some of your input data and the results you want to see?


#3

its a confidential data , so i cant post results here sir

but output looks like sequence number as a column name

under sequence number it it shows
header as 1
detail as 2
detail as 3
detail as 4
header as 1
detail as 2
detail as 3
trailer as 0, but it should look as 7


#4

post bogus sample data like the following

create table #persons(personId int, firstname varchar(50), 
lastname varchar(50), telephone varchar(50))

create table #correspondence(senderId int, 
recipientId int, message varchar(50), sentdate datetime )


insert into #persons
select 1, 'Darth', 'Vader', '1212' union
select 2, 'Luke', 'Skywalker', '3334355 Wars' union
select 3, 'Chewy', 'Backer', '2061213333' union
select 4, 'Princess', 'Lee Yah', '20612122' union
select 5, 'Ewok dem little teddy bears', 'Real Woke', '20612145454' union
select 6, 'Darth', 'Revan', '2061217766' union
select 7, 'Darth', 'Sidious',  '2061213336' union
select 8, 'Han', 'Solo',  '611' 

insert into #correspondence
select 1,2  , 'I am your dada', getdate() -100 union
select 2,3  , 'Engage thrusters!', getdate() -90 union
select 7,4  , 'hey cutie', getdate() -80 union
select 3,8  , 'mmmmrrrmmmmrrrhhhhh', getdate() -70


select sender.firstname + ' ' + 
         sender.lastname + ' said ' + c.message 
+ ' to ' + recipient.firstname + ' ' + recipient.lastname
	   
  from #persons sender
  join #correspondence c on sender.personId = c.senderId
  join #persons recipient on recipient.personId = c.recipientId

drop table #persons
drop table #correspondence

#5

Actually input data imports form the emr system , .. but you can see that in the trailer section in the bottom of the code , we have the follwoing command but it doesn't show the total number of rows count in trailer , it shows as 0

CONVERT(VARCHAR, @@ROWCOUNT) as Recordcount, --Rowcount gets all rows All Stmt Headers + Stmt Details
CONVERT(varchar, SUM(StatementAmountDue), 0) as StatementCount,


#6

Your SQL is almost impossible to read. but it looks like you;re expecting @@rowcount to hold the count of rows from the first query in the UNION ALL. It won't do that.


#7

Now I’m able to see the count with convert (bigint, count (*)) as sequencenumber

But the problem now is if there are 475 rows it’s disolaying total as 498


#8

At the risk of being sarcastic: You're expecting 475 but SQL is correctly reporting 498


#9

/******************/

ALTER proc [dbo].[PM_STMT_EOR_testing]
as
begin

WITH with_stmt_bills as (
SELECT
BarAcct_Main.VisitID,
led_bills.LedInsurance_MisInsID,
led_bills.LedInsuranceBillNumberID,
BarAcctBill_Main.TotalCharges,
led_bills.LedInsuranceBillPaymentTotal as TotalInsurancePayments,
led_bills.LedInsuranceBillAdjustmentTotal as TotalInsuranceAdjustments,
led_bills.LedInsuranceBillBalance as EstimatedInsuranceDue,
FORMAT(BarStatement_Main.CreatedDate, 'yyyyMMdd') as StatementGeneratedDate,
BarStatement_AcctBills_BillMsgText.TextLine,
BarStatement_AcctBills_BillMsgText.TextID,
FORMAT(BarStatement_Main.DueDate, 'yyyyMMdd') as StatementDueDate,
CASE
WHEN BarContract_Main.ContractTotalAmount IS NULL
THEN BarAcct_Balances.PatientBalance
ELSE BarContract_Main.ContractTotalAmount
END as PatientStatementAmountDue,
BarAcct_Balances.LastTransactionNumber as BalanceLastTransactionNumber,
BarAcct_Balances.Balance as AccountBalance,
BarAcctBill_Main.CollectionAgency_BarCollAgencyID as CollectionAgency,
FORMAT(led_bills.LedDateID, 'yyyyMMdd') as LedBillDateID

FROM testfdb.dbo.BarAcct_Main

INNER JOIN testfdb.dbo.BarAcctBill_Main ON
(BarAcctBill_Main.SourceID = BarAcct_Main.SourceID)
AND (SUBSTRING(BarAcctBill_Main.BarAcctBillID, 0, CHARINDEX('!', BarAcctBillID)) = BarAcct_Main.VisitID)

INNER JOIN testfdb.dbo.BarAcct_LedInsuranceBills led_bills ON
led_bills.VisitID = BarAcct_Main.VisitID
AND led_bills.LedInsuranceBillNumberID = RIGHT(BarAcctBillID, 1)

LEFT JOIN testfdb.dbo.BarStatement_AcctBills on
led_bills.VisitID = BarStatement_AcctBills.VisitID
AND led_bills.LedInsuranceBillNumberID = BarStatement_AcctBills.BillNumberID

LEFT JOIN testfdb.dbo.BarStatement_Main on
BarStatement_AcctBills.BarStatementID = BarStatement_Main.BarStatementID

LEFT JOIN testfdb.dbo.BarStatement_AcctBills_BillMsgText on
BarStatement_AcctBills_BillMsgText.VisitID = BarStatement_AcctBills.VisitID
and BarStatement_AcctBills_BillMsgText.BarStatementID = BarStatement_AcctBills.BarStatementID
AND BarStatement_AcctBills_BillMsgText.BillNumberID = led_bills.LedInsuranceBillNumberID
And BarStatement_AcctBills_BillMsgText.TextLine is not null

INNER JOIN testfdb.dbo.BarAcct_Balances on
BarAcct_Balances.VisitID = BarAcct_Main.VisitID
AND FORMAT(BarAcct_Balances.PostDateID, 'yyyyMMdd') = FORMAT(BarAcctBill_Main.PostDate, 'yyyyMMdd')

LEFT JOIN testfdb.dbo.BarContract_Main ON
BarContract_Main.VisitID = BarAcct_Main.VisitID

--SELECTS BY FACILITY

WHERE BarAcct_Main.Facility_MisFacID = 'EOR'
and BarStatement_Main.CreatedDate >= DATEADD(day, DATEDIFF(day, 0, getDate()) -120, 0)
AND BarStatement_Main.CreatedDate < DATEADD(day, DATEDIFF(day, 0, getDate()), 0)
-- WHERE BarAcct_Main.Facility_MisFacID = 'EOR' --EORH
),

--/////////// WITH STATEMENT 2
full_stmt_bills as (
SELECT
VisitID,

							(SELECT top 1 StatementGeneratedDate FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
									AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
									order by StatementGeneratedDate desc) as StatementGeneratedDate,
					
							(SELECT top 1 TotalCharges FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
									AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
									order by TotalCharges desc) as TotalCharges,
					
							(SELECT top 1 TotalInsurancePayments FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
								--	AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
									and with_stmt_bills.LedInsurance_MisInsID != 'SP'
									order by TotalInsurancePayments desc) as TotalInsurancePayments,

							(SELECT top 1 TotalInsuranceAdjustments FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
									--	AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
										AND with_stmt_bills.LedInsurance_MisInsID != 'SP'
									ORDER by TotalInsuranceAdjustments desc) as TotalInsuranceAdjustments,

							(SELECT top 1 TotalInsurancePayments FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
									--	AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
										AND with_stmt_bills.LedInsurance_MisInsID = 'SP'
									ORDER by TotalInsurancePayments desc) as TotalPatientPayments,

							(SELECT top 1 TotalInsuranceAdjustments FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
									--	AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
										AND with_stmt_bills.LedInsurance_MisInsID = 'SP'
									ORDER by TotalInsuranceAdjustments desc) as TotalPatientAdjustments,

							(SELECT top 1 EstimatedInsuranceDue FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
									--	AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
										AND with_stmt_bills.LedInsurance_MisInsID = 'SP'
									ORDER by BalanceLastTransactionNumber desc) as EstimatedInsuranceDue,

							(SELECT top 1 PatientStatementAmountDue FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
										AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
									ORDER BY BalanceLastTransactionNumber desc) as StatementAmountDue,

							(SELECT top 1 TextLine FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
										AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
									ORDER by TextID desc) as StatementMessage,

							(SELECT top 1 StatementDueDate FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
										AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
									ORDER by StatementDueDate desc) as StatementDueDate,

							(SELECT top 1 AccountBalance FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
										AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
																) as AccountBalance,

							(SELECT top 1 CollectionAgency FROM with_stmt_bills
									WHERE VisitID = internal_table.VisitID
										AND with_stmt_bills.LedBillDateID = internal_table.LedBillDateID
								
									) as CollectionAgency,
							LedBillDateID
							 FROM with_stmt_bills internal_table
				),

--/////// WITH STATEMENT 3 (USES WITH STATEMENT 2 (with_stmt --> full_stmt_bills --> all_data

all_data AS (
SELECT DISTINCT
'EOR' as ClientCode,
RegAcct_Main.AccountNumber as PatientAccountNumber,
RegAcct_Main.Facility_MisFacID as FacilityID,
HimRec_MedicalRecordNumbers.PrefixMedicalRecordNumber as PatientMedicalRecordNumber,
HimRec_Main.NameFirst as PatientFirstName,
HimRec_Main.NameLast as PatientLastName,
subs_guar.NameFirst as GuarantorFirstName,
subs_guar.NameLast as GuarantorLastName,
subs_guar.Address1 as GuarantorAddress1,
subs_guar.Address2 as GuarantorAddress2,
subs_guar.City as GuarantorCity,
subs_guar.State_MisStateProvID as GuarantorState,
REPLACE(subs_guar.Zip, '-','') as GuarantorZIP,
full_stmt_bills.StatementGeneratedDate,
FORMAT(RegAcct_Main.AdmitDateTime,'yyyyMMdd') as AdmitDate,
FORMAT(BarAcct_Main.DischargeDate,'yyyyMMdd') as DischargeDate,
full_stmt_bills.AccountBalance,
full_stmt_bills.TotalCharges,
full_stmt_bills.TotalInsurancePayments,
full_stmt_bills.TotalInsuranceAdjustments,
full_stmt_bills.TotalPatientPayments,
full_stmt_bills.TotalPatientAdjustments,
full_stmt_bills.EstimatedInsuranceDue,
full_stmt_bills.StatementMessage,
full_stmt_bills.StatementAmountDue,
full_stmt_bills.StatementDueDate,
full_stmt_bills.CollectionAgency as CollectCode, --Lol I totally don't know what this is
NULL as StatementNumber,
BarAcct_Main.CurrentFinancialClass_MisFinClassID as CurrentFinancialClass,
BarAcct_Txns.TransactionNumberID as TxnNumber,
BarTxnType_Main.BarTxnTypeID as TxnDescriptionCode,
BarCdm_Main.Name as TxnDescription,

CONVERT(VARCHAR, BarAcct_Txns.TransactionAmount) as DollarAmount

FROM testfdb.dbo.RegAcct_Main
-----REG tables------
LEFT JOIN testfdb.dbo.RegAcct_Guarantor ON
(RegAcct_Main.SourceID = RegAcct_Guarantor.SourceID)
AND (RegAcct_Main.VisitID = RegAcct_Guarantor.VisitID)

------HIM tables-------
INNER JOIN testfdb.dbo.HimRec_MedicalRecordNumbers ON
HimRec_MedicalRecordNumbers.PatientID = RegAcct_Main.PatientID

INNER JOIN testfdb.dbo.HimRec_Main ON
HimRec_Main.PatientID = RegAcct_Main.PatientID

LEFT JOIN testfdb.dbo.HimSubs_Main subs_guar ON
(subs_guar.SourceID = RegAcct_Main.SourceID)
AND (subs_guar.HimSubsID = RegAcct_Guarantor.GuarantorOid_HimSubsID)

--=====BAR tables=====--
INNER JOIN testfdb.dbo.BarAcct_Main on
(BarAcct_Main.SourceID = RegAcct_Main.SourceID)
AND (BarAcct_Main.VisitID = RegAcct_Main.VisitID)

INNER JOIN testfdb.dbo.BarAcct_Txns ON
BarAcct_Txns.VisitID = RegAcct_Main.VisitID
INNER join BarTxnType_Main on
BarTxnType_Main.BarTxnTypeID = BarAcct_Txns.TransactionType_BarTxnTypeID
AND BarTxnType_Main.BarTxnTypeID IN ('ADJ','CHG','PAY')

LEFT JOIN testfdb.dbo.BarCdm_Main ON
BarAcct_Txns.TransactionChargeCode_BarCdmID = BarCdm_Main.BarCdmID

INNER JOIN full_stmt_bills ON
(full_stmt_bills.VisitID = BarAcct_Main.VisitID)

---SELECTS BY FACILITY

WHERE BarAcct_Main.Facility_MisFacID = 'EOR' --EORH
)

--END WITH STATEMENTS

--HEADER
SELECT DISTINCT
'STMT HEADER' as RecordType,
ClientCode,
1 as SequenceNumber,
PatientAccountNumber,
PatientMedicalRecordNumber,
PatientFirstName,
PatientLastName,
GuarantorFirstName,
GuarantorLastName,
GuarantorAddress1,
GuarantorAddress2,
GuarantorCity,
GuarantorState,
GuarantorZIP,
StatementGeneratedDate,
AdmitDate,
DischargeDate,
AccountBalance,
TotalCharges,
TotalInsurancePayments,
TotalInsuranceAdjustments,
TotalPatientPayments,
TotalPatientAdjustments,
EstimatedInsuranceDue,
StatementMessage,
StatementAmountDue,
StatementDueDate

-- CollectCode,
-- StatementNumber,
-- CurrentFinancialClass

into STMT_EO
FROM all_data Statement_Header

UNION 

SELECT DISTINCT
'STMT DETAIL' as RecordType,
ClientCode,
DENSE_RANK() OVER (PARTITION BY PatientAccountNumber, StatementGeneratedDate 
		ORDER BY TxnNumber, DollarAmount) + 1 as SequenceNumber,
PatientAccountNumber,
PatientMedicalRecordNumber,
CASE TxnDescriptionCode
	WHEN 'ADJ' THEN 'ADJUSTMENT DETAIL'
	WHEN 'PAY' THEN 'PAYMENT DETAIL'
	WHEN 'CHG' THEN 'CHARGE SUMMARY BY REVENUE CODE'
	END as 'TxnDescriptionCode',
TxnDescription,
DollarAmount,
NULL, --9
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL

-- NULL,
-- NULL,
-- NULL --30

FROM all_data Statement_Detail

UNION 

SELECT
'TRAILER' as RecordType,
'EOR' as ClientCode,
CONVERT(bigint, COUNT(*) ) as SequenceNumber, --Rowcount gets all rows All Stmt Headers + Stmt Details
CONVERT(varchar, SUM(StatementAmountDue), 0) as StatementCount,
FORMAT(CURRENT_TIMESTAMP, 'yyyyMMdd') as FileDate,
--FILE NAME IS

.
--HH and MM can be 00; NN is 00
CONCAT( 'OHW', --TLS
'EOR', --LLS
DATEPART(yyyy, CURRENT_TIMESTAMP), --YYYY
DATEPART(MM, CURRENT_TIMESTAMP), --MM
DATEPART(dd, CURRENT_TIMESTAMP), --DD
'00', --HH
'00', --MM
'00', --NN
'.', -- .
'STMT' --TYPE
) as 'FileName',
NULL, --7
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
-- NULL,
-- NULL,
-- NULL --30

FROM all_data Statement_Trailer

order by PatientAccountNumber DESC, SequenceNumber ASC

Gbritton,

Thanks for your help ..
The problem what i'm facing here again is i'm getting pipe delimiters more in detail section, since we used union in code detail section doesn't have all details as headers .. how to avoid pipe delimiters please ..

uploaded you sample result how it looks .. please help me