|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Getting most recent valueI have a table with 6 column; week1-5 and month. Month column should be equal to the most recent column within week1...to..week5 column. If there is a value on week5 then month = week5, else week4....till week1. There can be null, blank, 0 or values on any of the week 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 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" <A**@discussions.microsoft.com> wrote in message Hello All,news:59815DE0-B812-4FB6-8C25-D547ACB755C6@microsoft.com... I have a table with 6 column; week1-5 and month. Month column should be equal to the most recent column within week1...to..week5 column. If there is a value on week5 then month = week5, else week4....till week1. There can be null, blank, 0 or values on any of the week 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 week's value. A. -- Show quoteSQL 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" <A**@discussions.microsoft.com> wrote in message > news:59815DE0-B812-4FB6-8C25-D547ACB755C6@microsoft.com... > > Hello All, > > I have a table with 6 column; week1-5 and month. Month column should be > equal to the most recent column within week1...to..week5 column. If there is > a value on week5 then month = week5, else week4....till week1. > There can be null, blank, 0 or values on any of the week 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" <A**@discussions.microsoft.com> wrote in message It's the business rule.news:9C10D96B-0D8D-4197-BD9A-92C14BF6529A@microsoft.com... Month value should be the latest week's value. A. -- Show quoteSQL 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" <A**@discussions.microsoft.com> wrote in message > news:59815DE0-B812-4FB6-8C25-D547ACB755C6@microsoft.com... > > Hello All, > > I have a table with 6 column; week1-5 and month. Month column should be > equal to the most recent column within week1...to..week5 column. If there > is > a value on week5 then month = week5, else week4....till week1. > There can be null, blank, 0 or values on any of the week 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. -- Show quoteSQL 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 (week5 > 0) THEN week5 WHEN (week4 > 0) THEN week4 WHEN (week3 > 0) THEN week3 WHEN (week2 > 0) THEN week2 WHEN (week1 > 0) THEN week1 ELSE NULL END Thanks, A. -- Show quoteSQL 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" <A**@discussions.microsoft.com> wrote in message In fact, all I needed was someting like COALESCE, but skips 0's as well asnews:ABB01FE5-CA25-4693-920B-411DDB14D666@microsoft.com... NULLs. The following case gave me the results I want. UPDATE TempOrders SET Month = CASE WHEN (week5 > 0) THEN week5 WHEN (week4 > 0) THEN week4 WHEN (week3 > 0) THEN week3 WHEN (week2 > 0) THEN week2 WHEN (week1 > 0) THEN week1 ELSE NULL END Thanks, A. -- Show quoteSQL 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. > >  
Other interesting topics
|
|||||||||||||||||||||||
