How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask ATLPROG Your Own Question

ATLPROG
ATLPROG, Computer Software Engineer
Category: Programming
Satisfied Customers: 9151
Experience:  MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
44910485
Type Your Programming Question Here...
ATLPROG is online now
A new question is answered every 9 seconds

I need help with my SQL Query. Can someone help? I have to

Customer Question

I need help with my SQL Query. Can someone help? I have to write queries from a Data Dictionary. Thanks
Submitted: 8 months ago.
Category: Programming
Expert:  ATLPROG replied 8 months ago.
Hello can you provide more info
Customer: replied 8 months ago.
Hi ATLPROG, please see below
WINQI Input Data Dictionary
1. Variable Name - Key (KEY)
Description – Sequence number; unique case identifier
Format-String up to 20 Characters
Value Description – User defined unique numeric identifier for each discharge record
Comments – Not required so we need N/A
From table HINF_RPT.dbo.ODSClaimsextract2. Variable Name – Age (AGE)
Description – Age in years at admission
Format-Numeric
Value Description – Age in years
Comments – Need data element
From HINF_QNXT. dbo.Restatedenrollment table3. Variable Name – Age in Days (AGEDAY)
Description – Age in days at admission (coded only when the age in years is less than 1)
Format – Numeric 0-364 days
Value Description – Age in days
Comment – If this data element is missing (and age is 0), then generally an alternative specification applies
From HINF_QNXT.dbo.Restatedenrollment table4. Variable Name – Race (RACE)
Description – Race of patient
Format – Numeric
Value Description – 1 =White, 2=Black, 3=Hispanic, 4=Asian or Pacific Islander, 5=Native American, 6=other (This will need to be a case statement)
Comment – Data elements must be mapped to one of the listed values
From BIDW.dbo.MemberDim Table5. Variable Name – Sex (SEX)
Description – Gender of patient
Format-Numeric
Value description – 1=Male, 2=Female (Make this a case statement)
From dbo.Restatedenrollment table6. Variable Name – Primary Payer (PAY1)
Description – Expected primary payer, uniform
Format-Numeric
Value description – ‘Other’
From dbo.ODSClaimsextract7. Variable Name – VendorID
Description – Data source Hospital ID
Format – String up to 12 characters
Value description – Vendor ID8. Variable Name – Patient State/County Code
Description – State County of Patients
Format – Numeric two-digit State code followed by three-digit county code
From TP_Zipcodedim tableThis is for 2015 data and when you use product type it should state where product type = ‘Care-Caid’. The four tables are belowODSClaimextract table field names
MemberID LOBCode SourceDataKey ClaimLineID VendorID BeginServiceDateKey EndServiceDateKey HCFACode BeginServiceMonth ProductType PodRegion PODNamePrimary PayerRestatedenrollment table field names
CapDate BeginDate EndDate MemberID Age Gender LOBCode ProductType OperationalMarketMemberDim Field names Table
MemberID Gender EthnicityTP_Zipcodedim
MemberID ZipCode , County
Customer: replied 8 months ago.
Here's what I came to with my query that needs cleaning up because I get an error message when executing.
Here's the code I executedSelect Distinct ClaimLineID
,'N/A'[Key(KEY)]
,'0' DATEIFF(YEAR,Age,BeginDate)[Age(AGE)]
,'0' DATEDIFF(DAY,Age,BeginDate)/365.25 [AGE in Days (AGEDAY)]
case when e.Ethnicity ='1' then e.Ethnicity ='White'[Race (RACE)]
else e.Ethnicity ='2' then e.Ethnicity = 'Black'[Race (RACE)]
else e.Ethnicity ='3' then e.Ethnicity = 'Hispanic'[Race (RACE)]
else e.Ethnicity ='4' then e.Ethnicity = 'Asian or Pacific Islander'[Race (RACE)]
else e.Ethnicity ='5' then e.Ethnicity = 'Native American'[Race (RACE)]
else e.Ethnicity ='6' then e.Ethnicity = 'Others'[Race (RACE)]END
replace(replace(ltrim(rtrim(Ethnicity)),' ',''),'.','')[Race(RACE)]
From [hinf_qnxt].[dbo].[Member_Ethnicity] a
,case when m.Gender ='-----',then '' else m.Gender[Sex(SEX)] END
,replace(replace(ltrim(rtrim(Gender)),' ',''),'.','')[Sex(SEX)]
,'1' ='Male'
,'2' ='Female'
,'Other' [Primary Payer(PAY1)]
,'1' [Patient State/County Code (FIPS)]
,'VendorID' [HOSPITALID (HOSPID)]From HINF_QNXT.dbo.Restatedenrollment_SP a
left join [hinf_qnxt].[dbo].[Member_Ethnicity] b
on b.Ethnicity=b.Ethnicity
left join MemberDim m
on m.Gender=a.Gender
left join HINF_RPT.dbo.ODSClaimsExtract_SP c
on c.VendorID=c.VendorID
left join TP_ZipcodeDim t
on t.Zip=t.Zip
--and YEAR (NOT SURE WHAT TO PUT HERE) This should be for 2015 data)Where CompanyID = 'QMXHPQ0979' or LOBCode = 'UMISC0028041899'Here is my error message
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'YEAR'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'replace'.
Customer: replied 8 months ago.
Ultimately I may have no logic to my query but some and need help writing it so it can execute properly. Can you help with this?
Expert:  ATLPROG replied 8 months ago.
change ,'0' DATEIFF(YEAR,Age,BeginDate)[Age(AGE)] on line 3 to,'0' DATEDIFF(YEAR,Age,BeginDate)[Age(AGE)] let me know if you still get error after that
Customer: replied 8 months ago.
I need to log onto my other computer. I don't remember my password ***** so let me try. Please give me a few.
Expert:  ATLPROG replied 8 months ago.
ok
Customer: replied 8 months ago.
I am ready to connect if I can
Customer: replied 8 months ago.
That information was the same
Customer: replied 8 months ago.
Are you still there?
Expert:  ATLPROG replied 8 months ago.
can you post error message here
Customer: replied 8 months ago.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'YEAR'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'replace'.
Customer: replied 8 months ago.
I maybe joining too many times as well, not sure
Expert:  ATLPROG replied 8 months ago.
what do you have on line 3 now
Customer: replied 8 months ago.
Here's what I have but it is incorrectSelect Distinct ClaimLineID
,'N/A'[Key(KEY)]
,'0' DATEDIFF(YEAR,Age,BeginDate)[Age(AGE)]
,'0' DATEDIFF(DAY,Age,BeginDate)/365.25 [AGE in Days (AGEDAY)]
case when e.Ethnicity ='1' then e.Ethnicity ='White'[Race (RACE)]
else e.Ethnicity ='2' then e.Ethnicity = 'Black'[Race (RACE)]
else e.Ethnicity ='3' then e.Ethnicity = 'Hispanic'[Race (RACE)]
else e.Ethnicity ='4' then e.Ethnicity = 'Asian or Pacific Islander'[Race (RACE)]
else e.Ethnicity ='5' then e.Ethnicity = 'Native American'[Race (RACE)]
else e.Ethnicity ='6' then e.Ethnicity = 'Others'[Race (RACE)]END
replace(replace(ltrim(rtrim(Ethnicity)),' ',''),'.','')[Race(RACE)]
From [hinf_qnxt].[dbo].[Member_Ethnicity] a
,case when m.Gender ='-----',then '' else m.Gender[Sex(SEX)] END
,replace(replace(ltrim(rtrim(Gender)),' ',''),'.','')[Sex(SEX)]
,'1' ='Male'
,'2' ='Female'
,'Other' [Primary Payer(PAY1)]
,'1' [Patient State/County Code (FIPS)]
,'VendorID' [HOSPITALID (HOSPID)]
From HINF_QNXT.dbo.Restatedenrollment_SP a
left join [hinf_qnxt].[dbo].[Member_Ethnicity] b
on b.Ethnicity=b.Ethnicity
left join MemberDim m
on m.Gender=a.Gender
left join HINF_RPT.dbo.ODSClaimsExtract_SP c
on c.VendorID=c.VendorID
left join TP_ZipcodeDim t
on t.Zip=t.Zip
--and YEAR (NOT SURE WHAT TO PUT HERE) This should be for 2015 data)Where CompanyID = 'QMXHPQ0979' or LOBCode = 'UMISC0028041899'
Customer: replied 8 months ago.
My colleauge was assigned a task for the same project but have different requirements and her executed nicely where I tried to follow the same logic although I have different requirements. This is her query.select distinct ClaimID
,'NA' [APR-DRG Severity of Illness (SEVERITY)]
,'NA' [APR-DRG Risk of Mortality (RISKMORT)]
,'NA' [XAPR-DRG Risk of Mortality (XRISKMORT)]
,case when s.DRGCode = '------' then '' else s.DRGCode end [Diagnosis Related Group (DRG)]
,'32' [Diagnosis-Related Group Version (DRGVER)]
,isnull(case when d.MDCCode = '---' then 'NULL' else d.MDCCode end, '') [Major Diagnostic Category (MDC)]
,replace(replace(ltrim(rtrim(dxcode1)),' ',''),'.','') [Principal Diagnosis (DX1)]
,replace(replace(ltrim(rtrim(dxcode2)),' ',''),'.','') [Diagnosis Code 2 (DX2)]
,replace(replace(ltrim(rtrim(dxcode3)),' ',''),'.','') [Diagnosis Code 2 (DX3)]from [HINF_RPT].[dbo].[ODSClaimsExtract_SP] s
left join TP_DRG d
on s.DRGCode = d.DRGCode
and YEAR(BeginServiceDateKey) = DRG_Yearwhere (CompanyID = 'QMXHPQ1003' or LOBCode = 'C54581391')
and BeginServiceDateKey >= '3/01/2015'order by claimID
Expert:  ATLPROG replied 8 months ago.
sorry but i am out of ideas. I will open this for all experts. Best wishes
Customer: replied 8 months ago.
Ok thanks
Customer: replied 8 months ago.
Hello, I was able to figure out the query myself. However I have another query that I can't get right. I have a number of rows returning triple the rows. Can you help with this?
Expert:  ATLPROG replied 8 months ago.
i can try.
Expert:  ATLPROG replied 8 months ago.
please provide more info
Customer: replied 8 months ago.
I am getting duplicates on my executionIF OBJECT_ID('TEMPDB..#affil') IS NOT NULL
drop table #affil
select distinct av.provid,av.affiliationid,av.affiltype,av.termdate,
av.affilphyaddr1,av.affilphyaddr2,av.affilphycity,av.affilphystate,
av.affilphycounty,av.affilphyzip,av.affilphone,aa.thevalue OfficeHrs
into #affil
from qnxt..affiliateview av
left join qnxt..affiliationattribute aa
on av.affiliationid = aa.affiliationid
and aa.attributeid in ('C44781439','C41559976')
and aa.termdate > getdate()
where av.affiltype = 'service' and av.termdate > getdate()
and av.status = 'active'
alter table #affil
alter column provid varchar(25)
--select * from #affil
IF OBJECT_ID('TEMPDB..#planaffil') IS NOT NULL
drop table #planaffil
select distinct affiliationid,programid,termdate,newassigns
into #planaffil
from qnxt..planaffilinfo
where termdate > getdate() and
programid in ('QMXHPQ0979','QMXHPQ0982')
alter table #planaffil
alter column programid varchar(25)
--select * from #planaffilIF OBJECT_ID('TEMPDB..#lan') IS NOT NULL
drop table #lanselect distinct ProviderID,ProviderLanguageKey,LanguageDesc
into #lan
from bidw..providerlanguagesxref lan
where sourcedatakey = '50'alter table #lan
alter column providerid varchar (25)
--- select * from #lan
--------GET Provider Contacts----
IF OBJECT_ID ('TEMPDB..#P') IS NOT NULL
drop table #P
Select distinct ProviderID,PhoneNumber
into #P
from bidw..ProviderPhoneDim P
where sourcedatakey = '50'
alter table #P
alter column providerid varchar (25)
-------------------------------------
IF OBJECT_ID('TEMPDB..#t1') IS NOT NULL
drop table #t1
select a.ProviderID,VendorID GroupID,a.LobCode,PCPFlag PCP,ParFlag Par_NonPar,EffDateKey
into #t1
from (
select distinct ProviderID,LobCode,MAX(EffectiveDateKey) EffDateKey,
MAX(enddatekey) enddatekey
from ProviderContractDim
where SourceDataKey = '50' and
LobCode in ('UMISC0028042858','UMISC0028041899') and
Active = '1' and
EndDateKey >= CONVERT(varchar(8), getDate(), 112) and
ParFlag = 'p'
group by ProviderID,LobCode) a
left join ProviderContractDim p
on a.ProviderID = p.ProviderID
and a.LobCode = p.LobCode
and a.EffDateKey = p.EffectiveDateKey
and a.enddatekey = p.EndDateKey
and p.ParFlag = 'p'
and p.Active = '1'
IF OBJECT_ID('TEMPDB..#t2') IS NOT NULL
drop table #t2
select x.ProviderID,x.LobCode,x.Programid,x.SDA,x.LastName,x.FirstName,
x.MiddleName,x.Title,x.ProviderKey,x.EffDateKey,x.PCP,x.Par_NonPar,
d.SpecialtyTypeDesc Specialty,x.NPI,x.TaxID,x.GroupID,p.FullName GroupName,
p.NPID GroupNPI,p.FIDN GroupTaxID,p.HandicapAccess,p.Gender,p.NewPatientFlag, pa.thevalue as AcceptExist,
case when pa.thevalue = 'YES' then 'E' when newpatientflag ='Y' then 'A' when newpatientflag ='N' then 'C' else '' end as PanelStatus
into #t2
from (
select distinct a.ProviderID,a.LobCode,lob.companycode Programid,max(a.GroupID) GroupID,
case when (a.LobCode = 'UMISC0028042858') then 'ICP'
when (a.LobCode = 'UMISC0028041899') then 'MMP'
else '' end SDA,p.LastName,p.FirstName,p.MiddleName,p.Title,p.ProviderKey,a.EffDateKey,
a.PCP,a.Par_NonPar,p.NPID NPI,p.FIDN TaxID
from #t1 a
inner join ProviderDim p
on a.ProviderID = p.ProviderID
and p.SourceDataKey = '50'
and p.Active =
Customer: replied 8 months ago.
Please look at this one--- disregard the last one.IF OBJECT_ID('TEMPDB..#affil') IS NOT NULL
drop table #affil
select distinct av.provid,av.affiliationid,av.affiltype,av.termdate,
av.affilphyaddr1,av.affilphyaddr2,av.affilphycity,av.affilphystate,
av.affilphycounty,av.affilphyzip,av.affilphone,aa.thevalue OfficeHrs
into #affil
from qnxt..affiliateview av
left join qnxt..affiliationattribute aa
on av.affiliationid = aa.affiliationid
and aa.attributeid in ('C44781439','C41559976')
and aa.termdate > getdate()
where av.affiltype = 'service' and av.termdate > getdate()
and av.status = 'active'
alter table #affil
alter column provid varchar(25)
--select * from #affil
IF OBJECT_ID('TEMPDB..#planaffil') IS NOT NULL
drop table #planaffil
select distinct affiliationid,programid,termdate,newassigns
into #planaffil
from qnxt..planaffilinfo
where termdate > getdate() and
programid in ('QMXHPQ0979','QMXHPQ0982')
alter table #planaffil
alter column programid varchar(25)
--select * from #planaffilIF OBJECT_ID('TEMPDB..#lan') IS NOT NULL
drop table #lanselect distinct ProviderID,ProviderLanguageKey,LanguageDesc
into #lan
from bidw..providerlanguagesxref lan
where sourcedatakey = '50'alter table #lan
alter column providerid varchar (25)
--- select * from #lan
--------GET Provider Contacts----
IF OBJECT_ID ('TEMPDB..#P') IS NOT NULL
drop table #P
Select distinct ProviderID,PhoneNumber
into #P
from bidw..ProviderPhoneDim P
where sourcedatakey = '50'
alter table #P
alter column providerid varchar (25)
-------------------------------------
IF OBJECT_ID('TEMPDB..#t1') IS NOT NULL
drop table #t1
select a.ProviderID,VendorID GroupID,a.LobCode,PCPFlag PCP,ParFlag Par_NonPar,EffDateKey
into #t1
from (
select distinct ProviderID,LobCode,MAX(EffectiveDateKey) EffDateKey,
MAX(enddatekey) enddatekey
from ProviderContractDim
where SourceDataKey = '50' and
LobCode in ('UMISC0028042858','UMISC0028041899') and
Active = '1' and
EndDateKey >= CONVERT(varchar(8), getDate(), 112) and
ParFlag = 'p'
group by ProviderID,LobCode) a
left join ProviderContractDim p
on a.ProviderID = p.ProviderID
and a.LobCode = p.LobCode
and a.EffDateKey = p.EffectiveDateKey
and a.enddatekey = p.EndDateKey
and p.ParFlag = 'p'
and p.Active = '1'
IF OBJECT_ID('TEMPDB..#t2') IS NOT NULL
drop table #t2
select x.ProviderID,x.LobCode,x.Programid,x.SDA,x.LastName,x.FirstName,
x.MiddleName,x.Title,x.ProviderKey,x.EffDateKey,x.PCP,x.Par_NonPar,
d.SpecialtyTypeDesc Specialty,x.NPI,x.TaxID,x.GroupID,p.FullName GroupName,
p.NPID GroupNPI,p.FIDN GroupTaxID,p.HandicapAccess,p.Gender,p.NewPatientFlag, pa.thevalue as AcceptExist,
case when pa.thevalue = 'YES' then 'E' when newpatientflag ='Y' then 'A' when newpatientflag ='N' then 'C' else '' end as PanelStatus
into #t2
from (
select distinct a.ProviderID,a.LobCode,lob.companycode Programid,max(a.GroupID) GroupID,
case when (a.LobCode = 'UMISC0028042858') then 'ICP'
when (a.LobCode = 'UMISC0028041899') then 'MMP'
else '' end SDA,p.LastName,p.FirstName,p.MiddleName,p.Title,p.ProviderKey,a.EffDateKey,
a.PCP,a.Par_NonPar,p.NPID NPI,p.FIDN TaxID
from #t1 a
inner join ProviderDim p
on a.ProviderID = p.ProviderID
and p.SourceDataKey = '50'
an