Help with SQL Statement to find first and last populated field in string of fields

[Alex]

Hello all,

I'm at a loss on how to do this. We're using MS SQL 2000 Server and I
have a list of fields I need to find the first and last entry for.
Here's an example of the table:

Number - VarChar(10)
Jan - Int
Feb - Int
Mar - Int
Apr - Int
May - Int
June - Int

ANd it'll look something like this:
Number Jan Feb Mar Apr May Jun
1232 200 190 192 201 203 205
4432 433 322 456
5423 754 694 665

And I need to create a table with this:

Number First Last Difference
1232 200 205 5
4432 433 456 23
5423 754 665 -89

I'm not sure if this'll copy over correctly, but I have gaps in the
data so I can't just say Jun-Jan, but I need tofind the first field
with data and last field with data, then find the difference of these.
Suggestions? Is there a loop or something I can do in TSQL that'll do
this? I'd like to do this in Query Analyzer since it's just a one-time
report. Thanks --

Alex

[Erland Sommarskog]

Alex (sa*****@gmail.com) writes:

I'm at a loss on how to do this. We're using MS SQL 2000 Server and I
have a list of fields I need to find the first and last entry for.
Here's an example of the table:

Number - VarChar(10)
Jan - Int
Feb - Int
Mar - Int
Apr - Int
May - Int
June - Int

ANd it'll look something like this:
Number Jan Feb Mar Apr May Jun
1232 200 190 192 201 203 205
4432 433 322 456
5423 754 694 665

And I need to create a table with this:

Number First Last Difference
1232 200 205 5
4432 433 456 23
5423 754 665 -89

I'm not sure if this'll copy over correctly, but I have gaps in the
data so I can't just say Jun-Jan, but I need tofind the first field
with data and last field with data, then find the difference of these.

For the example table, this should work:

SELECT Number, first, last, difference = first - last
FROM (SELECT Number,
first = coalesce(Jan, Feb, Mar, Apr, May, Jun),
last = coalesce(Jun, May, Apr, Mar, feb, Jan)
FROM tbl) as s
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Alex]

Erland Sommarskog wrote:

For the example table, this should work:

SELECT Number, first, last, difference = first - last
FROM (SELECT Number,
first = coalesce(Jan, Feb, Mar, Apr, May, Jun),
last = coalesce(Jun, May, Apr, Mar, feb, Jan)
FROM tbl) as s

That's exactly what I'm looking for! I didn't know this function
existed, even after reading through several books and online resources.
Thanks :)

Alex