Value accumulated day by day - Firebird

0

I'm starting now in the programming area, I'm having a hard time with SELECT which brings the accumulated value day by day of sales, I need this SELECT to play it on the line of a graph indicating the cumulative sales value of the month until arriving at another fixed line of goal.

SELECT  a.DATA
    ,   CAST(CEILING(COALESCE(  (
                                    SELECT SUM  (SIC0201.val_gerencial/1000) 
                                    FROM    sic0201
                                    WHERE   idempresa = 1 
                                        AND (SIC0201.data_pedido >= :WDTINI AND SIC0201.data_pedido <= :WDTFIM)
                                        AND (
                                                    SIC0201.situacao <> 'PC' 
                                                OR  SIC0201.situacao = '' 
                                                OR  SIC0201.situacao IS NULL
                                            ) 
                                        AND sic0201.data_pedido = a.DAta
                                        AND SIC0201.idvendedor >= :WVENDEDORINI 
                                        AND SIC0201.idvendedor <= :WVENDEDORFIM
                                        AND (
                                                (
                                                    (
                                                            SIC0201.IDOPERACAO =   '01' 
                                                        OR  SIC0201.IDOPERACAO = '14' 
                                                        OR  SIC0201.IDOPERACAO = '17'
                                                        OR  SIC0201.IDOPERACAO = '28'   
                                                        OR  SIC0201.IDOPERACAO ='30' 
                                                        OR  SIC0201.IDOPERACAO = '93'
                                                        OR  SIC0201.IDOPERACAO = '94' 
                                                        OR  SIC0201.IDOPERACAO = '91'
                                                    ) 
                                                    AND SIC0201.NOPER > 5000
                                                )
                                                OR  (
                                                            SIC0201.IDOPERACAO = '95' 
                                                        OR  SIC0201.IDOPERACAO = '96' 
                                                        OR  SIC0201.IDOPERACAO = '97'
                                                    )
                                            )), 0)
                    ) AS INTEGER) AS DATA1
    ,   CAST(((COALESCE(    (
                                SELECT  SUM(sic0213.fatbruto/1000) 
                                FROM    sic0213 
                                WHERE   SIC0213.TIPO = '23'
                                    AND SIC0213.codigo1 >= :WVENDEDORINI 
                                    AND SIC0213.codigo1 <= :WVENDEDORFIM
                                    AND sic0213.aamm >= COALESCE(LPAD(CAST(EXTRACT(YEAR FROM (CAST(:WDTINI as timestamp))) AS VARCHAR(4)), 4, '0') || LPAD(CAST(EXTRACT(MONTH FROM (CAST(:WDTINI AS TIMESTAMP))) AS VARCHAR(2)), 2, '0'), '')
                                    AND sic0213.aamm <= COALESCE(LPAD(CAST(EXTRACT(YEAR FROM (CAST(:WDTFIM as timestamp))) AS VARCHAR(4)), 4, '0') || LPAD(CAST(EXTRACT(MONTH FROM (CAST(:WDTFIM as timestamp))) AS VARCHAR(2)), 2, '0'), '')
                            ), 0) / (
                                        SELECT  *     
                                        FROM    dias_uteis  (   (
                                                                    SELECT DATEADD(1 - EXTRACT(DAY FROM CAST(a.data AS TIMESTAMP)) DAY TO CAST(a.data AS TIMESTAMP)) FROM RDB$DATABASE
                                                                ),  (
                                                                        SELECT  DATEADD(-EXTRACT(DAY FROM DATEADD(1 MONTH TO CAST(:WDTFIM AS TIMESTAMP) )) DAY TO DATEADD(1 MONTH TO CAST(:WDTFIM AS TIMESTAMP)))
                                                                        FROM    RDB$DATABASE
                                                                    )
                                                            )
                                    )
            )) AS INTEGER) AS data2
    ,   CAST(((COALESCE(    (
                                SELECT  SUM(sic0213.fatbruto/1000) 
                                FROM    sic0213 
                                WHERE   SIC0213.TIPO = '24'
                                    AND SIC0213.codigo1 >= :WVENDEDORINI 
                                    AND SIC0213.codigo1 <= :WVENDEDORFIM
                                    AND sic0213.aamm >= COALESCE(LPAD(CAST(EXTRACT(YEAR FROM (CAST(:WDTINI AS TIMESTAMP))) AS VARCHAR(4)), 4, '0') || LPAD(CAST(EXTRACT(MONTH FROM (CAST(:WDTINI AS TIMESTAMP))) AS VARCHAR(2)), 2, '0'), '')
                                    AND sic0213.aamm <= COALESCE(LPAD(CAST(EXTRACT(YEAR FROM (CAST(:WDTFIM as timestamp))) AS VARCHAR(4)), 4, '0') || LPAD(CAST(EXTRACT(MONTH FROM (CAST(:WDTFIM as timestamp))) AS VARCHAR(2)), 2, '0'), '')
                            ), 0) / (
                                        SELECT  *     
                                        FROM    dias_uteis  (   (
                                                                    SELECT DATEADD(1 - EXTRACT(DAY FROM CAST(a.data AS TIMESTAMP)) DAY TO CAST(a.data AS TIMESTAMP)) FROM RDB$DATABASE
                                                                ),  (
                                                                        SELECT  DATEADD(-EXTRACT(DAY FROM DATEADD(1 MONTH TO CAST(:WDTFIM AS TIMESTAMP))) DAY TO DATEADD(1 MONTH TO CAST(:WDTFIM AS TIMESTAMP)))
                                                                        FROM    RDB$DATABASE
                                                                    )
                                                            )
                                    )
            )) AS INTEGER) AS data3
    ,   CAST(((COALESCE(    (
                                SELECT  SUM(sic0213.fatbruto/1000) 
                                FROM    sic0213 
                                WHERE   SIC0213.TIPO = '25'
                                    AND SIC0213.codigo1 >= :WVENDEDORINI 
                                    AND SIC0213.codigo1 <= :WVENDEDORFIM
                                    AND sic0213.aamm >= COALESCE(LPAD(CAST(EXTRACT(YEAR FROM (CAST(:WDTINI AS TIMESTAMP))) AS VARCHAR(4)), 4, '0') || LPAD(CAST(EXTRACT(MONTH FROM (CAST(:WDTINI AS TIMESTAMP))) AS VARCHAR(2)), 2, '0'), '')
                                    AND sic0213.aamm <= COALESCE(LPAD(CAST(EXTRACT(YEAR FROM (CAST(:WDTFIM as timestamp))) AS VARCHAR(4)), 4, '0') || LPAD(CAST(EXTRACT(MONTH FROM (CAST(:WDTFIM AS TIMESTAMP))) AS VARCHAR(2)), 2, '0'), '')
                            ), 0) / (
                                        SELECT  *     
                                        FROM    dias_uteis  (   (
                                                                    SELECT DATEADD(1 - EXTRACT(DAY FROM CAST(a.data AS TIMESTAMP)) DAY TO CAST(a.data AS TIMESTAMP)) FROM RDB$DATABASE
                                                                ),  (
                                                                        SELECT  DATEADD(-EXTRACT(DAY FROM DATEADD(1 MONTH TO CAST(:WDTFIM AS TIMESTAMP) )) DAY TO DATEADD(1 MONTH TO CAST(:WDTFIM AS TIMESTAMP)))
                                                                        FROM    RDB$DATABASE
                                                                    )
                                                            )
                                    )
            )) AS INTEGER) AS data4
FROM    GEP0201 a
WHERE   a.DATA >= :WDTINI  
    AND a.DATA <= :WDTFIM
    AND a.idvendedor >= :WVENDEDORINI 
    AND a.idvendedor <= :WVENDEDORFIM
    AND (   (   (
                        a.IDOPERACAO = '01' 
                    OR  a.IDOPERACAO = '14' 
                    OR  a.IDOPERACAO = '17' 
                    OR  a.IDOPERACAO = '28'            
                    OR  a.IDOPERACAO = '30'     
                    OR  a.IDOPERACAO = '93' 
                    OR  a.IDOPERACAO = '94' 
                    OR  a.IDOPERACAO = '91'
                )
                AND a.NOPER > 5000
            ) 
            OR  (       
                        a.IDOPERACAO = '95' 
                    OR  a.IDOPERACAO = '96' 
                    OR  a.IDOPERACAO = '97'
                )
        )
GROUP BY 1  
ORDER BY 1
    
asked by anonymous 05.07.2018 / 17:54

0 answers