SELECT DDO.TIDO,DDO.NUDO,DDO.ENDO,DDO.KOFULIDO,DDO.KOPRCT,
'CANT'=(CASE  
        WHEN DDO.TIDO='GDV' 
        THEN 
          ( CASE 
            WHEN DDO.UDTRPR=1 THEN DDO.CAPRCO1-DDO.CAPRAD1-DDO.CAPREX1 
            ELSE DDO.CAPRCO2-DDO.CAPRAD2-DDO.CAPREX2 
            END ) 
        ELSE 
           ( CASE 
             WHEN DDO.UDTRPR=1 THEN DDO.CAPRCO1 
             ELSE DDO.CAPRCO2 
             END ) 
        END), 
'PL'=DDO.PPPRNELT, 
'VTPL'=DDO.PPPRNELT*0.0, 
'VTPV'=DDO.VANELI, 
'DSCTO'=DDO.VANELI*0.0, 
'MGL'=DDO.MGLTPR, 
'VTPC'=DDO.VANELI*0.0, 
'MGV'=DDO.VANELI*0.0 
INTO #METAS 
FROM MAEDDO AS DDO WITH ( NOLOCK ) 
 WHERE DDO.FEEMLI BETWEEN {d '2013-09-01'} AND {d '2013-09-17'} 
 AND DDO.TIDO IN ( 'BLV', 'BSV', 'FCV', 'FDV', 'FEV', 'FVL', 'FVT', 'FVX', 'NCV', 'NCX', 'NEV' ) 
 AND DDO.LILG NOT IN ('CR','IM') 
 AND DDO.PRCT=0 


INSERT INTO #METAS 
SELECT DDO.TIDO,DDO.NUDO,DDO.ENDO,DDO.KOFULIDO,DDO.KOPRCT, 
'CANT'=convert(float,0.0), 
'PL'=convert(float,0.0), 
'VTPL'=convert(float,0.0), 
'VTPV'=convert(float,0.0), 
'DSCTO'=DDO.VANELI, 
'MGL'=convert(float,0.0), 
'VTPC'=convert(float,0.0), 
'MGV'=convert(float,0.0) 
FROM MAEEDO AS EDO WITH ( NOLOCK ) 
INNER JOIN MAEDDO AS DDO ON DDO.TIDO=EDO.TIDO AND DDO.NUDO=EDO.NUDO AND DDO.ENDO=EDO.ENDO AND DDO.PRCT=1 
WHERE EDO.TIDO IN ( 'BLV', 'BSV', 'FCV', 'FDV', 'FEV', 'FVL', 'FVT', 'FVX', 'NCV', 'NCX', 'NEV' ) 
  AND EDO.FEEMDO BETWEEN {d '2013-09-01'} AND {d '2013-09-17'} 


UPDATE #METAS SET VTPL=CANT*PL, VTPC=(CANT*PL)*(1-(MGL/100))  

SELECT 
TIDO,NUDO,ENDO,KOFULIDO, 
VTPL =SUM(VTPL* (CASE WHEN TIDO IN ('NCV','NCX','NEV') THEN -1 ELSE 1 END)),  
VTPV =SUM(VTPV* (CASE WHEN TIDO IN ('NCV','NCX','NEV') THEN -1 ELSE 1 END)),  
DSCTO=SUM(DSCTO*(CASE WHEN TIDO IN ('NCV','NCX','NEV') THEN -1 ELSE 1 END)),
VTPC =SUM(VTPC* (CASE WHEN TIDO IN ('NCV','NCX','NEV') THEN -1 ELSE 1 END)),  
'MARGEN'=convert(float,0.0),     
'NOKOEN'=convert(varchar(50),'') 
INTO #METASSUM 
FROM #METAS WITH ( NOLOCK ) 
GROUP BY TIDO,NUDO,ENDO,KOFULIDO 

UPDATE #METASSUM SET MARGEN=CASE WHEN (VTPV+DSCTO)=0.0 THEN 0 ELSE (1-(VTPC/(VTPV+DSCTO)))*100 END,
NOKOEN=(SELECT TOP 1 MAEEN.NOKOEN FROM MAEEN WITH ( NOLOCK ) WHERE MAEEN.KOEN=#METASSUM.ENDO)

SELECT 
KOFULIDO, 
'NOKOFU'=convert(varchar(50),''), 
'META'=convert(float,0.0), 
TRANGO1=SUM( CASE WHEN MARGEN <  9999999                     THEN VTPV ELSE 0.0 END ), 
TRANGO2=SUM( CASE WHEN MARGEN >= 9999999 AND MARGEN < 9999999 THEN VTPV ELSE 0.0 END ), 
TRANGO3=SUM( CASE WHEN MARGEN >= 9999999 AND MARGEN < 9999999 THEN VTPV ELSE 0.0 END ), 
TRANGO4=SUM( CASE WHEN MARGEN >= 9999999                     THEN VTPV ELSE 0.0 END ), 
NRANGO1=SUM( CASE WHEN MARGEN <  9999999                     THEN 1.0  ELSE 0.0 END ), 
NRANGO2=SUM( CASE WHEN MARGEN >= 9999999 AND MARGEN < 9999999 THEN 1.0  ELSE 0.0 END ), 
NRANGO3=SUM( CASE WHEN MARGEN >= 9999999 AND MARGEN < 9999999 THEN 1.0  ELSE 0.0 END ), 
NRANGO4=SUM( CASE WHEN MARGEN >= 9999999                     THEN 1.0  ELSE 0.0 END ), 
VTPC=SUM(VTPC), 
VTPV=SUM(VTPV), 
DSCTO=SUM(DSCTO), 
'PROYECCION'=convert(float,0.0), 
'EXPECTATIV'=convert(float,0.0), 
'PORCENTAJE'=convert(float,0.0) 
INTO #METASFIN 
FROM #METASSUM WITH ( NOLOCK ) 
GROUP BY KOFULIDO 

INSERT INTO #METASFIN 
SELECT KOFU,'',SEPTIEMBRE,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0 
FROM TABMETAS 
WHERE PERIODO='2013' AND KOFU NOT IN ( SELECT X.KOFULIDO FROM #METASFIN AS X ) 

UPDATE #METASFIN SET NOKOFU=(SELECT F.NOKOFU FROM TABFU AS F WITH ( NOLOCK ) WHERE F.KOFU=#METASFIN.KOFULIDO ),
META=(SELECT M.SEPTIEMBRE FROM TABMETAS AS M WITH ( NOLOCK ) WHERE M.PERIODO='2013' AND M.KOFU=#METASFIN.KOFULIDO ),
PROYECCION=(VTPV/24)*24,
EXPECTATIV=CASE WHEN ISNULL((SELECT M.SEPTIEMBRE FROM TABMETAS AS M WITH ( NOLOCK ) WHERE M.PERIODO='2013' AND M.KOFU=#METASFIN.KOFULIDO ),0)=0 THEN 0.0 ELSE ((VTPV/24)*24)*100/(SELECT M.SEPTIEMBRE FROM TABMETAS AS M WITH ( NOLOCK ) WHERE M.PERIODO='2013' AND M.KOFU=#METASFIN.KOFULIDO ) END,
PORCENTAJE=CASE WHEN (VTPV+DSCTO)=0 THEN 0.0 ELSE (1-(VTPC/(VTPV+DSCTO)))*100 END 

