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