SQLTeam.com | Weblogs | Forums

SQL Date


#1

I need help with this please

Write a SELECT query that returns the vendorName and the SUM of the balanceDue (as we defined it above) as sumBalanceDue for all invoices with a past due balance (DATEDIFF < 0 and balanceDue > 0) GROUP BY vendorName (of course) and sort by sumBalanceDue with the greatest first.

balance due for an invoice is the invoiceTotal - paymentTotal - creditTotal.

This is what I did
ELECT VendorName, SUM(invoiceTotal - paymentTotal - creditTotal) AS BalanceDue
FROM Invoices, Vendors
WHERE InvoiceDueDate <= DATEDIFF
AND balanceDue > 0
GROUP BY VendorName
ORDER BY SUM InvoiceTotal - paymentTotal - creditTotal AS BalanceDue


#2

is invalid syntax. remove the AS BalanceDue on the end.

Try to use ANSI

FROM Invoices
JOIN Vendors
ON ...

Also, you have no join criteria, so you'll have a Cartesian product

IS DATEDIFF a column? It's also a function in SQL Server. If you want to use the datediff function, then it needs parameters

image

If DATEDIFF is a column name, you should choose a different one to avoid conflict with the function name.