Procedure t sql .. help urgency

salukuri · May 11, 2018, 2:33pm

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

gbritton · May 11, 2018, 3:42pm

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

yosiasz · May 11, 2018, 4:44pm

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

gbritton · May 11, 2018, 8:11pm

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.

gbritton · May 11, 2018, 8:33pm

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