10 lines
625 B
SQL
10 lines
625 B
SQL
create VIEW [C6Mart].[NotAllocatedResources]
|
|
as
|
|
SELECT PIRAMIDE_MOD.COD_FISCALE, PIRAMIDE_MOD.RETE, SUM(ISNULL(CTV,0)) as CTV, COUNT_BIG(*) as cnt
|
|
FROM C6MART.PIRAMIDE_MOD PIRAMIDE_MOD with (nolock)
|
|
INNER JOIN C6MART.PATRIMONIO_BF PIRAMIDE_VERT with (nolock)
|
|
ON PIRAMIDE_VERT.COD_FISCALE = PIRAMIDE_MOD.COD_FISCALE
|
|
AND PIRAMIDE_VERT.RETE = PIRAMIDE_MOD.RETE
|
|
AND CASE WHEN PIRAMIDE_VERT.ID_AREA = 'SELF' THEN LEFT(PIRAMIDE_VERT.ID_AREA,4) ELSE LEFT(PIRAMIDE_VERT.ID_AREA,3) END = PIRAMIDE_MOD.ID_AREA
|
|
WHERE PIRAMIDE_MOD.ID_AREA = 'Na' AND ISNULL(CTV,0) > 0
|
|
GROUP BY PIRAMIDE_MOD.COD_FISCALE, PIRAMIDE_MOD.RETE |