Query the database and list it with specific field names

SSAS and SSRS
1

We have a helpdesk sql server db. I want to query the database based on the following"

Created Time
Completed Time
Requester
Department
Request ID
Request Status
Technician
Subject / Description

These are the actual fields in the database for the Helpdesk system. Now, I want this to be queried but to be displayed with a different field names for the title

Created Time -> Log Time
Completed Time -> Close Date
Request ID -> Problem No
Request Status -> Status
Technician -> Assigned
Department -> Area
Subject / Description -> Title

So, it should query the database and bring these items based on the department and display it in the following format with the new headers. I am not sure whether this is really SSRS. But, I just selected this thinking it might fall under this.

I am not a SQL Programmer, so I am not sure how to do this. Can someone help please. There is a Query section within the Servicedesk software and if I enter the query, and click on run report, I should be able to run the report. So, any assistance would be of great help in how to get this.

Appreciate your help in advance!

Thanks

2

please provide table names involved

3

Hi Yosiasz,

First thanks for your response. I did a query on the user tables and I get too many.

I checked some of their queries within the system and see that it has something like this
WORKORDER.CREATEDTIME
WORKORDER.COMPLETEDTIME
WORKORDER.WORKORDERID

Will this Query statement provide you all that you need

SELECT ImpactDefinition.NAME 'Impact',InProgressTable.INPROGRESS 'Open',OnHoldTable.ONHOLD 'On Hold',ResolvedTable.RESOLVED 'Resolved', CompletedTable.COMPLETED 'Closed',PendingTable.PENDINGRESPONSE 'Awaiting User Response', InboundTable.INBOUND 'TotalCount', ( (CompletedTable.COMPLETED*100)/InboundTable.INBOUND) 'Closed %',SlaViolatedTable.SLA_VIOLATED 'SLA Violated Count' ,( (SlaViolatedTable.SLA_VIOLATED/CompletedTable.COMPLETED)*100 ) 'SLA Violated %' ,SlaNotViolatedTable.SLA_NOT_VIOLATED 'SLA Not Violated',( (SlaNotViolatedTable.SLA_NOT_VIOLATED/CompletedTable.COMPLETED)*100 ) '% SLA Not Violated' FROM ImpactDefinition RIGHT JOIN ( SELECT WorkOrderStates.IMPACTID, COUNT(WorkOrder.WORKORDERID) 'INBOUND' FROM WorkOrder LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID WHERE WorkOrder.CREATEDTIME >= <from_thismonth> AND WorkOrder.CREATEDTIME <= <to_thismonth> GROUP BY WorkOrderStates.IMPACTID ) AS InboundTable ON InboundTable.IMPACTID = ImpactDefinition.IMPACTID LEFT JOIN( SELECT WorkOrderStates.IMPACTID, COUNT(WorkOrder.WORKORDERID) 'COMPLETED' FROM WorkOrder LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID LEFT JOIN STATUSDEFINITION ON STATUSDEFINITION.STATUSID=WorkOrderStates.STATUSID WHERE WorkOrder.CREATEDTIME >= <from_thismonth> AND WorkOrder.CREATEDTIME <= <to_thismonth> AND STATUSDEFINITION.ISPENDING=0 GROUP BY WorkOrderStates.IMPACTID ) AS CompletedTable ON InboundTable.IMPACTID = CompletedTable.IMPACTID LEFT JOIN( SELECT WorkOrderStates.IMPACTID, COUNT(WorkOrder.WORKORDERID) 'INPROGRESS' FROM WorkOrder LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID LEFT JOIN STATUSDEFINITION ON STATUSDEFINITION.STATUSID=WorkOrderStates.STATUSID WHERE WorkOrder.CREATEDTIME >= <from_thismonth> AND WorkOrder.CREATEDTIME <= <to_thismonth> AND STATUSDEFINITION.ISPENDING=1 AND STATUSDEFINITION.STATUSSTOPCLOCK=0 GROUP BY WorkOrderStates.IMPACTID ) AS InProgressTable ON InboundTable.IMPACTID = InProgressTable.IMPACTID LEFT JOIN( SELECT WorkOrderStates.IMPACTID, COUNT(WorkOrder.WORKORDERID) 'ONHOLD' FROM WorkOrder LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID LEFT JOIN STATUSDEFINITION ON STATUSDEFINITION.STATUSID=WorkOrderStates.STATUSID WHERE WorkOrder.CREATEDTIME >= <from_thismonth> AND WorkOrder.CREATEDTIME <= <to_thismonth> AND STATUSDEFINITION.ISPENDING=1 AND STATUSDEFINITION.STATUSSTOPCLOCK=1 GROUP BY WorkOrderStates.IMPACTID ) AS OnHoldTable ON InboundTable.IMPACTID = OnHoldTable.IMPACTID LEFT JOIN( SELECT WorkOrderStates.IMPACTID, COUNT(WorkOrder.WORKORDERID) 'RESOLVED' FROM WorkOrder LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID LEFT JOIN STATUSDEFINITION ON STATUSDEFINITION.STATUSID=WorkOrderStates.STATUSID WHERE WorkOrder.CREATEDTIME >= <from_thismonth> AND WorkOrder.CREATEDTIME <= <to_thismonth> AND STATUSDEFINITION.ISPENDING=1 AND STATUSDEFINITION.STATUSSTOPCLOCK=1 GROUP BY WorkOrderStates.IMPACTID ) AS ResolvedTable ON InboundTable.IMPACTID = ResolvedTable.IMPACTID LEFT JOIN( SELECT WorkOrderStates.IMPACTID, COUNT(WorkOrder.WORKORDERID) 'PENDINGRESPONSE' FROM WorkOrder LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID WHERE WorkOrder.CREATEDTIME >= <from_thismonth> AND WorkOrder.CREATEDTIME <= <to_thismonth> AND WorkOrderStates.NOTIFICATIONSTATUS='TECH_REPLY' GROUP BY WorkOrderStates.IMPACTID ) AS PendingTable ON InboundTable.IMPACTID = PendingTable.IMPACTID LEFT JOIN(SELECT WorkOrderStates.IMPACTID,COUNT(WorkOrder.WORKORDERID) 'SLA_VIOLATED' FROM WorkOrder LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID WHERE WorkOrder.CREATEDTIME >= <from_thismonth> AND WorkOrder.CREATEDTIME <= <to_thismonth> AND WorkOrderStates.ISOVERDUE = 1 GROUP BY WorkOrderStates.IMPACTID ) AS SlaViolatedTable ON InboundTable.IMPACTID = SlaViolatedTable.IMPACTID LEFT JOIN(SELECT WorkOrderStates.IMPACTID,COUNT(WorkOrder.WORKORDERID) 'SLA_NOT_VIOLATED' FROM WorkOrder LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID=WorkOrderStates.WORKORDERID WHERE WorkOrder.CREATEDTIME >= <from_thismonth> AND WorkOrder.CREATEDTIME <= <to_thismonth> AND WorkOrderStates.ISOVERDUE = 0 GROUP BY WorkOrderStates.IMPACTID ) AS SlaNotViolatedTable ON InboundTable.IMPACTID = SlaNotViolatedTable.IMPACTID
ORDER BY InboundTable.IMPACTID

4

not really, the only thing I was able to glean from this query was : COMPLETED
Requester, Department, Technician are missing from that query.

5

I am trying to get it Yosiasz. Thanks again for your help. Should have it soon.

6

Am I missing something or is this as simple as using the AS statement to alias your column headers.

For example:

SELECT
[Created Time] AS [Log Time]
FROM
[yourtable];

Note: the square brackets are being used because you have spaces in your names.

7

Hi Yosiasz and Stephen

Here are the tables that are there.

SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request Status", dpt.DEPTNAME AS "Department", wo.CREATEDTIME AS "Created Time" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 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')

I want these table names (headers) in the left to be shown as those in the right hand side as headers.

Created Time - Log Time
Completed Time - Close Date
Request ID - Problem No
Request Status - Status
Technician - Assigned
Department - Area
Subject / Description - Title

8

Ok so you are currently aliasing wo.CREATETIME as Created Time, just change that to AS [Log Time] (and follow suit with the other columns as required.

9

SELECT wo.CREATEDTIME AS "LOG DATE", wo.COMPLETED TIME AS "CLOSE DATE", wo.WORKORDERID AS "Request ID", std.STATUSNAME AS "STATUS", ti.FIRST_NAME AS "ASSIGNED" , dpt.DEPTNAME AS "AREA", wo.TITLE AS "TITLE" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 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')

I have referenced it as you have mentioned but when I run a query nothing happens. I think there is an issue after the FROM statement....

10

I did get the response from support and got the entire query for this. I want to just add few more to this query where I can query by date range, weekly and monthly and also by specific department I want to query and send out reports. Any help? As, most of the tables are shown above. I just want to query by weekly, monthly and from to To date based on a department.

SELECT longtodate(wo.CREATEDTIME) AS "LOG DATE", longtodate(wo.COMPLETEDTIME) AS "CLOSE DATE", wo.WORKORDERID AS "Request ID", std.STATUSNAME AS "STATUS", ti.FIRST_NAME AS "ASSIGNED" , dpt.DEPTNAME AS "AREA", wo.TITLE AS "TITLE" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 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')