Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL 2005 - Report Query Assistance needed.

Posted on 2009-12-17
2
Medium Priority
?
198 Views
Last Modified: 2012-05-08
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

Open in new window

0
Comment
Question by:Jeff S
2 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 1000 total points
ID: 26078060
>> 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 am not able to view that one in the code attached and hence request you to include this as required..

DATEPART(mm, ImmunDate) as Months,
DATEPART(dd, ImmunDate) as Days,
DATEPART(yy, ImmunDate) as Year

If this is not what you require, kindly explain me in more detail..
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1000 total points
ID: 26086225
Yep rrjegan17 has pretty much nailed it....

DATEPART will return the numeric value of the date part being referenced (e.g. 1 for month January)
DATENAME will return the description of the date part being referenced (e.g. January for month 01)

You can also use DAY(ImmunDate) as immunDays, MONTH(ImmunDate) as immunMonth, YEAR(ImmunDate) as immunYear

The other thing that you can do is return the date in different formats such that other programs can still recognise them as date. For that you can use the CONVERT function with a style code e.g. CONVERT(varchar,immunDate,112) will return a date in the format yyyymmdd - which is good for sorting...

So, your code should look something like (and should use the table alias to prefix):
/*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 ,
                DAY(ImmunDate) as immunDays, MONTH(ImmunDate) as immunMonth, YEAR(ImmunDate) as immunYear
 
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 
                DAY(ImmunDate) as immunDays, MONTH(ImmunDate) as immunMonth, YEAR(ImmunDate) as immunYear
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

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question