Getting most recent value

Hello All,
I have a table with 6 column; w1-5 and month. Month column should be
equal to the most recent column within w1...to..w5 column. If there is
a value on w5 then month = w5, else w4...till w1.
There can be null, blank, 0 or values on any of the w columns.
WEEK1 WEEK2 WEEK3 WEEK4 WEEK5 MONTH
==== ===== ==== ===== ===== =====
4 7 NULL 9 0 9
2 6 0 NULL NULL 6
1 0 NULL 5 0 5
Thanks,
A.
SQL Server DBAWhy on Earth would you design something like this?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ada" <Ada@.discussions.microsoft.com> wrote in message
news:59815DE0-B812-4FB6-8C25-D547ACB755C6@.microsoft.com...
Hello All,
I have a table with 6 column; w1-5 and month. Month column should be
equal to the most recent column within w1...to..w5 column. If there is
a value on w5 then month = w5, else w4...till w1.
There can be null, blank, 0 or values on any of the w columns.
WEEK1 WEEK2 WEEK3 WEEK4 WEEK5 MONTH
==== ===== ==== ===== ===== =====
4 7 NULL 9 0 9
2 6 0 NULL NULL 6
1 0 NULL 5 0 5
Thanks,
A.
SQL Server DBA|||It's the business rule.
Month value should be the latest w's value.
A.
--
SQL Server DBA
"Tom Moreau" wrote:

> Why on Earth would you design something like this?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Ada" <Ada@.discussions.microsoft.com> wrote in message
> news:59815DE0-B812-4FB6-8C25-D547ACB755C6@.microsoft.com...
> Hello All,
> I have a table with 6 column; w1-5 and month. Month column should be
> equal to the most recent column within w1...to..w5 column. If there
is
> a value on w5 then month = w5, else w4...till w1.
> There can be null, blank, 0 or values on any of the w columns.
> WEEK1 WEEK2 WEEK3 WEEK4 WEEK5 MONTH
> ==== ===== ==== ===== ===== =====
> 4 7 NULL 9 0 9
> 2 6 0 NULL NULL 6
> 1 0 NULL 5 0 5
> Thanks,
> A.
> --
> SQL Server DBA
>|||You have null's interspersed with 0's. Why is that? Why can't you
normalize the table and support the business rule?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ada" <Ada@.discussions.microsoft.com> wrote in message
news:9C10D96B-0D8D-4197-BD9A-92C14BF6529A@.microsoft.com...
It's the business rule.
Month value should be the latest w's value.
A.
--
SQL Server DBA
"Tom Moreau" wrote:

> Why on Earth would you design something like this?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Ada" <Ada@.discussions.microsoft.com> wrote in message
> news:59815DE0-B812-4FB6-8C25-D547ACB755C6@.microsoft.com...
> Hello All,
> I have a table with 6 column; w1-5 and month. Month column should be
> equal to the most recent column within w1...to..w5 column. If there
> is
> a value on w5 then month = w5, else w4...till w1.
> There can be null, blank, 0 or values on any of the w columns.
> WEEK1 WEEK2 WEEK3 WEEK4 WEEK5 MONTH
> ==== ===== ==== ===== ===== =====
> 4 7 NULL 9 0 9
> 2 6 0 NULL NULL 6
> 1 0 NULL 5 0 5
> Thanks,
> A.
> --
> SQL Server DBA
>|||This is a nightmare; look up First Normal Form (1NF). Why are you
putting numeric data into a CHAR(n) column? Make it numeric. Drop the
month column and use a VIEW:
CREATE VIEW Foobar (wk1, wk2, wk3, wk4, wk5, mnth)
AS
SELECT wk1, wk2, wk3, wk4, wk5
COALESCE (wk5, wk4, wk3, wk2, wk1)
FROM Floob;|||I am new to this db, just trying to get a report from it. Those numbers are
actually multicharacter code which is resolved by their application. Since
they have data validity issues, there are 0's and NULLs.
Thanks, for your help.
A.
--
SQL Server DBA
"--CELKO--" wrote:

> This is a nightmare; look up First Normal Form (1NF). Why are you
> putting numeric data into a CHAR(n) column? Make it numeric. Drop the
> month column and use a VIEW:
> CREATE VIEW Foobar (wk1, wk2, wk3, wk4, wk5, mnth)
> AS
> SELECT wk1, wk2, wk3, wk4, wk5
> COALESCE (wk5, wk4, wk3, wk2, wk1)
> FROM Floob;
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||In fact, all I needed was someting like COALESCE, but skips 0's as well as
NULLs.
The following case gave me the results I want.
UPDATE TempOrders
SET Month = CASE
WHEN (w5 > 0) THEN w5
WHEN (w4 > 0) THEN w4
WHEN (w3 > 0) THEN w3
WHEN (w2 > 0) THEN w2
WHEN (w1 > 0) THEN w1
ELSE NULL
END
Thanks,
A.
--
SQL Server DBA
"--CELKO--" wrote:

> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
>|||If you want to skip the 0's then you can use:
CREATE VIEW Foobar (wk1, wk2, wk3, wk4, wk5, mnth)
AS
SELECT wk1, wk2, wk3, wk4, wk5,
COALESCE (
NULLIF (wk5, 0)
, NULLIF (wk4, 0)
, NULLIF (wk3, 0)
, NULLIF (wk2, 0)
, NULLIF (wk1, 0)
)
FROM Floob;
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ada" <Ada@.discussions.microsoft.com> wrote in message
news:ABB01FE5-CA25-4693-920B-411DDB14D666@.microsoft.com...
In fact, all I needed was someting like COALESCE, but skips 0's as well as
NULLs.
The following case gave me the results I want.
UPDATE TempOrders
SET Month = CASE
WHEN (w5 > 0) THEN w5
WHEN (w4 > 0) THEN w4
WHEN (w3 > 0) THEN w3
WHEN (w2 > 0) THEN w2
WHEN (w1 > 0) THEN w1
ELSE NULL
END
Thanks,
A.
--
SQL Server DBA
"--CELKO--" wrote:

> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
>