blah blah blah is here! blah blah » Close

0
andy_olsen
60
364 views

SQL Statement

up0down
link

Hi

I'm doing a winform app with sql server 2008 in VS2008, where a company has parking lots for rent. It works like this, when a car arrives it gets a date of arrival. If this car never has been invoiced before my sql statement should get the arrival date. If it has been invoiced before it should take the highest date (Max(invoiceday)) and only return that record/car.
In my case it works fine until I have done invoice no 2, then it returns both records for that car instead of just the highest.

"SELECT * FROM Cars LEFT JOIN Invoice ON Cars.carId=Invoice.carId WHERE Cars.Arrival <= '" & date & "' AND Invoice.Invoiceday IS NULL OR (SELECT MAX(Invoice.Invoiceday) FROM Invoice WHERE Invoice.carId=Cars.carId) <= '" & date & "' "

sql max() winform
  • asked one year ago
add comment
last answered one year ago

2 answers

Sort:
up1down
link

why you do not do change statement using
order by Invoiceday desc (that will give you order from highest date)
and after limit result on one row?



just an idea:)

add comment
up0down
link

I haven't tested it, but this should get what you want:

SELECT * FROM Cars
LEFT JOIN Invoice ON Cars.carId=Invoice.carId
HAVING Cars.Arrival IS NULL OR Cars.Arrival = MAX(Invoice.Invoiceday)

  • answered one year ago by:

    foamy
    2499
add comment
Feedback