find the lastest date among many fields

I have seven date fields, and I need to select the lastest date among the
seven fields.
Example
table1
ID, date1, date2, date3, date4,date5, date6, date7
Result should show '10/07/2005'
Thanks,
Culam
CREATE TABLE table1
(
id int,
date1 smalldatetime,
date2 smalldatetime,
date3 smalldatetime,
date4 smalldatetime,
date5 smalldatetime,
date6 smalldatetime,
date7 smalldatetime
)
INSERT INTO table1
(id, date1, date2, date3, date4, date5, date6, date7)
VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
'10/04/2005', '10/06/2005', '10/05/2005')7 date columns in a row looks suspiciously like poor normalization.
Anyway, try the following. Nulls will be ignored.
SELECT id,
(SELECT MAX(dt)
FROM
(SELECT date1 AS dt UNION ALL
SELECT date2 UNION ALL
SELECT date3 UNION ALL
SELECT date4 UNION ALL
SELECT date5 UNION ALL
SELECT date6 UNION ALL
SELECT date7) AS X)
AS maxdate
FROM Table1
David Portas
SQL Server MVP
--|||Culam,
I'm not a design expert but I think you may need to work on normalizing this
a bit.
You could try something like:
SELECT ID,
(SELECT MAX(DATEVALUE)
FROM
(SELECT DATE1 AS DATEVALUE FROM TABLE1 UNION ALL
SELECT DATE2 FROM TABLE1 UNION ALL
SELECT DATE3 FROM TABLE1 UNION ALL
SELECT DATE4 FROM TABLE1 UNION ALL
SELECT DATE5 FROM TABLE1 UNION ALL
SELECT DATE6 FROM TABLE1 UNION ALL
SELECT DATE7 FROM TABLE1)
AS Z)
FROM TABLE1
or use the MAX function with a temp table.
HTH
Jerry
"culam" <culam@.discussions.microsoft.com> wrote in message
news:49405690-F7F1-4E6A-BCCD-27407C0E35E7@.microsoft.com...
>I have seven date fields, and I need to select the lastest date among the
> seven fields.
> Example
> table1
> ID, date1, date2, date3, date4,date5, date6, date7
> Result should show '10/07/2005'
> Thanks,
> Culam
> CREATE TABLE table1
> (
> id int,
> date1 smalldatetime,
> date2 smalldatetime,
> date3 smalldatetime,
> date4 smalldatetime,
> date5 smalldatetime,
> date6 smalldatetime,
> date7 smalldatetime
> )
> INSERT INTO table1
> (id, date1, date2, date3, date4, date5, date6, date7)
> VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
> '10/04/2005', '10/06/2005', '10/05/2005')|||Try,
CREATE TABLE t1
(
id int,
date1 smalldatetime,
date2 smalldatetime,
date3 smalldatetime,
date4 smalldatetime,
date5 smalldatetime,
date6 smalldatetime,
date7 smalldatetime
)
INSERT INTO t1
(id, date1, date2, date3, date4, date5, date6, date7)
VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
'10/04/2005', '10/06/2005', '10/05/2005')
select [id], max(c1)
from
(
select [id], date1
from t1
union all
select [id], date2
from t1
union all
select [id], date3
from t1
union all
select [id], date4
from t1
union all
select [id], date5
from t1
union all
select [id], date6
from t1
union all
select [id], date7
from t1
) as t2([id], c1)
group by [id]
-- or
select
[id],
max(
case t2.c1
when 1 then date1
when 2 then date2
when 3 then date3
when 4 then date4
when 5 then date5
when 6 then date6
when 7 then date7
end
)
from t1 cross join (select 1 as c1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union all select 7)
as t2
group by [id]
drop table t1
go
AMB
"culam" wrote:

> I have seven date fields, and I need to select the lastest date among the
> seven fields.
> Example
> table1
> ID, date1, date2, date3, date4,date5, date6, date7
> Result should show '10/07/2005'
> Thanks,
> Culam
> CREATE TABLE table1
> (
> id int,
> date1 smalldatetime,
> date2 smalldatetime,
> date3 smalldatetime,
> date4 smalldatetime,
> date5 smalldatetime,
> date6 smalldatetime,
> date7 smalldatetime
> )
> INSERT INTO table1
> (id, date1, date2, date3, date4, date5, date6, date7)
> VALUES (1, '10/01/2005', '10/02/2005', '10/07/2005', '10/03/2005',
> '10/04/2005', '10/06/2005', '10/05/2005')|||Jerry Spivey wrote:
> SELECT ID,
> (SELECT MAX(DATEVALUE)
> FROM
> (SELECT DATE1 AS DATEVALUE FROM TABLE1 UNION ALL
> SELECT DATE2 FROM TABLE1 UNION ALL
> SELECT DATE3 FROM TABLE1 UNION ALL
> SELECT DATE4 FROM TABLE1 UNION ALL
> SELECT DATE5 FROM TABLE1 UNION ALL
> SELECT DATE6 FROM TABLE1 UNION ALL
> SELECT DATE7 FROM TABLE1)
> AS Z)
> FROM TABLE1
>
Did you try that with more than one row of dates? If Culam wants just a
single maximum date then you may as well remove the outer part of the
query.
David Portas
SQL Server MVP
--|||Was just about to post how your code rocks and mine is would be sluggish
with all of the table scans! ;-) Dropped all of the extra FROM TABLE1.
Thanks for the follow up...always learning from you.
Jerry
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129057820.822069.139720@.o13g2000cwo.googlegroups.com...
> Jerry Spivey wrote:
> Did you try that with more than one row of dates? If Culam wants just a
> single maximum date then you may as well remove the outer part of the
> query.
> --
> David Portas
> SQL Server MVP
> --
>|||David,
Might want to change it up a bit for multiple rows to something like this:
SELECT TOP 1 X.ID, MAX(X.MAXDATE) AS 'MAX DATE'
FROM
(SELECT id,
(SELECT MAX(dt)
FROM
(SELECT date1 AS dt UNION ALL
SELECT date2 UNION ALL
SELECT date3 UNION ALL
SELECT date4 UNION ALL
SELECT date5 UNION ALL
SELECT date6 UNION ALL
SELECT date7) AS X)
AS maxdate
FROM Table1) AS X
GROUP BY X.ID
ORDER BY MAX(X.MAXDATE)DESC
Thoughts?
Thanks
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23xMgPgpzFHA.2792@.tk2msftngp13.phx.gbl...
> Was just about to post how your code rocks and mine is would be sluggish
> with all of the table scans! ;-) Dropped all of the extra FROM TABLE1.
> Thanks for the follow up...always learning from you.
> Jerry
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1129057820.822069.139720@.o13g2000cwo.googlegroups.com...
>|||Just my usualy caveats around TOP: Use an ORDER BY that's guaranteed to
be unique or specify TOP WITH TIES. Random results due to ties can be a
lot of hassle.
David Portas
SQL Server MVP
--

Find Missing data from table

Here is an issue that has me stumped for the past few days. I have a
table called MerchTran. Among various columns, the relevant columns for
this issue are:

FileDate datetime
, SourceTable varchar(25)

SQL statement:
SELECT DISTINCT
FileDate
, SourceTable
FROM
MerchTran
ORDER BY
FileDate
, SourceTable

Data looks like this:
FileDate DataSource
-----------
2005-02-13 00:00:00.000S1
2005-02-13 00:00:00.000S2
2005-02-13 00:00:00.000S3
2005-02-14 00:00:00.000S1
2005-02-14 00:00:00.000S2
2005-02-14 00:00:00.000S3
2005-02-15 00:00:00.000S2
2005-02-15 00:00:00.000S3
2005-02-16 00:00:00.000S1
2005-02-16 00:00:00.000S2
2005-02-16 00:00:00.000S3
2005-02-17 00:00:00.000S1
2005-02-17 00:00:00.000S2
2005-02-18 00:00:00.000S1
2005-02-18 00:00:00.000S2
2005-02-18 00:00:00.000S3
2005-02-19 00:00:00.000S1
2005-02-19 00:00:00.000S3

We run a daily process that inserts data in to this table everyday for
all 3 sources S1, S2, S3

Notice how some data is missing indicating the import process for that
particular source failed.
Example: Missing record
2005-02-15 00:00:00.000S1
2005-02-17 00:00:00.000S3
2005-02-19 00:00:00.000S2

Can someone please help me with a SQL Statement that will return me the
3 missing records as above.

Thanks in advance for all your help!

DBA in distress!
Vishal[posted and mailed, please reply in news]

SQLJunkie (vsinha73@.gmail.com) writes:
> Here is an issue that has me stumped for the past few days. I have a
> table called MerchTran. Among various columns, the relevant columns for
> this issue are:
>...
> We run a daily process that inserts data in to this table everyday for
> all 3 sources S1, S2, S3
> Notice how some data is missing indicating the import process for that
> particular source failed.
> Example: Missing record
> 2005-02-15 00:00:00.000 S1
> 2005-02-17 00:00:00.000 S3
> 2005-02-19 00:00:00.000 S2
> Can someone please help me with a SQL Statement that will return me the
> 3 missing records as above.

The below assumes that daily means daily and not only Monday to Friday.
It will catch if a date is missing entirely, although if the first or
last day is missing.

CREATE TABLE demo (date datetime NOT NULL,
src char(2) NOT NULL,
CONSTRAINT pk_demo PRIMARY KEY (date, src))
go
-- Creates a numbers table in a somewhat casual way that
-- is not guaranteed to work. The MAXDOP turns of parallelism
-- to improve our chances.
SELECT TOP 8000 n = identity(int, 1, 1)
INTO numbers
FROM Northwind..Orders a
CROSS JOIN Northwind..Orders b
OPTION (MAXDOP 1)
go
INSERT demo (date, src)
SELECT '2005-02-13 00:00:00.000', 'S1' UNION
SELECT '2005-02-13 00:00:00.000', 'S2' UNION
SELECT '2005-02-13 00:00:00.000', 'S3' UNION
SELECT '2005-02-14 00:00:00.000', 'S1' UNION
SELECT '2005-02-14 00:00:00.000', 'S2' UNION
SELECT '2005-02-14 00:00:00.000', 'S3' UNION
SELECT '2005-02-15 00:00:00.000', 'S2' UNION
SELECT '2005-02-15 00:00:00.000', 'S3' UNION
SELECT '2005-02-16 00:00:00.000', 'S1' UNION
SELECT '2005-02-16 00:00:00.000', 'S2' UNION
SELECT '2005-02-16 00:00:00.000', 'S3' UNION
SELECT '2005-02-17 00:00:00.000', 'S1' UNION
SELECT '2005-02-17 00:00:00.000', 'S2' UNION
SELECT '2005-02-18 00:00:00.000', 'S1' UNION
SELECT '2005-02-18 00:00:00.000', 'S2' UNION
SELECT '2005-02-18 00:00:00.000', 'S3' UNION
SELECT '2005-02-19 00:00:00.000', 'S1' UNION
SELECT '2005-02-19 00:00:00.000', 'S3'
go
SELECT *
FROM (SELECT date = dateadd(DAY, n - 1, mindate)
FROM numbers n
CROSS JOIN (SELECT mindate = MIN(date),
maxdate = MAX(date)
FROM demo) d
WHERE n BETWEEN 1 AND datediff(DAY, mindate, maxdate) + 1) AS a
CROSS JOIN (SELECT src = 'S1' UNION SELECT 'S2' UNION SELECT 'S3') AS s
WHERE NOT EXISTS (SELECT *
FROM demo
WHERE demo.date = a.date
AND demo.src = s.src)
go
DROP TABLE numbers
DROP TABLE demo

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the quick response Erland. I see what you are trying to do,
but I have data in the table starting 2003. Would be a lot of work if I
have to insert 3 records in table numbers for everyday (including
saturday and sunday) starting 2003. But I will try this nonetheless.

Thanks!

Vishal|||SQLJunkie (vsinha73@.gmail.com) writes:
> Thanks for the quick response Erland. I see what you are trying to do,
> but I have data in the table starting 2003. Would be a lot of work if I
> have to insert 3 records in table numbers for everyday (including
> saturday and sunday) starting 2003. But I will try this nonetheless.

Numbers need to have as many rows as there days in the timespan.

In fact, it does have to be a numbers table. I used a numbers, because
1) it was easier to compose one on the fly.
2) a numbers table is applicable other problems as well.
However, in our system we don't have a numbers - but we have a dates table,
and here is how we fill it in a safe way:

CREATE TABLE dates (
thedate aba_date NOT NULL,
CONSTRAINT pk_dates PRIMARY KEY (thedate)
)

-- Get a temptable with numbers. This is a cheap, but not 100% reliable.
-- Whence the query hint and all the checks.
SELECT TOP 80001 n = IDENTITY(int, 0, 1)
INTO #numbers
FROM sysobjects o1
CROSS JOIN sysobjects o2
CROSS JOIN sysobjects o3
CROSS JOIN sysobjects o4
OPTION (MAXDOP 1)
go
-- Make sure we have unique numbers.
CREATE UNIQUE CLUSTERED INDEX num_ix ON #numbers (n)
go
-- Verify that table does not have gaps.
IF (SELECT COUNT(*) FROM #numbers) = 80001 AND
(SELECT MIN(n) FROM #numbers) = 0 AND
(SELECT MAX(n) FROM #numbers) = 80000
BEGIN
DECLARE @.msg varchar(255)

-- Insert the dates:
INSERT dates (thedate)
SELECT dateadd(DAY, n, '19800101')
FROM #numbers
WHERE dateadd(DAY, n, '19800101') < '21500101'

SELECT @.msg = 'Inserted ' + ltrim(str(@.@.rowcount)) + ' rows into
#numbers'
PRINT @.msg
END
ELSE
RAISERROR('#numbers is not contiguos from 0 to 80001!', 16, -1)
go
DROP TABLE #numbers

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||CREATE TABLE Readings
(collection_date DATETIME NOT NULL,
source CHAR(2) NOT NULL
CHECK(source IN ('S1', 'S2', 'S3')),
PRIMARY KEY (collection_date, source));

This is a quick way to see what you do have on the days with missing
data:

CREATE VIEW Shortdates(collection_date, source)
AS
SELECT collection_date, source
FROM Readings
GROUP BY collection_date
HAVING COUNT(*) < 3;

We can now use this view or make it into a derived table. We will also
another table, which probably exists already.

CREATE TABLE Sources (sources CHAR(2) NOT NULL PRIMARY KEY, ..);

SELECT R1.collection_date, S1.source
FROM Readings AS R1,
Sources AS S1
WHERE S1.source
NOT IN (SELECT R2.source
FROM Shortdates AS R2 -- or use a derived table
WHERE R1.collection_date
= R2.collection_date);

-- Untested