SQL Cookbook for ServiceDesk Plus - Chapter 1 - SQL basics
SQL Basics:
SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (wo.ISPARENT='1') AND wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth>
Select statement
:
The
SELECT
statement is used to query the database and retrieve the fields that you specify. You can select as many fields (column names) as you want, or use the asterisk symbol "*" to select all fields.
For
adding columns
in the query use the below link.
Conditions
:
The
WHERE
clause specifies which data values or rows will be returned or displayed, based on the criteria you specify.
For adding
conditions
in the query use the below link.
For adding
date filter
in the query use the below link.
Order by (to group/sort data):
The
ORDER BY
clause organizing/sorting the data by the field specified.
For organizing/sorting the data by the field specified in the query use the below link.