Comparison in CASE WHEN 1 > 0

[ijitnoin]

SELECT qryBatchMFGInvoice.MFG, qryBatchMFGInvoice.INVOICE, qryBatchMFGInvoice.MaxOfInvoiceDate AS InvoiceDate, qryBatchMFGInvoice.InvoiceStatus,
qryBatchMFGInvoice.SumOfINV_TOTAL AS [Total Invoice], qryBankMFGInvoice.SumOfINV_TOTAL AS [Total Paid],
qryBatchMFGInvoice.MaxOfInvoiceDate + 30 AS DueDate, tblManufacturerInfo.MFGNAME, CASE WHEN (0 < (GetDate()
- qryBatchMFGInvoice.MaxOfInvoiceDate)) AND ((GetDate() - qryBatchMFGInvoice.MaxOfInvoiceDate) < 31) THEN ([Total Invoice] - [Total Paid]) ELSE (0)
END AS [Current], CASE WHEN (0 < (GetDate() - [DueDate])) AND ((GetDate() - [DueDate]) < 31) THEN ([Total Invoice] - [Total Paid]) ELSE (0)
END AS [1-30 DAYS], CASE (30 < (GetDate() - [DueDate])) AND ((GetDate() - [DueDate]) < 46) THEN ([Total Invoice] - [Total Paid]) END (0) AS [31-45 DAYS],
CASE WHEN (45 < (GetDate() - [DueDate])) AND ((GetDate() - [DueDate]) < 61) THEN ([Total Invoice] - [Total Paid]) ELSE (0) END AS [46-60 DAYS],
CASE WHEN (60 < (GetDate() - [DueDate])) AND ((GetDate() - [DueDate]) < 91) THEN ([Total Invoice] - [Total Paid]) ELSE (0) END AS [61-90 DAYS],
CASE WHEN (90 < (GetDate() - [DueDate])) THEN ([Total Invoice] - [Total Paid]) ELSE (0) END AS [Over 90 DAYS],
([1-30 DAYS] + [31-45 DAYS] + [46-60 DAYS] + [61-90 DAYS] + [Over 90 DAYS]) AS [INVOICE TOTAL]
FROM qryBankMFGInvoice INNER JOIN
(tblManufacturerInfo INNER JOIN
qryBatchMFGInvoice ON tblManufacturerInfo.MFG = qryBatchMFGInvoice.MFG) ON (qryBankMFGInvoice.INVOICE = qryBatchMFGInvoice.INVOICE) AND
(qryBankMFGInvoice.MFG = qryBatchMFGInvoice.MFG)
WHERE (((qryBatchMFGInvoice.InvoiceStatus) = 'O'))
ORDER BY qryBatchMFGInvoice.INVOICE;

 

[vongrunt]

42

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jbenson001]

What is your question?

You need to use datediff() when comparing to an integer.

CASE WHEN (0 < datediff(GetDate() - DueDate) )

 

[ijitnoin]

I want to know if anyone can tell me why the < or > is not accept or call an error in a SELECT statement like the following:

CASE WHEN (0 < 5 THEN 'TRUE' ELSE 'FALSE' END

 

[vongrunt]

Because of ( ?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[gmmastros]

You're missing a WHEN after one of your cases and you have [!]END (0) AS [31-45 DAYS], [/!]

It should probably by Else (0) End As....

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom