troubleshooting Question

SQL 2005 - Report Query Assistance needed.

Avatar of Jeff S
Jeff SFlag for United States of America asked on
Microsoft SQL Server 2005
2 Comments2 Solutions226 ViewsLast Modified:
First and foremost, my SQL is handled dynamically by the server, therefore, coding in my WHERE clauses will look odd to you. Trust me, I know it looks odd, but it is not the issue.

With that being said, this is what I need some assistance with.

I would like to break out the a date field into 3 sections:  Month, Days then Year into 3 distinct fields.

Right now I have a column returned ImmunDate. It is currently reported back with a date for example: 2009-12-16 00:00:00.000

I would like a field ImmunMonth and return the '12', a field ImmunDays and return '16' and then finally a field ImmunYear and return '2009'. Any help is appreciated.
/*Immunization Report*/

SET NOCOUNT ON

SELECT
        	cusvImmunPatExternal.*,
        	cusvImmunPatExternal.Last + ', ' + cusvImmunPatExternal.First + CASE WHEN cusvImmunPatExternal.middle IS NULL THEN +''
        	                           ELSE ' ' + cusvImmunPatExternal.middle
        	                      END AS Name,
        	pp.Address1, 
        	pp.Address2,
        	pp.City,
        	pp.State,
        	pp.Zip,
        	dbo.FormatName(g.Prefix , g.First , g.Middle , g.Last , g.Suffix) AS GuarantorName,
        	dbo.cusCalcAgeYearsMonths(AgeYears , AgeMonths) AS AgeAtImmun

FROM
        	cusvImmunPatExternal
        	INNER JOIN PatientProfile pp ON cusvImmunPatExternal.PatientProfileID = pp.PatientProfileID
        	LEFT JOIN Guarantor g ON pp.GuarantorID = g.GuarantorID

WHERE  
	--Filter on patient
	(
	(NULL IS NOT NULL AND pp.PatientProfileID IN (NULL)) OR
	(NULL IS NULL)
	)

UNION

SELECT
        	cusvImmunPatVisits.*,
        	cusvImmunPatVisits.Last + ', ' + cusvImmunPatVisits.First + CASE WHEN cusvImmunPatVisits.middle IS NULL THEN +''
        	                           ELSE ' ' + cusvImmunPatVisits.middle
        	                      END AS Name,
        	pp.Address1, 
        	pp.Address2,
        	pp.City,
        	pp.State,
        	pp.Zip, 
        	dbo.FormatName(g.Prefix , g.First , g.Middle , g.Last , g.Suffix) AS GuarantorName,                 
        	dbo.cusCalcAgeYearsMonths(AgeYears , AgeMonths) AS AgeAtImmun

FROM
        	cusvImmunPatVisits
	INNER JOIN PatientProfile pp ON cusvImmunPatVisits.PatientProfileID = pp.PatientProfileID
	LEFT JOIN Guarantor g ON pp.GuarantorID = g.GuarantorID

WHERE
	--Filter on patient
	(
	(NULL IS NOT NULL AND pp.PatientProfileID IN (NULL)) OR
	(NULL IS NULL)
	)

ORDER BY 
	[Last], 
	[First], 
	Middle, 
	ListOrder, 
	ImmunCode
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros