Jeff S
asked on
Microsoft, SQL, 2005, SubQuery Help Needed
First and foremost, there will be portions of this SQL that will look odd to you .... this is handled dynamically, please try to not focus on it .... its not my issue.
What I need help with is the "ic.Listname" that I have commented out in the coding. I need to create a subquery I believe because when I comment out this field in my query, I get back the correct number of rows in my result set. When I un-comment it, I get a listing of every carrier the patient has and its causing un-necessary entrys. Any help is deeply appreciated!!
What I need help with is the "ic.Listname" that I have commented out in the coding. I need to create a subquery I believe because when I comment out this field in my query, I get back the correct number of rows in my result set. When I un-comment it, I get a listing of every carrier the patient has and its causing un-necessary entrys. Any help is deeply appreciated!!
SET NOCOUNT ON
CREATE TABLE #PatientTemp
(
[PatientProfileId] [int] NOT NULL,
[Prefix] [varchar](10) NULL,
[First] [varchar](30) NULL,
[Middle] [varchar](30) NULL,
[Last] [varchar](30) NULL,
[Suffix] [varchar](20) NULL,
[Created] [datetime] NOT NULL,
[SSN] [varchar](9) NULL,
[Birthdate] [datetime] NULL,
[Address1] [varchar](50) NULL,
[Address2] [varchar](50) NULL,
[City] [varchar](30) NULL,
[State] [varchar](3) NULL,
[Zip] [varchar](10) NULL,
[Phone1] [varchar](15) NULL,
[Sex] [VARCHAR](10) NULL
)
INSERT
#PatientTemp
SELECT
pp.PatientProfileId,
pp.Prefix,
pp.First,
pp.Middle,
pp.Last,
pp.Suffix,
CASE WHEN 1 = 1 THEN PP.Created
WHEN 1 = 2 THEN MIN(PV.Visit)
ELSE NULL
END AS Created,
pp.ssn,
pp.birthdate,
pp.Address1,
pp.Address2,
pp.City,
pp.State,
pp.Zip,
pp.Phone1,
pp.Sex
FROM
PatientVisit pv
INNER JOIN PatientProfile pp ON pp.PatientProfileId = pv.PatientProfileId
GROUP BY
pp.PatientProfileId,
pp.Prefix,
pp.First,
pp.Middle,
pp.Last,
pp.Suffix,
pp.Created,
pp.ssn,
pp.birthdate,
pp.Address1,
pp.Address2,
pp.City,
pp.State,
pp.Zip,
pp.Phone1,
pp.Sex
HAVING
( (( 1 = 1 )
AND ( PP.Created >= ISNULL('01/01/2008' , '1/1/1900')
AND PP.Created < DATEADD(d , 1 , ISNULL('10/02/2008' , '1/1/3000')) )
) )
OR ( ( 1 = 2 )
AND ( (MIN(PV.Visit) >= ISNULL('01/01/2008' , '1/1/1900')
AND ( MIN(PV.Visit) ) < DATEADD(d , 1 , ISNULL('10/02/2008' , '1/1/3000'))) ) )
ORDER BY
pp.First,
pp.Last
SELECT
#PatientTemp.PatientProfileId,
#PatientTemp.Last + ', ' + #PatientTemp.First AS [Member Name],
#PatientTemp.Created AS Created,
#PatientTemp.SSN,
ISNULL(CONVERT(VARCHAR(20) , #PatientTemp.birthdate , 101) , '') AS [Birthdate],
#PatientTemp.Address1,
#PatientTemp.Address2,
#PatientTemp.City,
#PatientTemp.State,
#PatientTemp.Zip,
#PatientTemp.Phone1,
#PatientTemp.Middle,
CASE WHEN #PatientTemp.Sex = 'M' THEN '1'
WHEN #PatientTemp.Sex = 'F' THEN '2'
ELSE ISNULL(#PatientTemp.Sex , '')
END AS [Sex],
-- ic.Listname,
ISNULL(pi.InsuredId , '') AS [InsuredID],
CASE WHEN pi.InsCardEffectiveDate IS NULL THEN ''
ELSE CONVERT(VARCHAR , pi.InsCardEffectiveDate , 101)
END AS [PrimaryInsEffDate],
DATEADD(year , 1 , pi.InsCardEffectiveDate) AS [PrimaryInsTermDate]
FROM
PatientVisit pv
INNER JOIN #PatientTemp ON pv.PatientProfileId = #PatientTemp.PatientProfileId
INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
LEFT JOIN dbo.DoctorFacility ref ON pv.ReferringDoctorId = ref.DoctorFacilityId
LEFT JOIN dbo.DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
LEFT JOIN PatientVisitResource pvr ON pvr.PatientVisitId = pv.PatientVisitId
LEFT JOIN PatientInsurance pi ON pv.PrimaryPICarrierId = pi.PatientInsuranceId
LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
AND pi.InsuranceCarriersId = ic.InsuranceCarriersId
WHERE ( ( NULL IS NOT NULL
AND pv.DoctorId IN ( NULL ) )
OR ( NULL IS NULL ) )
AND ( ( '6' IS NOT NULL
AND pv.FacilityId IN ( 6 ) )
OR ( '6' IS NULL ) )
AND --Filter on insurance carrier
( ( NULL IS NOT NULL
AND ic.InsuranceCarriersId IN ( NULL ) )
OR ( NULL IS NULL ) )
AND ( ( NULL IS NOT NULL
AND pvr.ResourceId IN ( NULL ) )
OR ( NULL IS NULL ) )
GROUP BY
#PatientTemp.Created,
dbo.FormatName(#PatientTemp.Prefix , #PatientTemp.First , #PatientTemp.Middle , #PatientTemp.Last , #PatientTemp.Suffix),
#PatientTemp.PatientProfileId,
#PatientTemp.Last,
#PatientTemp.First,
#PatientTemp.SSN,
#PatientTemp.Birthdate,
#PatientTemp.Address1,
#PatientTemp.Address2,
#PatientTemp.City,
#PatientTemp.State,
#PatientTemp.Zip,
#PatientTemp.Phone1,
#PatientTemp.Middle,
#PatientTemp.Sex,
-- ic.ListName,
pi.InsuredId,
pi.InsCardEffectiveDate
HAVING
( (( 1 = 1 )
AND ( #PatientTemp.Created >= ISNULL('01/01/2008' , '1/1/1900')
AND #PatientTemp.Created < DATEADD(d , 1 , ISNULL('10/02/2008' , '1/1/3000')) )
) )
OR ( ( 1 = 2 )
AND ( (MIN(pv.Visit) >= ISNULL('01/01/2008' , '1/1/1900')
AND ( MIN(pv.Visit) ) < DATEADD(d , 1 , ISNULL('10/02/2008' , '1/1/3000'))) ) )
ORDER BY
#PatientTemp.Last + ', ' + #PatientTemp.First,
#PatientTemp.PatientProfileId,
#PatientTemp.Created,
#PatientTemp.SSN,
#PatientTemp.Birthdate,
#PatientTemp.Address1,
#PatientTemp.Address2,
#PatientTemp.City,
#PatientTemp.State,
#PatientTemp.Zip,
#PatientTemp.Phone1,
#PatientTemp.Middle,
#PatientTemp.Sex,
-- ic.ListName,
pi.InsuredId,
pi.InsCardEffectiveDate,
[PrimaryInsTermDate]
DROP TABLE #PatientTemp
ASKER
Sorry, no. It made no difference on results.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What should my Groupby look like?
ASKER
chapmandew,
When I use the coding below, I get the exact results i need. I had to comment out the InsuredID, the Effective Date and calculated Term date. If you can help me get these back in this query, you will be my HERO. This is for a client thats needing this ASAP and words cant explain how much youve helped so far!!!
When I use the coding below, I get the exact results i need. I had to comment out the InsuredID, the Effective Date and calculated Term date. If you can help me get these back in this query, you will be my HERO. This is for a client thats needing this ASAP and words cant explain how much youve helped so far!!!
SET NOCOUNT ON
CREATE TABLE #PatientTemp
(
[PatientProfileId] [int] NOT NULL,
[Prefix] [varchar](10) NULL,
[First] [varchar](30) NULL,
[Middle] [varchar](30) NULL,
[Last] [varchar](30) NULL,
[Suffix] [varchar](20) NULL,
[Created] [datetime] NOT NULL,
[SSN] [varchar](9) NULL,
[Birthdate] [datetime] NULL,
[Address1] [varchar](50) NULL,
[Address2] [varchar](50) NULL,
[City] [varchar](30) NULL,
[State] [varchar](3) NULL,
[Zip] [varchar](10) NULL,
[Phone1] [varchar](15) NULL,
[Sex] [VARCHAR](10) NULL
)
INSERT
#PatientTemp
SELECT
pp.PatientProfileId,
pp.Prefix,
pp.First,
pp.Middle,
pp.Last,
pp.Suffix,
CASE WHEN 1 = 1 THEN PP.Created
WHEN 1 = 2 THEN MIN(PV.Visit)
ELSE NULL
END AS Created,
pp.ssn,
pp.birthdate,
pp.Address1,
pp.Address2,
pp.City,
pp.State,
pp.Zip,
pp.Phone1,
pp.Sex
FROM
PatientVisit pv
INNER JOIN PatientProfile pp ON pp.PatientProfileId = pv.PatientProfileId
GROUP BY
pp.PatientProfileId,
pp.Prefix,
pp.First,
pp.Middle,
pp.Last,
pp.Suffix,
pp.Created,
pp.ssn,
pp.birthdate,
pp.Address1,
pp.Address2,
pp.City,
pp.State,
pp.Zip,
pp.Phone1,
pp.Sex
HAVING
( (( 2 = 1 )
AND ( PP.Created >= ISNULL('01/01/2008' , '1/1/1900')
AND PP.Created < DATEADD(d , 1 , ISNULL('10/02/2008' , '1/1/3000')) )
) )
OR ( ( 2 = 2 )
AND ( (MIN(PV.Visit) >= ISNULL('01/01/2008' , '1/1/1900')
AND ( MIN(PV.Visit) ) < DATEADD(d , 1 , ISNULL('10/02/2008' , '1/1/3000'))) ) )
ORDER BY
pp.First,
pp.Last
SELECT
#PatientTemp.PatientProfileId,
#PatientTemp.Last + ', ' + #PatientTemp.First AS [Member Name],
#PatientTemp.Created AS Created,
#PatientTemp.SSN,
ISNULL(CONVERT(VARCHAR(20) , #PatientTemp.birthdate , 101) , '') AS [Birthdate],
#PatientTemp.Address1,
#PatientTemp.Address2,
#PatientTemp.City,
#PatientTemp.State,
#PatientTemp.Zip,
#PatientTemp.Phone1,
#PatientTemp.Middle,
CASE WHEN #PatientTemp.Sex = 'M' THEN '1'
WHEN #PatientTemp.Sex = 'F' THEN '2'
ELSE ISNULL(#PatientTemp.Sex , '')
END AS [Sex],
max(ic.Listname)
-- ISNULL(pi.InsuredId , '') AS [InsuredID]
-- CASE WHEN pi.InsCardEffectiveDate IS NULL THEN ''
-- ELSE CONVERT(VARCHAR , pi.InsCardEffectiveDate , 101)
-- END AS [PrimaryInsEffDate],
-- DATEADD(year , 1 , pi.InsCardEffectiveDate) AS [PrimaryInsTermDate]
FROM
PatientVisit pv
INNER JOIN #PatientTemp ON pv.PatientProfileId = #PatientTemp.PatientProfileId
INNER JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId
LEFT JOIN dbo.DoctorFacility ref ON pv.ReferringDoctorId = ref.DoctorFacilityId
LEFT JOIN dbo.DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
LEFT JOIN PatientVisitResource pvr ON pvr.PatientVisitId = pv.PatientVisitId
LEFT JOIN PatientInsurance pi ON pv.PrimaryPICarrierId = pi.PatientInsuranceId
LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
AND pi.InsuranceCarriersId = ic.InsuranceCarriersId
WHERE ( ( NULL IS NOT NULL
AND pv.DoctorId IN ( NULL ) )
OR ( NULL IS NULL ) )
AND ( ( '6' IS NOT NULL
AND pv.FacilityId IN ( 6 ) )
OR ( '6' IS NULL ) )
AND --Filter on insurance carrier
( ( NULL IS NOT NULL
AND ic.InsuranceCarriersId IN ( NULL ) )
OR ( NULL IS NULL ) )
AND ( ( NULL IS NOT NULL
AND pvr.ResourceId IN ( NULL ) )
OR ( NULL IS NULL ) )
GROUP BY
#PatientTemp.Created,
dbo.FormatName(#PatientTemp.Prefix , #PatientTemp.First , #PatientTemp.Middle , #PatientTemp.Last , #PatientTemp.Suffix),
#PatientTemp.PatientProfileId,
#PatientTemp.Last,
#PatientTemp.First,
#PatientTemp.SSN,
#PatientTemp.Birthdate,
#PatientTemp.Address1,
#PatientTemp.Address2,
#PatientTemp.City,
#PatientTemp.State,
#PatientTemp.Zip,
#PatientTemp.Phone1,
#PatientTemp.Middle,
#PatientTemp.Sex
-- ic.ListName,
-- pi.InsuredId
-- pi.InsCardEffectiveDate
HAVING
( (( 2 = 1 )
AND ( #PatientTemp.Created >= ISNULL('01/01/2008' , '1/1/1900')
AND #PatientTemp.Created < DATEADD(d , 1 , ISNULL('10/02/2008' , '1/1/3000')) )
) )
OR ( ( 2 = 2 )
AND ( (MIN(pv.Visit) >= ISNULL('01/01/2008' , '1/1/1900')
AND ( MIN(pv.Visit) ) < DATEADD(d , 1 , ISNULL('10/02/2008' , '1/1/3000'))) ) )
ORDER BY
#PatientTemp.Last + ', ' + #PatientTemp.First,
#PatientTemp.PatientProfileId,
#PatientTemp.Created,
#PatientTemp.SSN,
#PatientTemp.Birthdate,
#PatientTemp.Address1,
#PatientTemp.Address2,
#PatientTemp.City,
#PatientTemp.State,
#PatientTemp.Zip,
#PatientTemp.Phone1,
#PatientTemp.Middle,
#PatientTemp.Sex
-- ic.ListName,
-- pi.InsuredId
-- pi.InsCardEffectiveDate,
-- [PrimaryInsTermDate]
DROP TABLE #PatientTemp
MAX(ic.Listname),