July 30, 2008 at 11:05 am
Consider the following QUERY:
SELECT LastName, FirstName, COUNT(TripID) TripTotal
FROM Clients
JOIN Trips on tripClientID = ClientID
JOIN Accounts on accountClientID = ClientID
WHERE (tripDate BETWEEN '06-01-08' AND '06-30-08') AND (accountID = 501)
GROUP BY LastName, FirstName
ORDER BY LastName, FirstName
the code works just like I want.
it will return the client names and the total amount of trips the person took for the month for the given account.
However I NOW need to specify in the same query, the date of the first trip that client took in the month, and the date of the last trip the client had in the month.
Other than running a cursor, is there a way from a single select statement to do this?
July 30, 2008 at 11:13 am
It would be easier to help you if you would provide the DDL (create statements) for the tables involved, sample data for the tables (in the form of insert statements so the tables can be populated using cut, paste, and execute in Management Studio), and the expected results from the query based on the sample data (to check results against).
I can't really tell what you are asking for just from your post alone.
😎
July 30, 2008 at 12:02 pm
jkiley (7/30/2008)
Consider the following QUERY:
SELECT LastName, FirstName, COUNT(TripID) TripTotal
FROM Clients
JOIN Trips on tripClientID = ClientID
JOIN Accounts on accountClientID = ClientID
WHERE (tripDate BETWEEN '06-01-08' AND '06-30-08') AND (accountID = 501)
GROUP BY LastName, FirstName
ORDER BY LastName, FirstName
the code works just like I want.
it will return the client names and the total amount of trips the person took for the month for the given account.
However I NOW need to specify in the same query, the date of the first trip that client took in the month, and the date of the last trip the client had in the month.
Other than running a cursor, is there a way from a single select statement to do this?
Actually I solved it very simply:
SELECT LastName, FirstName, COUNT(TripID) TripTotal, MIN(TripDate), MAX(TripDate)
...and because the MIN/MAX is a function, I didn't have to add them to the GROUP BY
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply