TimeSpent SQL Query for ALL records in ChargesTable
Hello,
We want to generate a single report that includes ALL TimeSpent Records from the ChargesTable. If users are logging time, we want it to appear in our report.
The first step we use is to return every ChargeID from ChargesTable along with the one or more Keys from the "bridge" tables, WorkOrderToCharge, TaskToCharge, ChangeToCharge, ProblemToCharge that are associated with that ChargeID.
(SQL is constructed to prevent duplicating TimeSpent if/when ChargeID has multiple bridge keys).
Next, the Keys from the bridge tables are used to join with the various related tables containing more details about the requested work. (ProjectDetails, TaskDetails, Workorder, ChangeDetails, Problem, User Tables etc.)
The problem we are seeing is in the FIRST step. There are a number of records in the ChargesTable where the ChargeID is NOT represented in one of these "bridge" tables, resulting in "TimeSpent Orphans".
Query in MSSQL
-- TimeSpent ORPHANS
select dtCharge_created = cast(DATEADD(ss, createdtime / 1000,'1970-01-01 00:00:00') as date)
, dtCharge_start = cast(DATEADD(ss, ts_starttime / 1000,'1970-01-01 00:00:00') as date)
, dtCharge_end = cast(DATEADD(ss, ts_endtime / 1000,'1970-01-01 00:00:00') as date)
, timespent_mi = timespent / 1000 / 60
, timespent_hh = cast((cast(timespent as decimal(18,2)) / 1000 / 60 / 60) as decimal(18,2))
, ct.*
from ChargesTable ct
where chargeid not in ( select chargeid from WorkOrderToCharge
union select chargeid from TaskToCharge
union select chargeid from ChangeToCharge
union select chargeid from ProblemToCharge
)
and isnull(timespent,0) > 0
Can you explain what might be causing the Orphans?
We have tried entering test tickets to recreate the "orphan" timespent records. So far we have not been able to successfully recreate this scenario. (eg. Delete Worklog, Merge Requests and other tests)
We have searched all the tables in the servicedesk database for columns named chargeid or charge_id. ChargeID is found in the 5 tables referenced in the above query. The only other references found in the servicedesk database are 3 empty tables (arc_chargestable, arc_tasktocharge, arc_workordertocharge).
Any added information is appreciated.
Thanks for your consideration.
Tena TenBrink
ZELTIQ
www.coolsculpting.com
PS. Our SDP installation uses a PostgreSQL database, but we are extracting the data into MSSQL database for custom reporting. I did not include the full query to report ALL time, only the section of the query that generates the "orphaned" TimeSpent records.
We want to generate a single report that includes ALL TimeSpent Records from the ChargesTable. If users are logging time, we want it to appear in our report.
The first step we use is to return every ChargeID from ChargesTable along with the one or more Keys from the "bridge" tables, WorkOrderToCharge, TaskToCharge, ChangeToCharge, ProblemToCharge that are associated with that ChargeID.
(SQL is constructed to prevent duplicating TimeSpent if/when ChargeID has multiple bridge keys).
Next, the Keys from the bridge tables are used to join with the various related tables containing more details about the requested work. (ProjectDetails, TaskDetails, Workorder, ChangeDetails, Problem, User Tables etc.)
The problem we are seeing is in the FIRST step. There are a number of records in the ChargesTable where the ChargeID is NOT represented in one of these "bridge" tables, resulting in "TimeSpent Orphans".
Query in MSSQL
-- TimeSpent ORPHANS
select dtCharge_created = cast(DATEADD(ss, createdtime / 1000,'1970-01-01 00:00:00') as date)
, dtCharge_start = cast(DATEADD(ss, ts_starttime / 1000,'1970-01-01 00:00:00') as date)
, dtCharge_end = cast(DATEADD(ss, ts_endtime / 1000,'1970-01-01 00:00:00') as date)
, timespent_mi = timespent / 1000 / 60
, timespent_hh = cast((cast(timespent as decimal(18,2)) / 1000 / 60 / 60) as decimal(18,2))
, ct.*
from ChargesTable ct
where chargeid not in ( select chargeid from WorkOrderToCharge
union select chargeid from TaskToCharge
union select chargeid from ChangeToCharge
union select chargeid from ProblemToCharge
)
and isnull(timespent,0) > 0
Can you explain what might be causing the Orphans?
We have tried entering test tickets to recreate the "orphan" timespent records. So far we have not been able to successfully recreate this scenario. (eg. Delete Worklog, Merge Requests and other tests)
We have searched all the tables in the servicedesk database for columns named chargeid or charge_id. ChargeID is found in the 5 tables referenced in the above query. The only other references found in the servicedesk database are 3 empty tables (arc_chargestable, arc_tasktocharge, arc_workordertocharge).
Any added information is appreciated.
Thanks for your consideration.
Tena TenBrink
ZELTIQ
www.coolsculpting.com
PS. Our SDP installation uses a PostgreSQL database, but we are extracting the data into MSSQL database for custom reporting. I did not include the full query to report ALL time, only the section of the query that generates the "orphaned" TimeSpent records.