Search code examples
reporting-servicestimefiltermilliseconds

SSRS converting millisecond time and the filtering report

First time questioner so apologies for any vagueness or rule breaking

We use a ServiceDesk application that can produce a variety of pre configured reports and show the SQL query behind them. I then take these reports and create a wallboard from a SQL Server Report Server to give some "live" management information.

The application records work logs with a time stamp that seems to reference the number of milliseconds since 1/1/1970. I can generate the SQL query from the application to filter for "this month" for example and it them puts the time stamp in to my report such as the one below

SELECT ad.ORG_NAME "Account",wo.WORKORDERID "Request ID",ct.DESCRIPTION
 "Time Spent Description",ct.TIMESPENT "Time Spent",wtd.NAME "Worklog    
Type",ct.TS_STARTTIME "Time Spent Starttime",rctd.FIRST_NAME "Time Spent 
Technician",cd.CATEGORYNAME "Category",qd.QUEUENAME "Group",lvd.LEVELNAME 
"Level" 
FROM WorkOrder wo LEFT JOIN WorkOrderToCharge wotoc ON   
wo.WORKORDERID=wotoc.WORKORDERID 
LEFT JOIN ChargesTable ct ON  wotoc.CHARGEID=ct.CHARGEID 
LEFT JOIN SDUser rcti ON  ct.TECHNICIANID=rcti.USERID 
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID 
LEFT JOIN WorkLogTypeDefinition wtd ON ct.WORKLOGTYPEID=wtd.WORKLOGTYPEID 
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID 
LEFT JOIN QueueDefinition qd ON Woq.QUEUEID=qd.QUEUEID 
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID 
LEFT JOIN LevelDefinition lvd ON   wos.LEVELID=lvd.LEVELID 
INNER JOIN AccountSiteMapping asm ON  wo.siteid=asm.siteid INNER JOIN 
AccountDefinition ad ON asm.accountid=ad.org_id 
WHERE  (((ct.TS_STARTTIME >= 1441062000000) AND 
((ct.TS_STARTTIME != 0) AND (ct.TS_STARTTIME IS NOT NULL))) AND 
((ct.TS_STARTTIME <= 1443653999000) AND (((ct.TS_STARTTIME != 0) AND 
(ct.TS_STARTTIME IS NOT NULL)) AND (ct.TS_STARTTIME != -1))))  AND 
wo.ISPARENT='1'

The problem with this is that on the first of the month I have to go back to the servicedesk application - run the report again to update the tiem values to to reflect the current month - then transfer the query it generates over to the SSRS query. I would like to be able to remove the ct.ts.starttime elements of this query and instead have a filter in SSRS Dataset to only show the results where the ct.ts.startime is within the current month for example.

I am afraid I have very little SQL skills so if any one could point me in the right direction I would be very grateful

Thanks

BFG

Solution

  • You just need to change your WHERE clause to limit it within the current month using the starttime.

    WHERE DATEADD(s, ct.TS_STARTTIME / 1000, '1970-01-01') BETWEEN CAST(DATEADD(D, 1 - DATEPART(d, GETDATE()), GETDATE()) AS DATE) AND CAST(DATEADD(m, 1, DATEADD(D, 1 - DATEPART(d, GETDATE()), GETDATE())) AS DATE)
AND ct.TS_STARTTIME IS NOT NULL AND wo.ISPARENT='1'

    The only problem I see is that you don't say (and may not know) what version of SQL is being used (SQL Server, Oracle SQL, MySQL...). Unfortunately, most of these use a different function to get the current date. I used SQL Server's GETDATE() function.