09-17-2013 07:14 AM
Hello,
Our existing report indicates all clients and their through the view attached project. We would like another report which shows the clients which have no attached project.
Can somebody help please?
SELECT DISTINCT
"E2"."name" as "Client Name",
"E1"."name" as "Project"
FROM ( ( ( ( ( ( "DBA"."domain_Entity" AS E2
JOIN "DBA"."view_Node" AS N2 ON E2.id = N2.entityId )
JOIN "DBA"."view_Node" AS N1 ON N2.parentNodeId = N1.id )
JOIN "DBA"."domain_Entity" AS E1 ON E1.id = N1.entityId )
JOIN "DBA"."view_Node" AS N0 ON N1.parentNodeId = N0.id )
JOIN "DBA"."domain_Entity" AS E0 ON E0.id = N0.entityId )
JOIN "DBA"."domain_JobArchive" on E2.id = "DBA"."domain_JobArchive".clientId )
WHERE N2.treeId IN (SELECT id FROM view_tree WHERE name = 'Project' ) AND N2.level = 2
AND "DBA"."domain_JobArchive"."endTime" > NOMTimeToUTCBigint( '2012-05-01 00:00:00' )
ORDER by "Client Name", "Project"
09-19-2013 06:22 AM
Hello,
Since I didn't get any answer and I didn't find the solution yet I try to explain the problem clearer.
We need a custom query that indicates all abjects which are not listed in the selected view. So where ther is no entry at the view_node.
Could anyone help to modify the existing report in that way?