Max Date for multiple columns

Hi all,
Okay... this should be a pretty question, but, can't seem to figure out
how to do it. In this database I'm working with, they have created 8 column
s
(Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
I need to get the max date (Date1, Date2, Date3 or Date4) and the
information from the appropriate column (So, if Date2 is the max date, then
return the result set containing Version2 and Date2 data)
Any idea about how to approach this problem?
Any help is greatly apprectiated.
DougSure...
CREATE TABLE [dbo].[TEST] (
[ACCOUNT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SYSTEM1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE1] [datetime] NULL ,
[SYSTEM2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE2] [datetime] NULL ,
[SYSTEM3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE3] [datetime] NULL ,
[SYSTEM4] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE4] [datetime] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
"Doug" wrote:

> Hi all,
> Okay... this should be a pretty question, but, can't seem to figure ou
t
> how to do it. In this database I'm working with, they have created 8 colu
mns
> (Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
> I need to get the max date (Date1, Date2, Date3 or Date4) and the
> information from the appropriate column (So, if Date2 is the max date, the
n
> return the result set containing Version2 and Date2 data)
> Any idea about how to approach this problem?
> Any help is greatly apprectiated.
> Doug|||I see several solutions
A. Self joins
B. Big if statement
or
C. Temp table
Create a temp table that has the record number, Version, and Date. Each
row represents a single Version / Date pair. Each row in your original
table would then become 4 rows in the temp table.
Then you could do
select recordid, max(datecol)
from tempTable
group by recordid|||>> Any idea about how to approach this problem?
The very fact that a simple query as this requires a complex solution itself
suggest that the table design could be improved. Rather than using column
names to represent data, consider something along the lines of:
CREATE TABLE tbl (
key_col ...
version ..
date_col DATETIME ) ;
This will allow you to add more versions without having to alter the schema.
Moreover the design is more flexible & allows for better constraint
enforcement as well.
If you are somehow forced to stick with the existing schema consider using a
view/ derived table to logically abstract the data like:
SELECT key_col,
CASE n WHEN 1 THEN version1
WHEN 2 THEN version2
WHEN 3 THEN version3
WHEN 4 THEN version4
END AS "version",
CASE n WHEN 1 THEN date1
WHEN 2 THEN date2
WHEN 3 THEN date3
WHEN 4 THEN date4
END AS "version_date"
FROM tbl, ( SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 ) T ( n );
Now, it is just a matter of using aggregate function MAX() on the
version_date column to get the required value.
Anith|||On Mon, 20 Mar 2006 06:59:42 -0800, Doug wrote:

>Hi all,
> Okay... this should be a pretty question, but, can't seem to figure out
>how to do it. In this database I'm working with, they have created 8 colum
ns
>(Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
> I need to get the max date (Date1, Date2, Date3 or Date4) and the
>information from the appropriate column (So, if Date2 is the max date, then
>return the result set containing Version2 and Date2 data)
> Any idea about how to approach this problem?
Hi Doug,
Normalise your design. You should have a seperate table with Date and
Version as columns, plus a foreign key to the table where these 8
columns now are. Then, it's quite easy.
Assuming the normalised table looks like this
CREATE TABLE YourTable
(CustomerID int NOT NULL,
TheDate datetime NOT NULL,
Version varchar(20) NOT NULL,
PRIMARY KEY (CustomerID, TheDate),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
)
The query is like this
SELECT a.CustomerID, a.TheDate, a.Version
FROM YourTable AS a
INNER JOIN (SELECT CustomerID, MAX(TheDate) AS MaxDate
FROM YourTable
GROUP BY CustomerID) AS b
ON b.Customer = a.Customer
AND b.MaxDate = a.TheDate
Hugo Kornelis, SQL Server MVP|||Hi all,
Thanks for all of the help! Unfortunately, the design can't be
normalised. We are using the Goldmine application (commercial product) and
they designed the tables to work this way. This design has caused me a
number of headaches.
Overall, I went with a stored procedure to get the data into a
temporary table that was more normalized and then got my information using
standard techniques.
Dougie
"Hugo Kornelis" wrote:

> On Mon, 20 Mar 2006 06:59:42 -0800, Doug wrote:
>
> Hi Doug,
> Normalise your design. You should have a seperate table with Date and
> Version as columns, plus a foreign key to the table where these 8
> columns now are. Then, it's quite easy.
> Assuming the normalised table looks like this
> CREATE TABLE YourTable
> (CustomerID int NOT NULL,
> TheDate datetime NOT NULL,
> Version varchar(20) NOT NULL,
> PRIMARY KEY (CustomerID, TheDate),
> FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
> )
> The query is like this
> SELECT a.CustomerID, a.TheDate, a.Version
> FROM YourTable AS a
> INNER JOIN (SELECT CustomerID, MAX(TheDate) AS MaxDate
> FROM YourTable
> GROUP BY CustomerID) AS b
> ON b.Customer = a.Customer
> AND b.MaxDate = a.TheDate
> --
> Hugo Kornelis, SQL Server MVP
>