Here is data in my table <<
Please learn to post DDL instead of pictures. What you have posted is actually not a table at all if I understand things. Let us begin with a look at ISO 8601 standards for temporal values. They have a week_date; it uses the four digit year a week number within the year that ranges from 01 to 52 or 53, and the day of the week (one = Monday, etc.)
What you have done is display a spreadsheet or report, and not a table and not even in first normal form. Here is a normalized table
CREATE TABLE Sites
(site_id CHAR(3) NOT NULL,
reading_week CHAR(7) NOT NULL
CHECK(reading_week LIKE '[0-9][0-9][0-9]W[0-5][0-9]'),
reading_type CHAR(1) NOT NULL CHECK (reading_type IN ('A', 'B')),
PRIMARY KEY (site_id, reading_week, reading_type),
reading_value INTEGER NOT NULL CHECK(reading_value >= 0)
I will let you do all the copying and pasting to get the data out of the spreadsheet and into the corrected table.
I want a query that will look at all week1a,week2a,week3a,week4a,week5a and so forth then figure out which is the last column which has value greater than 0 for example SiteId 101 It is Week2A. SiteId 288 it is Week5A. SiteId 468 week3A. <<
But if you look at your data,and the week 5B column. Those values are clearly the last nonzero values for every site. Can we get a better specification?