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___
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
<<Outlook.jpg>>
