URGENT HELP! Single Record On A Stored Procedure

Hello again Experts, I know I am the new thorn in everyones behind but I have a quick question that I'm hoping someone can answer very soon.  I promise this will be the last request on this one!! :)

The below procedure pulls up the proper information but what I am looking for is one record for every InternalExamID that shows the same information that I am now getting in three different records with the below code.  For example every InternalExamID gives me three records that show the ChangeDTTM for 'DICTATE', 'TRANSCRIBE','AUTHENT'.  I need only one record that has those three fields included as opposed to three seperate records. Does that make sence?  Please help!  You guys are great.


CREATE PROCEDURE [dbo].[rptMinimumAuditDiagnosticResultDTTMs]
AS
declare @FirstOfThisMonth datetime
    set @FirstOfThisMonth = dateadd( month, datediff( month, 0, getdate() ), 0 )

    SELECT v.AuditEventType,v.InternalExamID,MIN(ChangeDTTM) as MinimumDate
    FROM dbo.vusrAuditDiagnosticReport v
    WHERE
        ((v.AuditEventType = 'DICTATE') OR (v.AuditEventType = 'TRANSCRIBE') OR (v.AuditEventType = 'AUTHENT'))
        and v.ChangeDTTM >= dateadd( month, -1, @FirstOfThisMonth )
        and v.ChangeDTTM < @FirstOfThisMonth
    GROUP BY v.InternalExamID,v.AuditEventType

GO
dbguy2626Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
here we go:

ALTER PROCEDURE [dbo].[rptMinimumAuditDiagnosticResultDTTMs]
AS
declare @FirstOfThisMonth datetime
    set @FirstOfThisMonth = dateadd( month, datediff( month, 0, getdate() ), 0 )

SELECT INTERNALEXAMID
, MAX(case when AUDITEVENTTYPE = 'DICTATE' THEN MINIUMDATE  ELSE NULL END) as [DICTATE-MINIUMDATE]
, MAX(case when AUDITEVENTTYPE = 'TRANSCRIBE' THEN MINIUMDATE  ELSE NULL END) as [TRANSCRIBE-MINIUMDATE]
, MAX(case when AUDITEVENTTYPE = 'AUTHENT' THEN MINIUMDATE  ELSE NULL END) as [AUTHENT-MINIUMDATE]
FROM dbo.vusrAuditDiagnosticReport v
WHERE v.AuditEventType IN (  'DICTATE', 'TRANSCRIBE', 'AUTHENT' )
        and v.ChangeDTTM >= dateadd( month, -1, @FirstOfThisMonth )
        and v.ChangeDTTM < @FirstOfThisMonth
    GROUP BY v.InternalExamID

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you post the data you get now, and the one you would get instead.
will make it much clearer.

and yes, it can make "sense".
0
 
dbguy2626Author Commented:
thanks Angel,
What I'm Getting Now 3 records
FIELD NAMES:AUDITEVENTTYPE, INTERNALEXAMID,MINIUMDATE
DICTATE         3248690      2007-08-06 14:56:00.000
TRANSCRIBE 3248690      2007-08-19 20:09:00.000
AUTHENT         3248690      2007-08-10 10:31:00.000

WHAT I WANT TO GET 1 RECORD
FIELD NAMES: INTERNALEXAMID,DICTATE-MINIUMDATE,TRANSCRIBE MINIUMDATE, AUTHEN-MINIUMDATE

So in the data that I am getting now you will notice that the INERNALEXAMID is the same in three records, I want those three records to be just one record with all information





0
 
dbguy2626Author Commented:
hope that makes better sence!!
0
 
dbguy2626Author Commented:
So basically in the output there will never be more then one record for each INTERNALEXAMID
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.