Remedy SQL query assistance... Please Help.
We would like to do this in Access but are running into an issue with extra characters being recognized and fails to connect, currently running this in Excel. We are going to use the Closed option due to it being the last in the list. And since this is going to be a process that is automated and updating every 2 minutes we kind of did not want to enter the actual date in the code. We were hoping to use a function similar to $DATE$ to capture the current date, we currently have that function in a macro that spits the data to a csv like you mentioned. The only problem is that some of the macros will not have data in the early morning so that is why we are shooting for a SQL query. Remedy gives you an error when no data is found and since there is no way to not display errors like that it stops the rest of the Remedy macros from running behind it. I attached the macro that we are using. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Eli Schilling Sent: Friday, December 14, 2007 7:21 PM To: arslist@ARSLIST.ORG Subject: Re: Remedy SQL query assistance... Please Help. ** 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: arslist@ARSLIST.ORG 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+ * 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. * ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are image001.jpg AssignedToServDeskCombined.arq Description: AssignedToServDeskCombined.arq
Re: Remedy SQL query assistance... Please Help.
If you are using SQL directly to the database then you have to do all of the translations for Selection Fields and Date (Date/Time, Date, and Time) Fields yourself. Selection Fields like Status are stored in the database as integers (1st selection = 0, 2nd selection = 1, ...) Date Fields are stored in the database as integers as well. Date/Time fields are the number of seconds since January 1, 1970 Date fields are the number of days since January 1, 4713 B.C. Time fields are the number of seconds since midnight Also since you will be doing this every 2 minutes here is a performance tip. Do all of the conversions on your side of the qualifications. (Instead of doing the conversion on a field in the database to convert it to what you are looking for, convert what you are looking for into the way it is stored in the databse. If you convert the field from the database then every row in the database will have to be checked to see if it matched your qualifications.) i.e. Instead of converting the date field in the database to a real date so you can check if it is a value, convert your value into an integer as it would be stored in the database. Fred From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Tuesday, December 18, 2007 3:21 PM To: arslist@ARSLIST.ORG Subject: Remedy SQL query assistance... Please Help. ** We would like to do this in Access but are running into an issue with extra characters being recognized and fails to connect, currently running this in Excel. We are going to use the Closed option due to it being the last in the list. And since this is going to be a process that is automated and updating every 2 minutes we kind of did not want to enter the actual date in the code. We were hoping to use a function similar to $DATE$ to capture the current date, we currently have that function in a macro that spits the data to a csv like you mentioned. The only problem is that some of the macros will not have data in the early morning so that is why we are shooting for a SQL query. Remedy gives you an error when no data is found and since there is no way to not display errors like that it stops the rest of the Remedy macros from running behind it. I attached the macro that we are using. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Eli Schilling Sent: Friday, December 14, 2007 7:21 PM To: arslist@ARSLIST.ORG Subject: Re: Remedy SQL query assistance... Please Help. ** 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: arslist@ARSLIST.ORG 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+ * 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___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are image001.jpg
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. * ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are AssignedToServDeskCombined.arq Description: AssignedToServDeskCombined.arq
Re: Remedy SQL query assistance... Please Help.
I think he mentioned that the SERVER has been limited to 1000. On Dec 14, 2007 11:03 AM, Evans.Randy [EMAIL PROTECTED] wrote: ** Change your preferences to unlimited while you run the macro. You probably don't want to go against the database itself. Remedy has a tendency to store dates as long integers based on the number of seconds since 1/1/1970. -- *From:* Action Request System discussion list(ARSList) [mailto: [EMAIL PROTECTED] *On Behalf Of *Martinez, Raul (ISD, IT) *Sent:* Friday, December 14, 2007 9:47 AM *To:* arslist@ARSLIST.ORG *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___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Remedy SQL query assistance... Please Help.
Change your preferences to unlimited while you run the macro. You probably don't want to go against the database itself. Remedy has a tendency to store dates as long integers based on the number of seconds since 1/1/1970. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 9:47 AM To: arslist@ARSLIST.ORG 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___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Remedy SQL query assistance... Please Help.
What database are you using? On Dec 14, 2007 11:33 AM, Martinez, Raul (ISD, IT) [EMAIL PROTECTED] wrote: ** Yes there is a limitation in affect right now on the server. Here is an example of what we are doing, but of course the code is not 100% correct: said syntax error *?* 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 (HPD:HelpDesk.Assignee Login Name='Jack Zaldivar') AND (HPD:HelpDesk.Status'Resolved' And HPD:HelpDesk.Status'Closed') AND (HPD:HelpDesk.Create TimeSYSDATE) ORDER BY HPD:HelpDesk.Case ID+ 11:11:11 AM *?* that's just an example 11:11:23 AM *?* not what we REALLY want 11:11:30 AM *From:* Action Request System discussion list(ARSList) [mailto: [EMAIL PROTECTED] *On Behalf Of *Frank Caruso *Sent:* Friday, December 14, 2007 11:08 AM *To:* arslist@ARSLIST.ORG *Subject:* Re: Remedy SQL query assistance... Please Help. ** I think he mentioned that the SERVER has been limited to 1000. On Dec 14, 2007 11:03 AM, Evans.Randy [EMAIL PROTECTED] wrote: ** Change your preferences to unlimited while you run the macro. You probably don't want to go against the database itself. Remedy has a tendency to store dates as long integers based on the number of seconds since 1/1/1970. -- *From:* Action Request System discussion list(ARSList) [mailto: [EMAIL PROTECTED] *On Behalf Of *Martinez, Raul (ISD, IT) *Sent:* Friday, December 14, 2007 9:47 AM *To:* arslist@ARSLIST.ORG *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___ __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
Re: Remedy SQL query assistance... Please Help.
Yes there is a limitation in affect right now on the server. Here is an example of what we are doing, but of course the code is not 100% correct: said syntax error ? 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 (HPD:HelpDesk.Assignee Login Name='Jack Zaldivar') AND (HPD:HelpDesk.Status'Resolved' And HPD:HelpDesk.Status'Closed') AND (HPD:HelpDesk.Create TimeSYSDATE) ORDER BY HPD:HelpDesk.Case ID+ 11:11:11 AM ? that's just an example 11:11:23 AM ? not what we REALLY want 11:11:30 AM From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Frank Caruso Sent: Friday, December 14, 2007 11:08 AM To: arslist@ARSLIST.ORG Subject: Re: Remedy SQL query assistance... Please Help. ** I think he mentioned that the SERVER has been limited to 1000. On Dec 14, 2007 11:03 AM, Evans.Randy [EMAIL PROTECTED] wrote: ** Change your preferences to unlimited while you run the macro. You probably don't want to go against the database itself. Remedy has a tendency to store dates as long integers based on the number of seconds since 1/1/1970. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 9:47 AM To: arslist@ARSLIST.ORG 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
Re: Remedy SQL query assistance... Please Help.
Not sure what you mean by the Database but we are trying this search in excel via an ODBC connection. We also have tried to connect straight through Access but are unable to due to the importing of all the tables instead of allowing us to specify particular tables. If anyone knows how to do this in Access either that would be helpful. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Frank Caruso Sent: Friday, December 14, 2007 11:50 AM To: arslist@ARSLIST.ORG Subject: Re: Remedy SQL query assistance... Please Help. ** What database are you using? On Dec 14, 2007 11:33 AM, Martinez, Raul (ISD, IT) [EMAIL PROTECTED] wrote: ** Yes there is a limitation in affect right now on the server. Here is an example of what we are doing, but of course the code is not 100% correct: said syntax error ? 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 (HPD:HelpDesk.Assignee Login Name='Jack Zaldivar') AND (HPD:HelpDesk.Status'Resolved' And HPD:HelpDesk.Status'Closed') AND (HPD:HelpDesk.Create TimeSYSDATE) ORDER BY HPD:HelpDesk.Case ID+ 11:11:11 AM ? that's just an example 11:11:23 AM ? not what we REALLY want 11:11:30 AM From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Frank Caruso Sent: Friday, December 14, 2007 11:08 AM To: arslist@ARSLIST.ORG Subject: Re: Remedy SQL query assistance... Please Help. ** I think he mentioned that the SERVER has been limited to 1000. On Dec 14, 2007 11:03 AM, Evans.Randy [EMAIL PROTECTED] wrote: ** Change your preferences to unlimited while you run the macro. You probably don't want to go against the database itself. Remedy has a tendency to store dates as long integers based on the number of seconds since 1/1/1970. From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT) Sent: Friday, December 14, 2007 9:47 AM To: arslist@ARSLIST.ORG 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___ __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
Re: Remedy SQL query assistance... Please Help.
I'm not familiar with the stock Help Desk app, but this looks odd to
me... What are the enum values for the Status, and if you are excluding
Resolved and Closed via , I just don't like that. If resolved and
closed are right next together and they have an enum value of 3 and 4,
then ('Status' 3 and 'Status' 4). If they are not next together
then you could say something like ('Status' = 0 OR 'Status' =1 OR
'Status' = 3). Break up the dates like ('Create Date' = 12/01/2007
AND 'Create Date 12/14/2007), dates are from midnight unless
specified, so be inclusive on the lower date and exclusive on the latest
date. If you are macroing it, set those puppies up as variables and
dump them as csv's then merge the spreadsheets. If you are trying to
catch over a 1000 tickets for one day then do something like: ('Create
Date' = 12/01/2007 AND 'Create Date 12/01/2007 12:00). I could
go on, but we probably need more details on this ODBC connection, is it
giving you fits outside the realm that I've addressed?
Darrell E Reading II
Contact Center Development
Wal-Mart
45739
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
##
##
##
##
##
##
##
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT)
Sent: Friday, December 14, 2007 10:34
To: arslist@ARSLIST.ORG
Subject: Re: Remedy SQL query assistance... Please Help.
**
Yes there is a limitation in affect right now on the server. Here is an
example of what we are doing, but of course the code is not 100%
correct:
said syntax error
?
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 (HPD:HelpDesk.Assignee Login Name='Jack Zaldivar') AND
(HPD:HelpDesk.Status'Resolved' And HPD:HelpDesk.Status'Closed')
AND (HPD:HelpDesk.Create TimeSYSDATE)
ORDER BY HPD:HelpDesk.Case ID+
11:11:11 AM
?
that's just an example
11:11:23 AM
?
not what we REALLY want
11:11:30 AM
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Frank Caruso
Sent: Friday, December 14, 2007 11:08 AM
To: arslist@ARSLIST.ORG
Subject: Re: Remedy SQL query assistance... Please Help.
** I think he mentioned that the SERVER has been limited to 1000.
On Dec 14, 2007 11:03 AM, Evans.Randy [EMAIL PROTECTED]
wrote:
**
Change your preferences to unlimited while you run the macro. You
probably don't want to go against the database itself. Remedy has a
tendency to store dates as long integers based on the number of seconds
since 1/1/1970.
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Martinez, Raul (ISD, IT)
Sent: Friday, December 14, 2007 9:47 AM
To: arslist@ARSLIST.ORG
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___
__20060125___This posting was submitted with HTML in
it___
-
**
This email and any files transmitted with it are confidential and
intended solely for the individual or entity to whom they are
addressed. If you have received this email in error destroy it
immediately.
**
Wal-Mart Confidential
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: arslist@ARSLIST.ORG 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___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are Outlook.jpg
Re: Remedy SQL query assistance... Please Help.
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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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
