Jump to content

Grouping Accounts


RenatoReis

Recommended Posts

Hello everybody.
Need to make a query that will allow me to group total monthly financial moves by financial account. I'll explain better: I have a dbf table that receives entries in financial accounts and need a query that lists the period of one year with the total of each account grouped by months. Come on.
In the form, I will provide the desired period.
table SCCAMOF
MOFDATA, MOFCONTAB, MOFVALOR
Layout of the consultation
Account Jul14 Jun14 May14 Apr14 Dec13 Nov13 Jan14 Mar14 Feb14 Oct13 Sep13 Aug13
303 1020.00 200.33 540.99 ...
304 876.99 989.55 ...
...
I was trying this code...
close;
sql.Clear;
sql.add('SELECT MOFCONTAB, ');
sql.Add('JAN = SUM(CASE WHEN MONTH([MOFDATA])=1 THEN [MOFVALOR] ELSE 0.00 END),');
sql.Add('FEV = SUM(CASE WHEN MONTH([MOFDATA])=2 THEN [MOFVALOR] ELSE 0.00 END),');
sql.Add('MAR = SUM(CASE WHEN MONTH([MOFDATA])=3 THEN [MOFVALOR] ELSE 0.00 END),');
sql.Add('ABR = SUM(CASE WHEN MONTH([MOFDATA])=4 THEN [MOFVALOR] ELSE 0.00 END),');
sql.Add('MAI = SUM(CASE WHEN MONTH([MOFDATA])=5 THEN [MOFVALOR] ELSE 0.00 END),');
sql.Add('JUN = SUM(CASE WHEN MONTH([MOFDATA])=6 THEN [MOFVALOR] ELSE 0.00 END),');
sql.Add('JUL = SUM(CASE WHEN MONTH([MOFDATA])=7 THEN [MOFVALOR] ELSE 0.00 END),');
sql.Add('AGO = SUM(CASE WHEN MONTH([MOFDATA])=8 THEN [MOFVALOR] ELSE 0.00 END),');
sql.Add('SET = SUM(CASE WHEN MONTH([MOFDATA])=9 THEN [MOFVALOR] ELSE 0.00 END),');
sql.Add('OUT = SUM(CASE WHEN MONTH([MOFDATA])=10 THEN [MOFVALOR] ELSE 0.00 END),');
sql.Add('NOV = SUM(CASE WHEN MONTH([MOFDATA])=11 THEN [MOFVALOR] ELSE 0.00 END),');
sql.Add('DEZ = SUM(CASE WHEN MONTH(MOFDATA)=12 THEN MOFVALOR ELSE 0.00 END) ');
sql.add('from SCCAMOF ');
sql.Add('where MOFDATA between :DATA_INI and :DATA_FIN GROUP BY MOFCONTAB, MOFDATA ORDER BY YEAR(MOFDATA)');
Params.ParamByName('DATA_INI').asDateTime := edtInicial.Date;
Params.ParamByName('DATA_FIN').asDateTime := edtFinal.Date;
Open;

...But I have this message: expected lexical element not found: FROM You are missing the keyword FROM after the color definitions in your SELECT statement

Could anyone help ?? Thank you

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...