Select last column amongst group of columns where value>0

[Mary Phelps]

Here is data in my table

SiteId Week1A Week1B Week2A Week2B Week3A Week3B Week4A Week4B Week5A Week5B
101 0 8 7 9 0 6 0 9 0 9
288 8 8 0 7 0 7 0 8 6 6
468 1 8 6 9 8 6 0 9 0 9
768 0 8 0 7 0 7 6 8 0 6

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.

then after the last column is figured out sum up values of weeks remaing after that so for example siteid101 last column is week2a so i want a sum of week3b,week4b and week5b.

[--CELKO--]

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 '[12][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?

[m***@gmail.com]

Post by Mary Phelps
Here is data in my table
SiteId Week1A Week1B Week2A Week2B Week3A Week3B Week4A Week4B Week5A Week5B
101 0 8 7 9 0 6 0 9 0 9
288 8 8 0 7 0 7 0 8 6 6
468 1 8 6 9 8 6 0 9 0 9
768 0 8 0 7 0 7 6 8 0 6
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.
then after the last column is figured out sum up values of weeks remaing after that so for example siteid101 last column is week2a so i want a sum of week3b,week4b and week5b.

Your presentation is not clear. For example row SiteID:101 Week5B:9 is greater than > 0 and if we assume that order starts from Week1A then it would be the last column with a value greater than zero.