I need a report that shows the top 10 service requests grouped by category, sub_category for the past month.

 

Has anyone got a similar report I could look at.

 

I have developed a script in MSSQL Management Studio but unsure how to filter dates which are stored as integers.

 

When I run the same query in SDP it does not format correctly.

 

Running SDP 8013.

 

This is my sql script which as I said works OK except it  does not filter on  dates as not sure how to interpret them.

 

SELECT top 10 ISNULL(cd.CATEGORYNAME,'Unassigned') "Category", ISNULL(sc.NAME,'Unassigned') "SubCategory",
COUNT(*)  'Count'  FROM WorkOrder
INNER JOIN WorkOrder_Threaded ON WORKORDER.workorderid = WorkOrder_Threaded.thd_woid
LEFT JOIN WorkOrderStates wos ON workorder.WORKORDERID=wos.WORKORDERID
LEFT JOIN SubCategoryDefinition sc on wos.subcategoryid = sc.subcategoryid
LEFT JOIN CategoryDefinition cd on wos.CATEGORYID = cd.CATEGORYID
WHERE WorkOrder_Threaded.workorderid = WorkOrder_Threaded.thd_woid
GROUP BY cd.CATEGORYNAME,sc.NAME ORDER BY 3 DESC

 

Any help would be much appreciated

 

Cheers

 

Jeff