Edit

Order

Name

Query /* * This script will query the database for claims after @Startdate with a value code 68 or 70 * Only 'pure' claims to be billed to primary ins and created by the middleware are included, no submissions to secondary payers, voids, claims, etc */ DECLARE @StartDate DATE = '2/1/25' DECLARE @68 INT DECLARE @80 INT DECLARE @MedListsId INT dECLARE @Code VARCHAR(5) select @68 = MedListsId from MEdlists where TableName='valuecodes' and Code = '68'-- 68 is 1794 select @80 = MedListsId from MEdlists where TableName='valuecodes' and Code = '80'-- 80 is 5352 select ic.ListName as 'InsCarrier', (case WHEN pvf.ValueCode0MId=@68 OR pvf.ValueCode1MId=@68 OR pvf.ValueCode2MId=@68 OR pvf.ValueCode3MId=@68 OR pvf.ValueCode4MId=@68 OR pvf.ValueCode5MId=@68 OR pvf.ValueCode6MId=@68 OR pvf.ValueCode7MId=@68 OR pvf.ValueCode8MId=@68 OR pvf.ValueCode9MId=@68 OR pvf.ValueCode10MId=@68 OR pvf.ValueCode11MId=@68 THEN 'YES' else '' END) as 'VC68', (case WHEN pvf.ValueCode0MId=@80 OR pvf.ValueCode1MId=@80 OR pvf.ValueCode2MId=@80 OR pvf.ValueCode3MId=@80 OR pvf.ValueCode4MId=@80 OR pvf.ValueCode5MId=@80 OR pvf.ValueCode6MId=@80 OR pvf.ValueCode7MId=@80 OR pvf.ValueCode8MId=@80 OR pvf.ValueCode9MId=@80 OR pvf.ValueCode10MId=@80 OR pvf.ValueCode11MId=@80 THEN 'YES' else '' END) as 'VC80', pp.PatientId, --pp.Last+', '+ pp.First as'Pt Name', pv.TicketNumber, df.ListName as'Facility', CAST(pv.Visit AS DATE) 'VisitFrom', CAST(pv.Entered AS DATE) 'VisitTo', pv.Description, pv.Created, pv.CreatedBy FROM PatientVisit pv left join InsuranceCarriers ic on ic.InsuranceCarriersId = CurrentInsuranceCarriersId left join PatientProfile pp on pp.PAtientProfileid = pv.PatientProfileId LEFT JOIN DoctorFacility df on df.DoctorFacilityId = pv.FacilityId --where JOIN PatientVisitFiling pvf on pvf.PatientVisitId = pv.PatientVisitId AND (valuecode0mid in (@68, @80) OR valuecode1mid in (@68, @80) OR valuecode2mid in (@68, @80) OR valuecode3mid in (@68, @80) OR valuecode4mid in (@68, @80) OR valuecode5mid in (@68, @80) OR valuecode6mid in (@68, @80) OR valuecode7mid in (@68, @80) OR valuecode8mid in (@68, @80) OR valuecode9mid in (@68, @80) OR valuecode10mid in (@68, @80) OR valuecode11mid in (@68, @80) ) AND pv.CurrentCarrier = 1 -- only claims to primary AND LEFT(pv.TicketNumber, 1) = 'G' -- only MW created tickets, no clones without the G in front AND LEN(pv.TicketNumber) = 7 -- G######, so no clones with A/B on the end AND Entered > @StartDate order by InsCarrier, pv.Entered desc /* select * from DoctorFacility where DoctorFacilityId=73 select top 100 * from MedLists select * from MEdlists where code='68' select * from MEdlists where TableName='valuecodes' -- 68 is 1794 select * from cus_GaiaBillingCharges where chargetype=20 */

Notes