I have a several custom reports which for example show how many calls each technician has updated today. The SQL query for this is shown below.

We also use SQL reporting services to display a wallboard of automatically refreshed query results. If I copy the SQL query below at use it to form the basis of a Dataset in SQL Reporting Services I can create the requred table, however because the date is coded in the query, the same query can not be used for more than one day.

Can you give me any advise on how to change this SQL query to incorporate "todays" figures, whatever the date might be.

I have tried to replace the ct.ts.starttime criteria in the query with a "getdate()" but can not get it working.

thanks in advance

Gary

SELECT lvd.LEVELNAME "Level",rctd.FIRST_NAME "Time Spent Technician",ct.TIMESPENT "Time Spent" 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 WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN LevelDefinition lvd ON wos.LEVELID=lvd.LEVELID WHERE  ((((lvd.LEVELNAME = N'1st Line' COLLATE Latin1_General_CI_AS) OR (lvd.LEVELNAME = N'2nd Line' COLLATE Latin1_General_CI_AS)) OR (lvd.LEVELNAME = N'3rd Line' COLLATE Latin1_General_CI_AS)) AND (((ct.TS_STARTTIME >= 1415836800000) AND ((ct.TS_STARTTIME != 0) AND (ct.TS_STARTTIME IS NOT NULL))) AND ((ct.TS_STARTTIME <= 1415923199000) AND (((ct.TS_STARTTIME != 0) AND (ct.TS_STARTTIME IS NOT NULL)) AND (ct.TS_STARTTIME != -1)))))  AND wo.ISPARENT='1'