Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Need help in Orange colored part, how to do this sql part.
CurrentData:
SQL
SELECT
CURDATE() as Date1,
Customer_id,
Customer_Sub,
Customer_Location,
Invoice_No,
Invoice_Amount,
If(GETDATE() - [DueDate] >= 30,Count(Invoice_No) as NbOverdueInv,
If(GETDATE() - [DueDate] >= 30,Sum(Invoice_Amount) as NbOverdueInv,
Count(Invoice_No) as NumberofInvoices,
Sum(Invoice_Amount) as TotalAmt
FROM Master.dbo Group by Customer_id,Customer_Sub,Customer_Location, Invoice_No,Invoice_Amount;
STORE CurrentData into CurrentData.qvd(qvd);
Regards,
Chriss
Hi,
Close your if condition brackets. and Field name must be unique.
Else
use below mentioned code.
Ex:
if(DATEDIFF(day,Now(),[DueDate])>= 30,Count(Invoice_No)) as NbOverdueInvCount,
if(DATEDIFF(day,Now(),[DueDate])>= 30,Sum(Invoice_Amount)) as NbOverdueInvSum,
TEST:
Load *,
If(GETDATE() - [DueDate] >= 30,Count(Invoice_No) as NbOverdueInv,
If(GETDATE() - [DueDate] >= 30,Sum(Invoice_Amount) as NbOverdueInv;
SQL
SELECT *
FROM Master.dbo
CurrentData:
load
Customer_id,
Customer_Sub,
Customer_Location,
Invoice_No,
Invoice_Amount,
NbOverdueInv,
NbOverdueInv,
Count(Invoice_No) as NumberofInvoices,
Sum(Invoice_Amount) as TotalAmt
FROM TEST Group by Customer_id,Customer_Sub,Customer_Location, Invoice_No,Invoice_Amount,NbOverdueInv,NbOverdueInv;
STORE CurrentData into CurrentData.qvd(qvd);
Hi Kumar,
Thanks for your reply, as I am getting the error of "Incorrect syntax near the keyword 'if'."
In the SQL select is not possible to use the IF conditon.
Regards
Chriss
CASE WHEN GETDATE() - [DueDate] >= 30 THEN Count(Invoice_No) END as NbOverdueInv,
CASE WHEN GETDATE() - [DueDate] >= 30 THEN Sum(Invoice_Amount) END as NbOverdueInv,