CALCULATION IN CASE WHEN THEN statement is not working!

[ijitnoin]

SELECT TOP 200 qryBatchMFGInvoice.MFG, qryBatchMFGInvoice.INVOICE, qryBatchMFGInvoice.MaxOfInvoiceDate AS InvoiceDate,
qryBatchMFGInvoice.InvoiceStatus, dbo.qryBatchMFGInvoice.SumOfINV_TOTAL AS [Total Invoice],
dbo.qryBankMFGInvoice.SumOfINV_TOTAL AS [Total Paid], qryBatchMFGInvoice.MaxOfInvoiceDate + 30 AS [DueDate], tblManufacturerInfo.MFGNAME,
CASE WHEN 0 < datediff(dd, GetDate(), qryBatchMFGInvoice.MaxOfInvoiceDate) AND datediff(dd, GetDate(), qryBatchMFGInvoice.MaxOfInvoiceDate)
< 31 THEN dbo.qryBatchMFGInvoice.SumOfINV_TOTAL - qryBankMFGInvoice.SumOfINV_TOTAL ELSE 0 END AS [Current], CASE WHEN 0 < datediff(dd,
GetDate(), (qryBatchMFGInvoice.MaxOfInvoiceDate + 30)) AND datediff(dd, GetDate(), (qryBatchMFGInvoice.MaxOfInvoiceDate + 30))
< 31 THEN dbo.qryBatchMFGInvoice.SumOfINV_TOTAL - qryBankMFGInvoice.SumOfINV_TOTAL ELSE 0 END AS [1-30 DAYS],
CASE WHEN 30 < datediff(dd, GetDate(), (qryBatchMFGInvoice.MaxOfInvoiceDate + 30)) AND datediff(dd, GetDate(),
(qryBatchMFGInvoice.MaxOfInvoiceDate + 30))
< 46 THEN dbo.qryBatchMFGInvoice.SumOfINV_TOTAL - qryBankMFGInvoice.SumOfINV_TOTAL ELSE 0 END AS [31-45 DAYS],
CASE WHEN 45 < datediff(dd, GetDate(), (qryBatchMFGInvoice.MaxOfInvoiceDate + 30)) AND datediff(dd, GetDate(),
(qryBatchMFGInvoice.MaxOfInvoiceDate + 30))
< 61 THEN dbo.qryBatchMFGInvoice.SumOfINV_TOTAL - qryBankMFGInvoice.SumOfINV_TOTAL ELSE 0 END AS [46-60 DAYS],
CASE WHEN 60 < datediff(dd, GetDate(), (qryBatchMFGInvoice.MaxOfInvoiceDate + 30)) AND datediff(dd, GetDate(),
(qryBatchMFGInvoice.MaxOfInvoiceDate + 30))
< 91 THEN dbo.qryBatchMFGInvoice.SumOfINV_TOTAL - qryBankMFGInvoice.SumOfINV_TOTAL ELSE 0 END AS [61-90 DAYS],
CASE WHEN 90 < datediff(dd, GetDate(), (qryBatchMFGInvoice.MaxOfInvoiceDate + 30))
THEN (dbo.qryBatchMFGInvoice.SumOfINV_TOTAL - dbo.qryBankMFGInvoice.SumOfINV_TOTAL) ELSE 0 END AS [Over 90 DAYS],
(CASE WHEN 0 < datediff(dd, GetDate(), (qryBatchMFGInvoice.MaxOfInvoiceDate + 30)) AND datediff(dd, GetDate(),
(qryBatchMFGInvoice.MaxOfInvoiceDate + 30))
< 31 THEN dbo.qryBatchMFGInvoice.SumOfINV_TOTAL - qryBankMFGInvoice.SumOfINV_TOTAL ELSE 0 END) + (CASE WHEN 30 < datediff(dd, GetDate(),
(qryBatchMFGInvoice.MaxOfInvoiceDate + 30)) AND datediff(dd, GetDate(), (qryBatchMFGInvoice.MaxOfInvoiceDate + 30))
< 46 THEN dbo.qryBatchMFGInvoice.SumOfINV_TOTAL - qryBankMFGInvoice.SumOfINV_TOTAL ELSE 0 END) + (CASE WHEN 45 < datediff(dd, GetDate(),
(qryBatchMFGInvoice.MaxOfInvoiceDate + 30)) AND datediff(dd, GetDate(), (qryBatchMFGInvoice.MaxOfInvoiceDate + 30))
< 61 THEN dbo.qryBatchMFGInvoice.SumOfINV_TOTAL - qryBankMFGInvoice.SumOfINV_TOTAL ELSE 0 END) + (CASE WHEN 60 < datediff(dd, GetDate(),
(qryBatchMFGInvoice.MaxOfInvoiceDate + 30)) AND datediff(dd, GetDate(), (qryBatchMFGInvoice.MaxOfInvoiceDate + 30))
< 91 THEN dbo.qryBatchMFGInvoice.SumOfINV_TOTAL - qryBankMFGInvoice.SumOfINV_TOTAL ELSE 0 END) + (CASE WHEN 90 < datediff(dd, GetDate(),
(qryBatchMFGInvoice.MaxOfInvoiceDate + 30)) THEN dbo.qryBatchMFGInvoice.SumOfINV_TOTAL - qryBankMFGInvoice.SumOfINV_TOTAL ELSE 0 END)
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 dbo.qryBatchMFGInvoice.[INVOICE]

There should be something in the [Over 90] column from qryBatchMFGInvoice.SumOfINV_TOTAL - qryBankMFGInvoice.SumOfINV_TOTAL because there is data in both fields. Also, their should be data in the [INVOICE TOTAL] column.

Help Please! Thanks in advance!

 

[SQLBill]

I notice this line from the CASE WHEN 90 < (etc) is different from the rest...

THEN (dbo.qryBatchMFGInvoice.SumOfINV_TOTAL - dbo.qryBankMFGInvoice.SumOfINV_TOTAL) ELSE

The parenthesis don't exist in the other lines, nor does the second "dbo" (highlighted in red).

It shouldn't make a difference, but be consistent.

-SQLBill

Posting advice: FAQ481-4875

 

[ijitnoin]

I realized that in the datediff(dd,GetDate(),A) statement the GetDate() must be the second date; meaning diff(dd,A,GetDate()) was the proper way to get the diference. Otherwise, the number of days will be negative. That is why the [Over 90 Days] field had 0 from the ELSE rather that the THEN value. I created a simple query with the datediff AS a column. Thanks for your help!