Project Queries

Project Queries

Project - task report

select  tdet.taskid , tdet.title as TITLE, pdet.title as PROJECT, tec.first_name as TECHNICIAN, from_unixtime(ct.ts_starttime/1000) as STARTTIME,from_unixtime(ct.ts_endtime/1000) as ENDTIME, round(ct.timespent/(60*60*1000))::text|| 'hrs' || round(ct.timespent/(60*1000)`)::text || 'mins' as TIMESPENT, ct.total_charge as TOTALCHARGE from projectdetails pdet left join tasktoprojects tap on pdet.projectid=tap.projectid inner join tasktocharge tac on tap.taskid=tac.taskid left join chargestable ct on tac.chargeid=ct.chargeid left join taskdetails tdet on tac.taskid=tdet.taskid left join aaauser tec on ct.technicianid=tec.user_id order by 3

Project and task by technician report


select   tec.first_name as TECHNICIAN,  pdet.projectid as PROJECTID, pdet.title as PROJECTTITLE,tdet.taskid , tdet.title as TITLE,from_unixtime(ct.ts_starttime/1000) as STARTTIME,from_unixtime(ct.ts_endtime/1000) as ENDTIME, round(ct.timespent/(60*60*1000))::text|| 'hrs' || round(ct.timespent/(60*1000)`)::text || 'mins' as TIMESPENT, ct.total_charge as TOTALCHARGE from projectdetails pdet left join tasktoprojects tap on pdet.projectid=tap.projectid inner join tasktocharge tac on tap.taskid=tac.taskid left join chargestable ct on tac.chargeid=ct.chargeid left join taskdetails tdet on tac.taskid=tdet.taskid left join aaauser tec on ct.technicianid=tec.user_id where ct.ts_starttime< extract(epoch from timestamp with time zone '2014-12-12 03:04:05+06')*1000 and  ct.ts_starttime>=extract(epoch from timestamp with time zone '2014-09-25 03:04:05+06')*1000 and ct.ts_endtime>=extract(epoch from timestamp with time zone '2014-09-25 03:04:05+06')*1000 order by 1


Here in the above query you need to change the time as required. <from_today> etc can also be suggested.

Project by account report

select ad.org_name as ACCOUNTNAME, pdet.projectid as PROJECTID, pdet.title as TITLE, ps.statusname as STATUS, from_unixtime(pdet.scheduledstarttime/1000) as SCHEDULEDSTARTTIME, from_unixtime(pdet.actualstarttime/1000) as ACTUALSTARTTIME, from_unixtime(pdet.scheduledendtime/1000) as SCHEDULEDENDTIME, from_unixtime(pdet.actualendtime/1000) as ACTUALENDTIME, pest.actualtaskhours as ACTUALHOURS, pest.tot_taskhours_cost as ACTUALCOST from projectdetails pdet left join projectstatus ps on pdet.statusid=ps.statusid left join projectestimations pest on pdet.projectid=pest.projectid left join projectaccmapping pam on pdet.projectid=pam.projectid
left join accountdefinition ad on pam.accountid=ad.org_id order by 1,4
              New to ADManager Plus?

                New to ADSelfService Plus?