Hello all, I'm learning SQL and need help with a query. I have a table that
stores employee ID and values per a given year. EmplID is stored in field 1
,
Yr1 val in field 2, Yr2 val in field 3... and so on up to any given number o
f
Yr fields. Not all of the Yr values exist for all Yr fields. I need to be
able to to pull the most recent value for a given row/record. So, for
instance, if EmplID ABCD stores the following:
EmplID Yr1 Yr2 Yr3 Yr4
ABCD 41 42 43 0
I need to pull 41 if Yr1 is the most recent year.
If Emplid EFGH stores the following:
EmplID Yr1 Yr2 Yr3 Yr4
EFGH 0 42 0 0
I need to pull 42, the Yr2 value, because no value exists for Yr1.
I am using SQL Server on a WinXP box. Any help would be greatly appreciated
.First of all, this is a poor design for a table. What are you doing,
continually adding new fields (we call them columns, or attributes) each
year? Better would be to have a column named "Year".
EmplID Year Value
ABCD 1 41
ABCD 2
You should, when possible, learn a bit about data modeling and
normalization... But
"kiloez" wrote:
> Hello all, I'm learning SQL and need help with a query. I have a table th
at
> stores employee ID and values per a given year. EmplID is stored in field
1,
> Yr1 val in field 2, Yr2 val in field 3... and so on up to any given number
of
> Yr fields. Not all of the Yr values exist for all Yr fields. I need to b
e
> able to to pull the most recent value for a given row/record. So, for
> instance, if EmplID ABCD stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> ABCD 41 42 43 0
> I need to pull 41 if Yr1 is the most recent year.
> If Emplid EFGH stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> EFGH 0 42 0 0
> I need to pull 42, the Yr2 value, because no value exists for Yr1.
> I am using SQL Server on a WinXP box. Any help would be greatly appreciated.[/col
or]|||Sorry submitted that before done...
First of all, this is a poor design for a table. What are you doing,
continually adding new fields (we call them columns, or attributes) each
year? Better would be to have a column named "Year".
EmplID Year Value
ABCD Yr1 41
ABCD Yr2 42
ABCD Yr3 43
ABCD Yr4 44
etc. .............
Second, what is Yr1, Yr2, Yr3, Yr4, mean ? Are these the actual column
names in your table? Do they map to actual years spmewhere else in your data
model?
When possible, you should learn a bit about data modeling and
normalization...
Given your problem, a solution based on the columns that exist in the table
right now would break when you add a new column for Yr5, unless the solution
was constructed to treat the "YrN" columns dynamically. That type of
solution would have to read system tables to determine which YR columns
existed, and then write dynamic sql to execute against the table.. A complex
but doable task. It would be much simpler (and therefore quicker more cost
effective) to restructure the data correctly.
"kiloez" wrote:
> Hello all, I'm learning SQL and need help with a query. I have a table th
at
> stores employee ID and values per a given year. EmplID is stored in field
1,
> Yr1 val in field 2, Yr2 val in field 3... and so on up to any given number
of
> Yr fields. Not all of the Yr values exist for all Yr fields. I need to b
e
> able to to pull the most recent value for a given row/record. So, for
> instance, if EmplID ABCD stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> ABCD 41 42 43 0
> I need to pull 41 if Yr1 is the most recent year.
> If Emplid EFGH stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> EFGH 0 42 0 0
> I need to pull 42, the Yr2 value, because no value exists for Yr1.
> I am using SQL Server on a WinXP box. Any help would be greatly appreciated.[/col
or]|||First off my dear friend, you might want to re-evaluate the database design.
You have just violated the second normal form (or is it the third? I get
) Anyways, the best solution would be to move your data to another
table and drop this table here.
I would suggest the following design for your table
CREATE TABLE Emp_Details (
EmpID int NOT NULL,
Year char(4) NOT NULL,
Value int NOT NULL DEFAULT 0
)
Then, populate it as follows:
INSERT INTO Emp_Details
SELECT EmplID, 'Yr1', Yr1
FROM Emp_Table
INSERT INTO Emp_Details
SELECT EmplID, 'Yr2', Yr2
FROM Emp_Table
INSERT INTO Emp_Details
SELECT EmplID, 'Yr3', Yr3
FROM Emp_Table
INSERT INTO Emp_Details
SELECT EmplID, 'Yr4', Yr4
FROM Emp_Table
and so on...
now you have a sensible looking table. you may want to drop your old table
and rename this table. Let everyone know that you have just got some sensibl
e
table design done and that the applications need to change the way they
access data.
Once this is done, you can run the following query:
SELECT Emp_ID, MAX(Year), Value
FROM Emp_Details
WHERE (Value <> 0)
GROUP BY Emp_ID, Value
--that said, if you want a quick fix or boss is not willing to let go of
his ideas of 3NF, you can run this query:
SELECT EmplID,
CASE
WHEN (Yr1 <> 0) THEN Yr1
ELSE
CASE
WHEN (Yr2 <> 0) THEN Yr2
ELSE
CASE
WHEN (Yr3 <> 0) THEN Yr3
ELSE Yr4
END CASE
END CASE
END CASE
FROM Empl_Details
Hope that helps,
Aneesh.
"kiloez" wrote:
> Hello all, I'm learning SQL and need help with a query. I have a table th
at
> stores employee ID and values per a given year. EmplID is stored in field
1,
> Yr1 val in field 2, Yr2 val in field 3... and so on up to any given number
of
> Yr fields. Not all of the Yr values exist for all Yr fields. I need to b
e
> able to to pull the most recent value for a given row/record. So, for
> instance, if EmplID ABCD stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> ABCD 41 42 43 0
> I need to pull 41 if Yr1 is the most recent year.
> If Emplid EFGH stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> EFGH 0 42 0 0
> I need to pull 42, the Yr2 value, because no value exists for Yr1.
> I am using SQL Server on a WinXP box. Any help would be greatly appreciated.[/col
or]|||Thanks for the help guys. I had nothing to do with the design and
development of the DB. I was only asked to query some of the tables.
Aneesh, the quick fix was perfect for what I was looking to accomplish.
Worked out just fine.
Many thanks,
kiloez
"Aneesh Aravind" wrote:
> First off my dear friend, you might want to re-evaluate the database desig
n.
> You have just violated the second normal form (or is it the third? I get
>
) Anyways, the best solution would be to move your data to another
> table and drop this table here.
> I would suggest the following design for your table
> CREATE TABLE Emp_Details (
> EmpID int NOT NULL,
> Year char(4) NOT NULL,
> Value int NOT NULL DEFAULT 0
> )
> Then, populate it as follows:
> INSERT INTO Emp_Details
> SELECT EmplID, 'Yr1', Yr1
> FROM Emp_Table
> INSERT INTO Emp_Details
> SELECT EmplID, 'Yr2', Yr2
> FROM Emp_Table
> INSERT INTO Emp_Details
> SELECT EmplID, 'Yr3', Yr3
> FROM Emp_Table
> INSERT INTO Emp_Details
> SELECT EmplID, 'Yr4', Yr4
> FROM Emp_Table
> and so on...
> now you have a sensible looking table. you may want to drop your old table
> and rename this table. Let everyone know that you have just got some sensi
ble
> table design done and that the applications need to change the way they
> access data.
> Once this is done, you can run the following query:
> SELECT Emp_ID, MAX(Year), Value
> FROM Emp_Details
> WHERE (Value <> 0)
> GROUP BY Emp_ID, Value
> --that said, if you want a quick fix or boss is not willing to let go of
> his ideas of 3NF, you can run this query:
> SELECT EmplID,
> CASE
> WHEN (Yr1 <> 0) THEN Yr1
> ELSE
> CASE
> WHEN (Yr2 <> 0) THEN Yr2
> ELSE
> CASE
> WHEN (Yr3 <> 0) THEN Yr3
> ELSE Yr4
> END CASE
> END CASE
> END CASE
> FROM Empl_Details
> Hope that helps,
> Aneesh.
> "kiloez" wrote:
>
John Colaizzi wrote:
John Colaizzi wrote: