SELECT

F8FPDateMonthFR(PerYYYYMM) ,

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID = PerID AND EcrCpt = '6070001'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID = PerID AND EcrCpt = '6070002'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID = PerID AND EcrCpt = '6070003'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID = PerID AND EcrCpt = '6070004'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID = PerID AND EcrCpt = '6070005'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID = PerID AND EcrCpt = '6070006'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID = PerID AND EcrCpt = '6070007'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID = PerID AND EcrCpt = '6070008'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID = PerID AND EcrCpt = '6070011'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID = PerID AND EcrCpt = '6070015'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID = PerID AND EcrCpt = '6070017'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID = PerID AND EcrCpt = '6070016'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID = PerID AND EcrCpt = '6070010'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID = PerID AND EcrCpt = '6071000'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID = PerID AND EcrCpt = '6072000')


FROM DBA.PER , DBA.EXE
WHERE PerGExeID = ExeID 

AND ExeDebDate <= (Current_timestamp) AND ExeFinDate >= (Current_timestamp)





SELECT

F8FPDateMonthFR(PerYYYYMM) ,

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6070001'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6070002'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6070003'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6070004'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6070005'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6070006'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6070007'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6070008'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6070011'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6070015'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6070017'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6070016'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6070010'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6071000'),

(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6072000')


FROM DBA.PER , DBA.EXE
WHERE PerGExeID = ExeID AND ExeDebDate <= (Current_timestamp) AND ExeFinDate >= (Current_timestamp)











DROP TABLE IF EXISTS #resultFP;
CREATE TABLE #resultFP (
  colno		DECIMAL(22,7),
  colnom  	NVARCHAR(80),
  col1    	DECIMAL(22,7)
);


INSERT INTO #resultFP (colno, colnom, col1)
SELECT
NUMBER(),
F8FPDateMonthFR(PerYYYYMM),
(SELECT SUM(EcrDebit-EcrCredit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6070004')
FROM DBA.PER , DBA.EXE
WHERE PerGExeID = ExeID AND ExeDebDate <= (Current_timestamp) AND ExeFinDate >= (Current_timestamp)
;

INSERT INTO #resultFP (colno, colnom, col1)
SELECT
NUMBER(),
F8FPDateMonthFR(PerYYYYMM) ,
(SELECT SUM(EcrCredit-EcrDebit) FROM DBA.ECR WHERE EcrGPerID <= PerID AND EcrGExeID = ExeID AND PerFinDate <= (Current_timestamp) AND EcrCpt = '6070004')
INTO #resultFP1
FROM DBA.PER , DBA.EXE
WHERE PerGExeID = ExeID
AND ExeDebDate <= (DATEADD(YEAR,-1,Current_timestamp)) AND ExeFinDate >= (DATEADD(YEAR,-1,Current_timestamp))
;

SELECT colnom, SUM(Col1) FROM #resultFP
GROUP BY colnom, colno 
ORDER BY colno;








SELECT

DISTINCT XdaMonth,
(SELECT SUM(EcrDebit-EcrCredit)
FROM DBA.ECR 
WHERE EcrGExeID = 7 AND MONTH(EcrDate) = XdaMM AND EcrCpt = '6070001')

FROM XDA

WHERE Date(XdaDate) >= '2016-10-01' AND Date(XdaDate) <= '2018-09-30'


