[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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
0
dbguy2626
Asked:
dbguy2626
  • 9
  • 5
1 Solution
 
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
 
adraughnCommented:
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
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.

 
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now