returning the column name with the highest value

[arabideau]

I have a table with 10 smalldatetime fields. I need to build a select statement that will return the column name of the field with most recent date for a specific row or for all rows.

Database is SQL2000 sp4. Any suggestions would be appreciated.

[erickwidya]

will return the column name of the field with most recent date for a specific row or for all rows. plz post the example data and the result that u want to achieve

[arabideau]

For reasons I don't want to get in to we have a table that we import data into from a legacy app that is still a mission critical app. Within this app there are several datestamps for specific actions on the file. When the end users are viewing reports on their files they have to look at all of these dates for each record to determine what the status of the record is. What I am trying to do is add a status field to the table that shows them in one field what the status is. To do this I need to select the most recent datestamp from these fields and insert the column name of that datestamp into the summary field.

For example:
fileid sumbitted approved denied suspended status
59833 5/5/2005 5/12/2005 null null approved
57429 5/3/2005 null 5/4/2005 null denied
60324 5/6/2005 null null null submitted

This can be done as a stored procedure if needed or better yet as a calculated field within the table.

[erickwidya]

i'm confused now

so u want to make something like "the example" which data is from "File" ? is text based File or else?

[arabideau]

Initially they are in flat files that can only be read in binary mode. For now, we export the data using the export utility in the app to a text file and import it into SQL for reporting purposes. The reporting tools in the app are painfully slow because it is built on flat files spread over multiple directories. There isn't a means to export the status field from the application because it is calculated when viewing the record and the value is never actually stored in the file. I am trying to replicate this status field in the SQL table that we do our reporting off of.

The status is calculated based on the most recent date of several fields. If field 'A' has a date of 1/1/2001 and field 'B' has a date of 1/2/2001 then the "status" is 'B'. If the date of field 'A' changes to 1/3/2001 then the "status" of the record changes to 'A'. In total there are 10 fields that the "status" needs to be selected from.

Is this any clearer? If not feel free to contact me via email and I can give you some samples.

[erickwidya]

I am trying to replicate this status field in the SQL table that we do our reporting off of. u planned to added the status field coz the table now looks like ur first post but without the status field and with huge record for each fileid?

try this one
CREATE PROC CalculatedStatus
AS
DECLARE @StatusDate Datetime

INSERT INTO NewTable(FileID, SubmittedDate, StatusDate, Status)
SELECT [table_name].[fileid], Max([table_name].[submitted]),
CASE
WHEN Approved <> NULL THEN SET @StatusDate = Approved
WHEN Denied <> NULL THEN SET @StatusDate = Denied
WHEN Suspended <> NULL THEN SET @StatusDate = Suspended
END,
CASE
WHEN Approved <> NULL THEN 'Approved'
WHEN Denied <> NULL THEN 'Denied'
WHEN Suspended <> NULL THEN 'Suspended'
END
FROM table_name
GROUP BY [table_name].[fileid]
GO

it's basically added new record to existing "NewTable" table for ur app export utility's result since i'm not sure how the app read the flat files, and what the structure of that flat file

PS : i writing the code directly so perhaps some syntax were wrong, hope it can give an idea

good luck :wave:

[arabideau]

Thanks for the help. This jogged me out of my tunnel vision enough to get me down the right path.

[erickwidya]

ur welcome

good luck :thumb: