Date a Balance went to Zero

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?

Report Code

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

I haven't gone right through your code yet, but... Can you wrap up your main query section into a table expression and then put something like 'select min(somedate) from (yourbigquery) q where q.totalvisitbalance >= 0"

Actually - it looks like you're populating #temp, so you could do a similar thing there:

select min(somedate) from #temp where totalvisitbalance >= 0

I notice that you're doing this: "CONVERT(VARCHAR,pv.visit,101) as DateOfService"... I wouldn't, because then you can't use things like 'min' on it. Don't convert it to varchar. You should still be able to display it nicely in your report (pick your format in your report, not in the query), but by not converting it, the database will still understand it as a date.

Rob