Date a balance went to Zero

[jeffsturgeon2002]

In my report code, I am pulling pva.InsBalance + pva.patbalance AS TotalVisitBalance,

What I need is, to know the date in which this balance went to $0.00. Is it possible to script this anyhow?

set nocount on

declare @startdate datetime,
    	@enddate datetime,	
    	@ticketnumber varchar(20)

set @ticketnumber  = CAST(NULL as VARCHAR(20))
set @startdate = ISNULL(NULL,'1/1/1900') 
set @enddate = DATEADD(DAY,1,ISNULL(NULL,'1/1/3000'))

SELECT  ic.ListName AS CarrierName, 
	pva.InsBalance + pva.patbalance AS TotalVisitBalance,
 	ic.address1 as CarrierAddress,
 	ic.city as CarrierCity,
 	ic.state as CarrierState,
 	ic.zip as CarrierZip,
    	icc.ClaimPayerId,
    	pv.Ticketnumber,
    	CONVERT(VARCHAR,pv.visit,101) as DateOfService,
	CONVERT(VARCHAR,pv.firstfileddate,101)AS FirstFiled,
	'DaysBetween'= DATEDIFF(DD,pv.visit,pv.firstfileddate),
	CONVERT(VARCHAR,pv.lastfileddate,101)AS LastFiled,
    	pp.last+', '+pp.first as PatientName,
    	pp.PatientID,
    	ec.Charges as VisitChargesFiled,
    	ec.Procedures as VisitProceduresFiled,
    	CONVERT(VARCHAR,ecf.FileTransmitted,101)AS FileTransmitted,
    	CAST(NULL as DATETIME) as ClaimPrinted,
   	ecf.FiledBy,
    	ecf.SubmissionNumber,
    	ecf.name as ClaimFileName,
    	ch.ClearinghouseName,
    	fm.description as FilingMethod,
    	'Electronic' as FilingType

into #temp
    
FROM 	EDIClaimFile ecf
    	INNER JOIN EDIClaim ec ON ecf.EDIClaimFileId = ec.EDIClaimFileId 
    	INNER JOIN InsuranceCarriers ic ON ec.InsuranceCarriersId = ic.InsuranceCarriersId 
    	INNER JOIN InsuranceCarrierCompany icc ON ic.InsuranceCarriersId = icc.InsuranceCarriersId
    	INNER JOIN patientvisit pv on ec.patientvisitID = pv.patientvisitID
	INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId 
    	INNER JOIN patientprofile pp on pv.patientprofileID = pp.patientprofileID
    	LEFT JOIN (select * from medlists where tablename= 'FilingMethods') fm on ec.filingmethodMID = fm.medlistsID
    	INNER JOIN clearinghouse ch on ecf.clearinghouseID = ch.clearinghouseID

WHERE   ecf.FileTransmitted >= @startdate
    	AND ecf.FileTransmitted < @enddate
    	AND  --Filter on ticket
	(
	(NULL IS NOT NULL AND pv.ticketnumber = @ticketnumber) OR
	(NULL IS NULL)
	)    
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND  --Filter on Carrier
	(
	(NULL IS NOT NULL AND ec.insurancecarriersID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Provider
	(
	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Patient
	(
	(NULL IS NOT NULL AND pv.PatientProfileID IN (NULL)) OR
	(NULL IS NULL)
	)

--------------------------------------------  Paper Claims  ------------------------------------------
INSERT INTO #temp (Carriername,TotalVisitBalance, --CarrierAddress, CarrierCity, CarrierState, CarrierZip,
             ticketnumber, dateofservice, firstfiled, DaysBetween, lastfiled, patientname, patientID, visitchargesfiled, 
            visitproceduresfiled, ClaimPrinted, filedby, claimfilename, clearinghousename, 
         	filingmethod, FilingType)

SELECT  ISNULL(pvpc.Name,'No Carrier') AS CarrierName, 
	pva.InsBalance + pva.patbalance AS TotalVisitBalance,
    	pv.Ticketnumber,
    	CONVERT(VARCHAR,pv.visit,101) as DateOfService,
	CONVERT(VARCHAR,pv.firstfileddate,101)AS FirstFiled,
	'DaysBetween'= DATEDIFF(DD,pv.visit,pv.firstfileddate),
	CONVERT(VARCHAR,pv.lastfileddate,101)AS LastFiled,
    	pp.last+', '+pp.first as PatientName,
    	pp.PatientID,
    	pvpc.Charges as VisitChargesFiled,
    	pvpc.Procedures as VisitProceduresFiled,
    	pvpc.created as ClaimPrinted,
    	pvpc.createdby as FiledBy,    
    	'Paper' as claimfilename,	
    	'' as clearinghousename,
    	fm.description as FilingMethod,
    	'Paper' as FilingType
    
FROM 	PatientvisitPaperClaim pvpc
    	INNER JOIN patientvisit pv on pvpc.patientvisitID = pv.patientvisitID
	INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId 
    	INNER JOIN patientprofile pp on pv.patientprofileID = pp.patientprofileID
    	LEFT JOIN (select * from medlists where tablename= 'FilingMethods') fm on pvpc.filingmethodMID = fm.medlistsID

WHERE   pvpc.created >= @startdate
    	AND pvpc.created < @enddate
    	AND  --Filter on ticket
	(
	(NULL IS NOT NULL AND pv.ticketnumber = @ticketnumber) OR
	(NULL IS NULL)
	)    
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
--    AND  --Filter on Carrier
--	(
--	(NULL IS NOT NULL AND ec.insurancecarriersID IN (NULL)) OR
--	(NULL IS NULL)
--	)
	AND  --Filter on Provider
	(
	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Patient
	(
	(NULL IS NOT NULL AND pv.PatientProfileID IN (NULL)) OR
	(NULL IS NULL)
	)


IF '1' = '1' 
BEGIN
    select *
    from #temp
    order by ticketnumber
END

IF '1' = '2' 
BEGIN
    select *
    from #temp
    where filingtype = 'Electronic'
    order by ticketnumber
END


IF '1' = '3' 
BEGIN
    select *
    from #temp
    where filingtype = 'Paper'
    order by ticketnumber
END

drop table #temp

Jeff

SELECT * FROM users WHERE clue > 0

 

[AlexCuse]

Jeff -

I don't have time to read all your code, but shouldn't this be as simple as left joining to a derived table? My suggestion would be something like this:

left join
(
select PatientID, min(DateColumn), Balance
from MyTable
where Balance = 0
group by PatientID, Balance
) a
on SomeTable.PatientID = a.PatientID

You will need to update it for your table and column names of course.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLBill]

What I need is, to know the date in which this balance went to $0.00. Is it possible to script this anyhow?

I don't know, maybe? You didn't provide any data samples or anything about your data. Do you store the amount of the balance each day. If you don't keep track of the balance each day, then the answer is gonna be - no.

-SQLBill

Posting advice: FAQ481-4875