Posted by Erland Sommarskog on 06/21/06 22:00

Alex (samalex@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: SQL server replication from non-clustered database servers
• Prev in forum: Re: Database name with point in it (mail.archive.mdf) not recognised in query analyser of SQL 2000
• Thread view: Re: Help with SQL Statement to find first and last populated field in string of fields

[Reply to this message]