?
Solved

URGENT HELP! Single Record On A Stored Procedure

Posted on 2007-09-28
5
Medium Priority
?
177 Views
Last Modified: 2010-07-27
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
0
Comment
Question by:dbguy2626
  • 3
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19982113
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
 

Author Comment

by:dbguy2626
ID: 19982169
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
 

Author Comment

by:dbguy2626
ID: 19982175
hope that makes better sence!!
0
 

Author Comment

by:dbguy2626
ID: 19982184
So basically in the output there will never be more then one record for each INTERNALEXAMID
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19982228
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

850 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