Click here to Skip to main content
15,614,943 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:

Discipline  Activity    Area    Month   Week1 TOn       Week2 TOn       Week3 TON       Week4 TON
S               A       2       June    10              20      
S               B       2       June    15                              25              

I need to get the latest ton value using a query.

For eg:- For activity A/June we need the output ==10
For activity B/June we need the output ==25

Kindly provide a query to fetch the details.

What I have tried:

Using case with and but its not working kindly do the needful
Updated 21-Jun-21 22:39pm
Maciej Los 22-Jun-21 2:35am    
Why 10 and 25? Shoudn't be 30 and 40 accordingly?

Assuming you want the last non-null column from week 1 to week 4, use the COALESCE[^] function:
    Coalesce([Week4 TON], [Week3 TON], [Week2 TOn], [Week1 TOn]) As WeekTON
NB: You should avoid using spaces and other special characters in table and column names. Since your column names include spaces, you need to enclose then in square brackets everywhere you use them.
Share this answer
Maciej Los 22-Jun-21 4:41am    
"It's not working" is one of the most useless problem descriptions we get: it tells us absolutely nothing about the problem. We don't know if you get an error message, or the wrong data, or even that that code compiles successfully!
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
So tell us what happens when you run that code, what you expected to happen, how you checked what happened. Help us to help you!

But Google is your friend: Be nice and visit him often. He can answer questions a lot more quickly than posting them here ... 10 seconds with Google gave me solutions: SQL max of four columns - Google Search[^]
Including this: sql server - SQL MAX of multiple columns? - Stack Overflow[^]

In future, please try to do at least basic research yourself, and not waste your time or ours.
Share this answer
Member 11658469 22-Jun-21 2:11am    
Col1 Col2 Col3 Col4
10 5

I need to get the output as 5 because col3 is having the last value.Kindly help to fetch the output like this.
OriginalGriff 22-Jun-21 2:41am    
Follow the link(s) I gave you ...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900