SQL statement

rbuser

Hi

I need to do some calculations in my sql statement that will return me
values based in whether a date falls wintin a certain month. I have tried
to do this with calculations when reating the query. What I would like it to
do is basically set out in the SQL statement below :-

The lines affected are marked by ***
I need a month type function.


SELECT Customer.Code, Customer.Name,
CInvoice.DateInvoice, CInvoice.DueInc,
CInvoice.refMasterStatus,
CPayment.PaidInc, CPayment.DistributedInc,
CPayment.Distributed, CCredit.CreditInc,
CCredit.DistributedInc DistributedInc_2,
CCredit.Distributed Distributed_2,
If(Month(DateInvoice) = Month(Date(),Sum(DueInc))
If_Month_DateInvoice_Mont, ***
If(Month(DateInvoice) = Month(Date()-1),Sum(DueInc))
If_Month_DateInvoice_Mo_2, ***
If(Month(DateInvoice) = Month(Date()-2),Sum(DueInc))
If_Month_DateInvoice_Mo_3, ***
If(Month(DateInvoice) > Month(Date()-2),Sum(DueInc))
If_Month_DateInvoice_Mo_4, ***
SUM(CPayment.PaidInc) SUM_CPayment_PaidInc,
SUM(CPayment.DistributedInc) SUM_CPayment_DistributedI,
SUM(CCredit.CreditInc) SUM_CCredit_CreditInc,
SUM(CCredit.DistributedInc) SUM_CCredit_DistributedIn,
((SumPaidInc-SumPDistInc)+(SumCreditInc-SumCDistInc),Undistributed
SumPaidInc_SumPDistInc_S,
(IncCurrent+Inc30+Inc60+Inc90,IncTotal) IncCurrent_Inc30_Inc60_I
FROM Customer Customer
LEFT OUTER JOIN CInvoice CInvoice ON
(CInvoice.Customer = Customer.Code)
LEFT OUTER JOIN CPayment CPayment ON
(CPayment.Customer = Customer.Code)
LEFT OUTER JOIN CCredit CCredit ON
(CCredit.Customer = Customer.Code)
WHERE ( CInvoice.DueInc <> 0 )
AND ( CPayment.Distributed <> TRUE )
AND ( CCredit.Distributed <> TRUE )
AND (
((SumPaidInc-SumPDistInc)+(SumCreditInc-SumCDistInc),Undistributed <> 0 )
AND ( (IncCurrent+Inc30+Inc60+Inc90,IncTotal) <> 0 )
GROUP BY Customer.Code, Customer.Name.

Thank you if you can help.

Allan Ryan

nicocizik

Hi Allan,

Are you trying to extract the actual month from a DataTime value? If so,
you can try using the EXTRACT() method in your SQL statement. Something
like EXTRACT(Month FROM Source) to use it. You might also want to check out
the Autosearch capabilities of ReportBuilder once you get this working.
Using this, you could display only the months you choose in your report.
There are Autosearch demos available in the \RBuilder\Demos\Autosearch\...
directory.

--