STORED PROCEDURE PLEASE HELP ME!!!

Hey Experts!! PLEASE PLEASE PLEASE HELP!!!    SOMEONE? ANYONE?!!  I need help writing a stored procedure that will be implemented in a Microsoft SQL DB and used within Crystal Reports.  Your help would be greatly greatly apprecited!!  I am going to give the CRYSTAL CODE and please help me transfer it to a stored procedure.  By the way I am gathering all the data from VIEWS and not tables. The name of the database is IDXrad

HERE IS THE DATA I WANT TO RETURN. IT WILL BE 3 DATA/TIME FIELDS AND AN ID FIELD SO I HAVE SOMETHING TO REFERENCE THE DATA TO. ALSO I NEED THE DATE TIME FIELD BE FORMATTED THIS WAY YYYY,MM,DD,HH,MM,SS.  PLEASE NOTE THAT THERE CAN BE MULTIPLE RETURNS BELOW, I AM LOOKING FOR THE MINIUM DATE TIME FIELD FOR THE LAST FULL MONTH

1.
View: vusrAuditDiagnosticReport
Field: {ChangeDTTM}
Where  {vusrAuditDiagnosticReport.AuditEventType} = 'DICTATE'
RETURN DATE TIME FIELD: Minimum({vusrAuditDiagnosticReport.ChangeDTTM})
ALSO NEED THE InternalExamID FIELD ON THE RETURNED ROW WHICH IS LOCATED IN THE SAME VIEW

2.
View: vusrAuditDiagnosticReport
Field: {ChangeDTTM}
Where  {vusrAuditDiagnosticReport.AuditEventType} = 'TRANSCRIBE'
RETURN THE DATE TIME FIELD: Minimum({vusrAuditDiagnosticReport.ChangeDTTM})
ALSO NEED THE InternalExamID FIELD ON THE RETURNED ROW WHICH IS LOCATED IN THE SAME VIEW
3.
View: vusrAuditDiagnosticReport
Field: {ChangeDTTM}
Where  {vusrAuditDiagnosticReport.AuditEventType} = 'AUTHENT'
RETURN THE DATE TIME FIELD: Minimum({vusrAuditDiagnosticReport.ChangeDTTM})
ALSO NEED THE InternalExamID FIELD ON THE RETURNED ROW WHICH IS LOCATED IN THE SAME VIEW
dbguy2626Asked:
Who is Participating?
 
adraughnConnect With a Mentor Commented:
you neeed something like this:

CREATE PROCEDURE AS upYourSproc
SELECT y.AuditEventType,v.InternalExamID, MIN(ChangeDTTM)
FROM vusrAuditDiagnosticReport v
WHERE (v.AuditEventType = DICTATE) OR (v.AuditEventType = TRANSCRIBE) OR (v.AuditEventType = AUTHENT)
GROUP BY y.AuditEventType
0
 
Anthony PerkinsCommented:
You have posted this same question before without any luck, have you considered it may be time to get professional help from some place like www.rentacoder.com
0
 
adraughnCommented:
that's a bit much to ask for in a question. why don't you take a stab at it and then post your questions/problem areas and we will help you..

-a
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
adraughnCommented:
That should give you a start. You will need to work with the formatting
0
 
adraughnCommented:
squares are ' (apostrophe)
0
 
dbguy2626Author Commented:
Thanks adraughn for the start, I'll give it a shot..
0
 
dbguy2626Author Commented:
I'll update with questions.  Sorry for being such a pain in the a**..  I appreciate the help
0
 
adraughnCommented:
just let us know if you get stuck
0
 
dbguy2626Author Commented:
adraughn: I get the following error
Server: Msg 107, Level 16, State 2, Procedure rptMinimumAuditDiagnosticResultDTTMs, Line 3
The column prefix 'y' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Procedure rptMinimumAuditDiagnosticResultDTTMs, Line 3
The column prefix 'y' does not match with a table name or alias name used in the query.

is this because I am using views?  I checked the spelling of the names and all looks right. Any ideas?

CREATE PROCEDURE dbo.rptMinimumAuditDiagnosticResultDTTMs
AS
SELECT y.AuditEventType,v.InternalExamID, MIN(ChangeDTTM)
FROM vusrAuditDiagnosticReport v
WHERE (v.AuditEventType = 'DICTATE') OR (v.AuditEventType = 'TRANSCRIBE') OR (v.AuditEventType = 'AUTHENT')
GROUP BY y.AuditEventType
0
 
dbguy2626Author Commented:
thanks again adraughn for helping me, I know this was too long of a question to post but I didnt know where else to turn to.. I guess I should of rented a coder!
0
 
adraughnCommented:
sorry - typo - change the y to v
0
 
adraughnCommented:
you're welcome
0
 
adraughnCommented:
i'm leaving to haul the kids to soccer and am going out tonight but i will check this thread in the morning. good luck!

-a
0
 
dbguy2626Author Commented:
adraughn:thank you very very much, I'm gonna close this thread now, I think I have a couple follow up questions that I'm hoping to get answered so I'm going to post a new one!  I think everyone is going to hate me on this system, but I'm on such a tight deadline, I'm very new so  I dont know the etiquette yet!  I'm Very sorry to everyone :)
0
 
adraughnCommented:
you're welcome. just post back if you need help.

adria
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.