sql Help

I am trying to get a list of cars and its sessionID with latest classes that was done before today

SELECT t.sessionKey,T.carKey 
FROM (
	SELECT  max(SU.sessionStart) as sessionStart,S.sessionkey
	FROM sessionMap SM 
	inner join session S on S.sessionKey = SM.sessionKey
	inner join sessionUnit SU on SU.sessionKey = S.sessionKey
	inner join car C on C.carkey = S.carKey
	WHERE  SM.sessionKey > 0 and c.carActiveIn = 1
	and CONVERT(date,SU.sessionStart,120) <= GETDATE()
	GROUP BY S.sessionKey
)T
inner join session S on S.sessionKey = t.sessionKey

Open in new window


SO it should looks like this
carKey     SessionKey
1                    111
2                     333
3                    444
LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
in your inner query replace
and CONVERT(date,SU.sessionStart,120) <= GETDATE()
with:
and CONVERT(date,SU.sessionStart,120) <
--today at midnight
DateAdd(Day, Datediff(Day,0, GetDate() ), 0)

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
T.carKey  should be S.carKey .
What's the data type for SU.sessionStart column?
erikTsomikSystem Architect, CF programmer Author Commented:
The  SU.sessionStart is the type dateTime. Bit it is unrelated to my issue. I am trying to get the last session mileage for each car.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Anthony PerkinsCommented:
Something like this perhaps:
SELECT  T.sessionKey,
        T.carKey
FROM    session S
        INNER JOIN car C ON C.carkey = S.carKey
        INNER JOIN sessionUnit SU2 ON SU2.sessionKey = S.sessionKey
        INNER JOIN (SELECT  SM.sessionkey,
                            MAX(SU.sessionStart) AS sessionStart
                    FROM    sessionMap SM
                            INNER JOIN sessionUnit SU ON SU.sessionKey = SM.sessionKey
                    WHERE   SM.sessionKey > 0
                            AND CONVERT(date, SU.sessionStart, 120) <= GETDATE()
                    GROUP BY SM.sessionKey
                   ) T ON S.sessionKey = T.sessionKey
                          AND SU2.sessionStart = T.sessionStart
WHERE   C.carActiveIn = 1;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
erikTsomik, do you still need help with this question?
Anthony PerkinsCommented:
I am sorry you did not like my answer.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.