Jump to content

RenatoReis

Members
  • Posts

    1
  • Joined

  • Last visited

RenatoReis's Achievements

Newbie

Newbie (1/7)

0

Reputation

  1. 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
×
×
  • Create New...