SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL query help - CTE's and getting a count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JeffS23
Posting Yak Master

210 Posts

Posted - 01/18/2013 :  16:54:40  Show Profile  Reply with Quote
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.



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]

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/19/2013 :  02:43:15  Show Profile  Reply with Quote
can you post some sample data and explain what you want in below format?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000