Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31311

Re: Select last column amongst group of columns where value>0

Show all headers | View raw

>> 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?

Back to microsoft.public.sqlserver.programming | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Select last column amongst group of columns where value>0 Mary Phelps <icanhelp33@gmail.com> - 2016-12-01 08:21 -0800
  Re: Select last column amongst group of columns where value>0 --CELKO-- <jcelko212@earthlink.net> - 2016-12-02 10:07 -0800
  Re: Select last column amongst group of columns where value>0 maizesheng@gmail.com - 2016-12-23 16:40 -0800

csiph-web