Raul, are you running this query from within MS Excel? Crystal Reports? Also, If you're using an ARSystem ODBC driver the date conversion formula I gave you isn't necessary.
________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 12:35 PM To: [email protected] Subject: FW: Remedy SQL query assistance... Please Help. ** From: Zaldivar, Jack (ISD, IT) Sent: Friday, December 14, 2007 3:33 PM To: Martinez, Raul (ISD, IT) Subject: RE: Remedy SQL query assistance... Please Help. SELECT "HPD:HelpDesk"."Assigned To Group+", "HPD:HelpDesk"."Assigned To Individual+", "HPD:HelpDesk"."Assignee Login Name", "HPD:HelpDesk"."Case ID+", "HPD:HelpDesk".Source, "HPD:HelpDesk".Status, "HPD:HelpDesk"."Submitted By" FROM "HPD:HelpDesk" "HPD:HelpDesk" WHERE (dateadd(ss, (convert(int, "Create Date"))+(3600*-8), '01-01-1970 00:00:00') >= '12/13/2007' AND dateadd(ss, (convert(int, "Create Date"))+(3600*-8), '01-01-1970 00:00:00') < '12/14/2007') AND ("HPD:HelpDesk".Status<>'Resolved' And "HPD:HelpDesk".Status<>'Closed') ORDER BY "HPD:HelpDesk"."Case ID+" ________________________________ From: Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 2:21 PM To: Zaldivar, Jack (ISD, IT) Subject: FW: Remedy SQL query assistance... Please Help. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Eli Schilling Sent: Friday, December 14, 2007 3:13 PM To: [email protected] Subject: Re: Remedy SQL query assistance... Please Help. ** Raul, If you want to do a direct SQL query and work with date ranges you have to convert the date field in the database. To do so in MS SQL try: dateadd(ss, (convert(int, Submit_Date))+(3600*-8), '01-01-1970 00:00:00') So a simple query might look like: Select Incident_Number, Description, Priority_text = Case WHEN Priority = 3 THEN 'Low' WHEN Priority = 2 THEN 'Medium' When PRIORITY = 1 THEN 'High' WHEN Priority = 0 THEN 'Critical' END, submitter from HPD_Help_Desk Where dateadd(ss, (convert(int, Submit_Date))+(3600*-8), '01-01-1970 00:00:00') >= '12/13/2007' AND dateadd(ss, (convert(int, Submit_Date))+(3600*-8), '01-01-1970 00:00:00') < '12/14/2007' AND (Submitter = 'Bob.Backline' OR Submitter = 'Sally.Supporter' OR Submitter = 'Francy.Frontline') Group by Submitter Is that what you were hoping for? Cheers! Eli ________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 7:47 AM To: [email protected] Subject: Remedy SQL query assistance... Please Help. ** Can anyone assist me in either in connecting an ODBC connection to Remedy through Access or with creating a SQL search query for $DATE$ function? I am needing to grab all submitted tickets for current day based on certain submitters. I already have a remedy macro created but it would be easier with a SQL query. I had to split the macro up several times due to the server being limited to only 1000 line return. ************************************************************************ * This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies. ************************************************************************ * __20060125_______________________This posting was submitted with HTML in it___ __20060125_______________________This posting was submitted with HTML in it___ __20060125_______________________This posting was submitted with HTML in it___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
<<Outlook.jpg>>
