|
JeffS23
Posting Yak Master
212 Posts |
 Posted - 2013-01-18 : 16:54:40
|
| I need help on my below listed query. I was able to get a 1 count on those patients that had a subsequent visit; however I can not quite figure out how to get a count on those where the patient did not have a subsequent visit. Let me back track some - my client has requested a count of unique patients that came in for an initial visit, a unique count of patients that came in for a subsequent visit (must have an initial visit) and counts on patients that had an initial with subsequent and ones that only had the initial visit. Right now, I am having issues on the ones where they did not have a subsequent visit. I am using crystal reports to group the data and sum it all up, i just need it to put a 0 or a 1 depending on if they returned or not. [CODE]SET NOCOUNT ON DECLARE @InitialStart DATETIME, @InitialEnd DATETIME, @StartDate DATETIME, @EndDate DATETIME, @groupby1 VARCHAR(60), @groupby2 VARCHAR(60) SET @InitialStart = ISNULL('01/01/2012','1/1/1900')SET @InitialEnd = ISNULL('12/31/2012','1/1/3000') SET @StartDate = ISNULL('01/01/2013','1/1/1900')SET @EndDate = ISNULL('01/17/2013','1/1/3000') SET @groupby1 = CONVERT(VARCHAR(60),'(None)')SET @groupby2 = CONVERT(VARCHAR(60),'(None)');WITH INITIAL AS (SELECT ROW_NUMBER() over (partition by pp.patientprofileId order by visit) as 'rownum', 'Initial' AS VisitType, 1 AS Initial, 0 AS Subsequent, ISNULL(CONVERT(VARCHAR(20) , pv.Visit , 101) , '') AS Visit , pv.PatientVisitId, pv.TicketNumber , dbo.cusfn_GetCPTCode(pv.PatientVisitId) AS CPTCodes , dbo.cusfn_GetDXCode(pv.PatientVisitId) AS DXCodes , pp.PatientProfileId, dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS [Patient Name] , pp.PatientId , ISNULL(CONVERT(VARCHAR(20) , pp.birthdate , 101) , '') AS PatientDOB , FLOOR(DATEDIFF(DAY , pp.birthdate , pv.Visit) / 365.25) AS [Patient Age] , ISNULL(pp.sex , '') AS Gender , ISNULL(race.Description , 'Unknown') AS Race , ISNULL(eth.Description , 'Unknown') AS Ethnicity , fac.ListName AS Facility , doc.ListName AS Doctor , ISNULL(ic.ListName , 'Self Pay') AS [Insurance Carrier] , ISNULL(pt.Description , 'No Policy Type') AS PolicyType, CASE WHEN 0 = 0 THEN 'No Detail' ELSE 'Detail' END AS ShowDetail, CASE @groupby1 WHEN 'Facility' THEN fac.ListName WHEN 'Provider' THEN doc.ListName WHEN 'Policy Type' THEN ISNULL(pt.Description, 'No Policy Type') WHEN 'Insurance Carrier' THEN ISNULL(ic.ListName, 'Self Pay') WHEN 'Race' THEN ISNULL(race.Description,'Unknown') WHEN 'Ethnicity' THEN ISNULL(eth.Description , 'Unknown') ELSE 'None' END AS Group1, CASE @groupby2 WHEN 'Facility' THEN fac.ListName WHEN 'Provider' THEN doc.ListName WHEN 'Policy Type' THEN ISNULL(pt.Description, 'No Policy Type') WHEN 'Insurance Carrier' THEN ISNULL(ic.ListName, 'Self Pay') WHEN 'Race' THEN ISNULL(race.Description,'Unknown') WHEN 'Ethnicity' THEN ISNULL(eth.Description , 'Unknown') ELSE 'None' END AS Group2 FROM PatientVisit pv INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId LEFT JOIN MedLists pt ON ic.PolicyTypeMID = pt.MedListsID LEFT JOIN Medlists race ON pp.RaceMId = race.MedListsId LEFT JOIN MedLists eth ON pp.EthnicityMId = eth.MedListsId LEFT JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId LEFT OUTER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId AND pvp.PatientVisitDiags1 = pvd.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd1 ON pv.PatientVisitId = pvd1.PatientVisitId AND pvp.PatientVisitDiags2 = pvd1.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd2 ON pv.PatientVisitId = pvd2.PatientVisitId AND pvp.PatientVisitDiags3 = pvd2.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd3 ON pv.PatientVisitId = pvd3.PatientVisitId AND pvp.PatientVisitDiags4 = pvd3.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd4 ON pv.PatientVisitId = pvd4.PatientVisitId AND pvp.PatientVisitDiags5 = pvd4.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd5 ON pv.PatientVisitId = pvd5.PatientVisitId AND pvp.PatientVisitDiags6 = pvd5.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd6 ON pv.PatientVisitId = pvd6.PatientVisitId AND pvp.PatientVisitDiags7 = pvd6.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd7 ON pv.PatientVisitId = pvd7.PatientVisitId AND pvp.PatientVisitDiags8 = pvd7.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd8 ON pv.PatientVisitId = pvd8.PatientVisitId AND pvp.PatientVisitDiags9 = pvd8.ListOrder WHERE ISNULL(pvp.voided , 0) = 0 AND -- Filter on Dates ( ('1' = '1' AND pvp.DateOfServiceFrom >= @InitialStart AND pvp.DateOfServiceFrom < @InitialEnd + 1) OR ('1' = '2' AND pvp.dateofentry >= @InitialStart AND pvp.dateofentry < @InitialEnd + 1) ) AND pvp.ProceduresId IN (54) ), SUBSEQUENT AS ( SELECT ROW_NUMBER() over (partition by pp.patientprofileId order by pv.visit) as 'rownum', 'Subsequent' AS VisitType, 0 AS Initial, 1 AS Subsequent, ISNULL(CONVERT(VARCHAR(20) , pv.Visit , 101) , '') AS Visit , pv.PatientVisitId, pv.TicketNumber , dbo.cusfn_GetCPTCode(pv.PatientVisitId) AS CPTCodes , dbo.cusfn_GetDXCode(pv.PatientVisitId) AS DXCodes , pp.PatientProfileId, dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS [Patient Name] , pp.PatientId , ISNULL(CONVERT(VARCHAR(20) , pp.birthdate , 101) , '') AS PatientDOB , FLOOR(DATEDIFF(DAY , pp.birthdate , pv.Visit) / 365.25) AS [Patient Age] , ISNULL(pp.sex , '') AS Gender , ISNULL(race.Description , 'Unknown') AS Race , ISNULL(eth.Description , 'Unknown') AS Ethnicity , fac.ListName AS Facility , doc.ListName AS Doctor , ISNULL(ic.ListName , 'Self Pay') AS [Insurance Carrier] , ISNULL(pt.Description , 'No Policy Type') AS PolicyType, CASE WHEN 1 = 0 THEN 'No Detail' ELSE 'Detail' END AS ShowDetail, CASE @groupby1 WHEN 'Facility' THEN fac.ListName WHEN 'Provider' THEN doc.ListName WHEN 'Policy Type' THEN ISNULL(pt.Description, 'No Policy Type') WHEN 'Insurance Carrier' THEN ISNULL(ic.ListName, 'Self Pay') WHEN 'Race' THEN ISNULL(race.Description,'Unknown') WHEN 'Ethnicity' THEN ISNULL(eth.Description , 'Unknown') ELSE 'None' END AS Group1, CASE @groupby2 WHEN 'Facility' THEN fac.ListName WHEN 'Provider' THEN doc.ListName WHEN 'Policy Type' THEN ISNULL(pt.Description, 'No Policy Type') WHEN 'Insurance Carrier' THEN ISNULL(ic.ListName, 'Self Pay') WHEN 'Race' THEN ISNULL(race.Description,'Unknown') WHEN 'Ethnicity' THEN ISNULL(eth.Description , 'Unknown') ELSE 'None' END AS Group2 FROM PatientVisit pv INNER JOIN INITIAL int ON pv.PatientProfileId = int.PatientProfileId INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId INNER JOIN DoctorFacility doc ON pv.DoctorId = doc.DoctorFacilityId LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId LEFT JOIN MedLists pt ON ic.PolicyTypeMID = pt.MedListsID LEFT JOIN Medlists race ON pp.RaceMId = race.MedListsId LEFT JOIN MedLists eth ON pp.EthnicityMId = eth.MedListsId LEFT JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId LEFT OUTER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId AND pvp.PatientVisitDiags1 = pvd.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd1 ON pv.PatientVisitId = pvd1.PatientVisitId AND pvp.PatientVisitDiags2 = pvd1.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd2 ON pv.PatientVisitId = pvd2.PatientVisitId AND pvp.PatientVisitDiags3 = pvd2.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd3 ON pv.PatientVisitId = pvd3.PatientVisitId AND pvp.PatientVisitDiags4 = pvd3.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd4 ON pv.PatientVisitId = pvd4.PatientVisitId AND pvp.PatientVisitDiags5 = pvd4.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd5 ON pv.PatientVisitId = pvd5.PatientVisitId AND pvp.PatientVisitDiags6 = pvd5.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd6 ON pv.PatientVisitId = pvd6.PatientVisitId AND pvp.PatientVisitDiags7 = pvd6.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd7 ON pv.PatientVisitId = pvd7.PatientVisitId AND pvp.PatientVisitDiags8 = pvd7.ListOrder LEFT OUTER JOIN PatientVisitDiags pvd8 ON pv.PatientVisitId = pvd8.PatientVisitId AND pvp.PatientVisitDiags9 = pvd8.ListOrder WHERE ISNULL(pvp.voided , 0) = 0 AND -- Filter on Dates ( ('1' = '1' AND pvp.DateOfServiceFrom >= @StartDate AND pvp.DateOfServiceFrom < @EndDate + 1) OR ('1' = '2' AND pvp.dateofentry >= @StartDate AND pvp.dateofentry < @EndDate + 1) ) AND pvp.ProceduresId IN (57) ), CTE AS ( SELECT rownum, VisitType, Initial, Subsequent, isnull(cnt,0) AS HasSubsequentVisit, 0 AS NoSubsequentVisit, Visit, PatientVisitId, TicketNumber, CPTCodes, DXCodes, i.PatientProfileId, [Patient Name], PatientId, PatientDOB, [Patient Age], Gender, Race, Ethnicity, Facility, Doctor, [Insurance Carrier], PolicyType, ShowDetail, Group1, Group2 FROM INITIAL i LEFT OUTER JOIN (SELECT 1 as cnt, patientprofileid from SUBSEQUENT) s on i.PatientProfileId = s.patientprofileid UNION SELECT rownum, VisitType, Initial, Subsequent, 0 AS HasSubsequentVisit, 0 AS NoSubsequentVisit, Visit, PatientVisitId, TicketNumber, CPTCodes, DXCodes, PatientProfileId, [Patient Name], PatientId, PatientDOB, [Patient Age], Gender, Race, Ethnicity, Facility, Doctor, [Insurance Carrier], PolicyType, ShowDetail, Group1, Group2 FROM SUBSEQUENT )SELECT * FROM CTE WHERE rownum = 1 ORDER BY VisitType, [Patient Name] [/CODE] |
|
SQL query help - CTE's and getting a count