Re: Fetching Records...

[John Noble]

Sorry....

SELECT
salesHistoryArchive.customerID,
customers.name as customerName,
salesHistoryArchive.despatchDate,
salesHistoryArchive.invNum,
salesHistoryArchive.productID,
products.description as productDescription,
CASE WHEN notr = "10" THEN salesHistoryArchive.quantityDespatched ELSE salesHistoryArchive.quantityDespatched *-1 END as quantity,
CASE WHEN notr = "10" THEN salesHistoryArchive.lineWeight ELSE salesHistoryArchive.lineWeight * -1 END as lineWeight,
salesHistoryArchive.price,
CASE WHEN notr = "10" THEN salesHistoryArchive.lineValue - salesHistoryArchive.discountValue - salesHistoryArchive.discount2Value ELSE (salesHistoryArchive.lineValue - salesHistoryArchive.discountValue - salesHistoryArchive.discount2Value) * -1 END as lineValue
FROM
salesHistoryArchive
INNER JOIN customers ON customers.customerID = salesHistoryArchive.customerID
INNER JOIN products ON products.productID = salesHistoryArchive.productID
WHERE
notr = "10" AND
salesHistoryArchive.MSCFlag = "T" AND
despatchDate BETWEEN p_fromDate AND p_toDate AND
salesHistoryArchive.transactionType <> "WL"

Thanks,

John