Hi all
I've got a couple queries successfully doing the following process:
1. Selecting all records that have a certain date range
2. From that query then I need to look into history table that can have several of the same "unique identifiers" and return all of the "history" for that "unique identifier".
This works great, however I think this can be combined into one query and I am looking for help on how to do this.
The one thing I am running into is that the same "unique identifier" can show up in the 1st query several times, that is OK, but I want to limit that "unique identifier" to only pull the history for them one time regardless of how many times they appear in 1st query.
In the first query I am stuffing a field with "1" as a way to tag that claim for the program it gets fed into as being the "active" record. The second query needs to stuff that field with the "inactive" value.
I have included the existing code now that does work, but I am looking for guidance on how to combine this into one query if possible.
truncate table dbo.tbl_auto_audit_raw
insert into dbo.tbl_auto_audit_raw (plan_id,
patient_id,
serv_date_from,serv_date_to,
provider_id,
spec_code,
claim_number,
sex, dob, loc_code,
prim_diag, sec_diag, third_diag,fourth_diag, fifth_diag,sixth_diag,
proc_code,
modifier1,
--modifier2, not in sql yet
--modifier3, not in sql yet
--modifier4, not in sql yet
--serv_units,don't know what this is
--cap_flag, don't see this being populated in the old fox code
amount_billed,
amount_paid,
max_allowed,
active_flag)
select 'NHP1' as plan_id,
claim.casubno + claim.capersno + ' ' as patient_id,
convert(char(8), claim.CAPRIMDATE, 112) as serv_date_from,
convert(char(8), claim.CATHRUDATE, 112) as serv_date_to,
--provider zip needs to go here
claim.CAVENDOR as provider_id,
-- this "provider id" looks more like a vendor id, ask someone to make sure
left(claim.CAPCPSPEC, 5) as spec_code,
claim.caclaim + claim.cbline as claim_number,
claim.CASEX as sex,
convert(char(8),member.aadob, 112) as dob,
left(claim.caplace, 2) as loc_code,
claim.cadx1 as prim_diag,
claim.cadx2 as sec_diag,
claim.cadx3 as third_diag,
claim.cadx4 as fourth_diag,
claim.cadx5 as fifth_diag,
claim.cadx6 as sixth_diag,
left( claim.CBPROCCODE, 5) as proc_code,
claim.CBMODIFIER as modifier1,
--cbmodifier2
--cbmodifier3
--cbmodifier4
--serv_units --
--cap_flag,
left(cast(claim.CABILLED as char), 9) as amount_billed,
left(cast(claim.CBNET as char),9) as amount_paid,
left(cast(claim.CBALLOWED as char),9) as max_allowed,
'1' as active_flag
from fin.dbo.vw_MEV_OP as claim inner join nhpri_diam_ds01.dbo.tbl_CurrentMemberDimension as member
on claim.capersno = member.aapersno and claim.casubno = member.aasubno
where claim.carecdate between '01/01/2005' and '01/31/3005'
select * from dbo.tbl_auto_audit_raw
-- this gets the history for each member who has a claim in the first query
-- we attempted to get all the history and not to worry about dups, but
-- the filesize was too big to open so we had to scale it down and put in
-- xtra logic to only get each member's history one time
insert into dbo.tbl_auto_audit_raw (plan_id,
patient_id,
serv_date_from,serv_date_to,
provider_id,
spec_code,
claim_number,
sex, dob, loc_code,
prim_diag, sec_diag, third_diag,fourth_diag, fifth_diag,sixth_diag,
proc_code,
modifier1,
--modifier2, not in sql yet
--modifier3, not in sql yet
--modifier4, not in sql yet
--serv_units,don't know what this is
--cap_flag, don't see this being populated in the old fox code
amount_billed,
amount_paid,
max_allowed,
active_flag)
select 'NHP1' as plan_id,
claim.casubno + claim.capersno + ' ' as patient_id,
convert(char(8), claim.CAPRIMDATE, 112) as serv_date_from,
convert(char(8), claim.CATHRUDATE, 112) as serv_date_to,
--provider zip needs to go here
claim.CAVENDOR as provider_id,
-- this "provider id" looks more like a vendor id, ask someone to make sure
left(claim.CAPCPSPEC, 5) as spec_code,
claim.caclaim + claim.cbline as claim_number,
claim.CASEX as sex,
convert(char(8),member.aadob, 112) as dob,
left(claim.caplace, 2) as loc_code,
claim.cadx1 as prim_diag,
claim.cadx2 as sec_diag,
claim.cadx3 as third_diag,
claim.cadx4 as fourth_diag,
claim.cadx5 as fifth_diag,
claim.cadx6 as sixth_diag,
left( claim.CBPROCCODE, 5) as proc_code,
claim.CBMODIFIER as modifier1,
--cbmodifier2
--cbmodifier3
--cbmodifier4
--serv_units --
--cap_flag,
left(cast(claim.CABILLED as char), 9) as amount_billed,
left(cast(claim.CBNET as char),9) as amount_paid,
left(cast(claim.CBALLOWED as char),9) as max_allowed,
'' as active_flag
from fin.dbo.vw_MEV_OP as claim
inner join nhpri_diam_ds01.dbo.tbl_CurrentMemberDimension as member
on claim.capersno = member.aapersno and claim.casubno = member.aasubno
where claim.carecdate between '01/01/2004' and '12/31/3004' and
claim.casubno + claim.capersno in (select distinct patient_id from dbo.tbl_auto_audit_raw)
I've got a couple queries successfully doing the following process:
1. Selecting all records that have a certain date range
2. From that query then I need to look into history table that can have several of the same "unique identifiers" and return all of the "history" for that "unique identifier".
This works great, however I think this can be combined into one query and I am looking for help on how to do this.
The one thing I am running into is that the same "unique identifier" can show up in the 1st query several times, that is OK, but I want to limit that "unique identifier" to only pull the history for them one time regardless of how many times they appear in 1st query.
In the first query I am stuffing a field with "1" as a way to tag that claim for the program it gets fed into as being the "active" record. The second query needs to stuff that field with the "inactive" value.
I have included the existing code now that does work, but I am looking for guidance on how to combine this into one query if possible.
truncate table dbo.tbl_auto_audit_raw
insert into dbo.tbl_auto_audit_raw (plan_id,
patient_id,
serv_date_from,serv_date_to,
provider_id,
spec_code,
claim_number,
sex, dob, loc_code,
prim_diag, sec_diag, third_diag,fourth_diag, fifth_diag,sixth_diag,
proc_code,
modifier1,
--modifier2, not in sql yet
--modifier3, not in sql yet
--modifier4, not in sql yet
--serv_units,don't know what this is
--cap_flag, don't see this being populated in the old fox code
amount_billed,
amount_paid,
max_allowed,
active_flag)
select 'NHP1' as plan_id,
claim.casubno + claim.capersno + ' ' as patient_id,
convert(char(8), claim.CAPRIMDATE, 112) as serv_date_from,
convert(char(8), claim.CATHRUDATE, 112) as serv_date_to,
--provider zip needs to go here
claim.CAVENDOR as provider_id,
-- this "provider id" looks more like a vendor id, ask someone to make sure
left(claim.CAPCPSPEC, 5) as spec_code,
claim.caclaim + claim.cbline as claim_number,
claim.CASEX as sex,
convert(char(8),member.aadob, 112) as dob,
left(claim.caplace, 2) as loc_code,
claim.cadx1 as prim_diag,
claim.cadx2 as sec_diag,
claim.cadx3 as third_diag,
claim.cadx4 as fourth_diag,
claim.cadx5 as fifth_diag,
claim.cadx6 as sixth_diag,
left( claim.CBPROCCODE, 5) as proc_code,
claim.CBMODIFIER as modifier1,
--cbmodifier2
--cbmodifier3
--cbmodifier4
--serv_units --
--cap_flag,
left(cast(claim.CABILLED as char), 9) as amount_billed,
left(cast(claim.CBNET as char),9) as amount_paid,
left(cast(claim.CBALLOWED as char),9) as max_allowed,
'1' as active_flag
from fin.dbo.vw_MEV_OP as claim inner join nhpri_diam_ds01.dbo.tbl_CurrentMemberDimension as member
on claim.capersno = member.aapersno and claim.casubno = member.aasubno
where claim.carecdate between '01/01/2005' and '01/31/3005'
select * from dbo.tbl_auto_audit_raw
-- this gets the history for each member who has a claim in the first query
-- we attempted to get all the history and not to worry about dups, but
-- the filesize was too big to open so we had to scale it down and put in
-- xtra logic to only get each member's history one time
insert into dbo.tbl_auto_audit_raw (plan_id,
patient_id,
serv_date_from,serv_date_to,
provider_id,
spec_code,
claim_number,
sex, dob, loc_code,
prim_diag, sec_diag, third_diag,fourth_diag, fifth_diag,sixth_diag,
proc_code,
modifier1,
--modifier2, not in sql yet
--modifier3, not in sql yet
--modifier4, not in sql yet
--serv_units,don't know what this is
--cap_flag, don't see this being populated in the old fox code
amount_billed,
amount_paid,
max_allowed,
active_flag)
select 'NHP1' as plan_id,
claim.casubno + claim.capersno + ' ' as patient_id,
convert(char(8), claim.CAPRIMDATE, 112) as serv_date_from,
convert(char(8), claim.CATHRUDATE, 112) as serv_date_to,
--provider zip needs to go here
claim.CAVENDOR as provider_id,
-- this "provider id" looks more like a vendor id, ask someone to make sure
left(claim.CAPCPSPEC, 5) as spec_code,
claim.caclaim + claim.cbline as claim_number,
claim.CASEX as sex,
convert(char(8),member.aadob, 112) as dob,
left(claim.caplace, 2) as loc_code,
claim.cadx1 as prim_diag,
claim.cadx2 as sec_diag,
claim.cadx3 as third_diag,
claim.cadx4 as fourth_diag,
claim.cadx5 as fifth_diag,
claim.cadx6 as sixth_diag,
left( claim.CBPROCCODE, 5) as proc_code,
claim.CBMODIFIER as modifier1,
--cbmodifier2
--cbmodifier3
--cbmodifier4
--serv_units --
--cap_flag,
left(cast(claim.CABILLED as char), 9) as amount_billed,
left(cast(claim.CBNET as char),9) as amount_paid,
left(cast(claim.CBALLOWED as char),9) as max_allowed,
'' as active_flag
from fin.dbo.vw_MEV_OP as claim
inner join nhpri_diam_ds01.dbo.tbl_CurrentMemberDimension as member
on claim.capersno = member.aapersno and claim.casubno = member.aasubno
where claim.carecdate between '01/01/2004' and '12/31/3004' and
claim.casubno + claim.capersno in (select distinct patient_id from dbo.tbl_auto_audit_raw)