most recent date? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

most recent date?

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-19 : 12:11:03

If I have date1, date2, date3, date4, how would I get that date that is the most recent? Thanks!

Brenda

If it weren't for you guys, where would I be?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-19 : 12:14:08

SELECT min(dt) FROM (
SELECT @date1 AS dt UNION ALL
SELECT @date2 UNION ALL
SELECT @date3 UNION ALL
SELECT @date4) a

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-19 : 12:19:57

OK. I tried this, but it doesn't work:

SELECT min(dt) FROM tblcaprec (
SELECT appsent AS dt UNION ALL
SELECT appsent2 UNION ALL
SELECT appsent3 UNION ALL
SELECT appsent4 UNION ALL
SELECT appsent5) a

what is wrong?

Brenda

If it weren't for you guys, where would I be?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-19 : 12:36:42

I thought you were using date variables, not multiple columns in the same table (this is a poor design by the way)

As it turns out I made a mistake in my original post, should have used max instead of min.

I don't have a solution now, I might have one later. You might want to try searching the forums for "greatest" or "least", I'm pretty sure there was a post that solved this problem.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-19 : 12:37:18

what doesn't work? it gives you an error? it returns the wrong value? what data do you have and what are you looking to return?

please look at your question:

quote:

f I have date1, date2, date3, date4, how would I get that date that is the most recent?

step back, forget that you are intimately familiar with your situation, and pretend you have seen that question for the first time. does it provide enough info to give you the answer you are looking for? is it one row, multile rows, multiple tables, variables, multiple columns in multipler rows, etc?

and, as always, please consider normalizing your database!

- Jeff

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-19 : 12:41:17

I have this now:

SELECT max(dt) FROM tblcaprec
(SELECT appsent AS dt UNION ALL
SELECT appsent2 UNION ALL
SELECT appsent3 UNION ALL
SELECT appsent4 UNION ALL
SELECT appsent5)
WHERE CaseNumber = '021-%'

and it gives me this error:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.

Brenda

If it weren't for you guys, where would I be?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-19 : 12:43:55

is this what you meant?

SELECT max(dt) FROM
(SELECT appsent AS dt UNION ALL
SELECT appsent2 UNION ALL
SELECT appsent3 UNION ALL
SELECT appsent4 UNION ALL
SELECT appsent5) tblcaprec
WHERE CaseNumber = '021-%'

but then you'd need CaseNumber in your unions...
if that is another table you need a column to joind them on...

Go with the flow & have fun! Else fight the flow

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-19 : 12:50:57

I just have one table: tblCapRec. It has casenumbers and dates. I want to be able to get the date that is most recent. How do I do that? Thanks!

Brenda

If it weren't for you guys, where would I be?

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-19 : 12:54:20

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(appsent datetime, appsent2 datetime, appsent3 datetime, appsent4 datetime, appsent5 datetime)
GO

INSERT INTO myTable99(appsent, appsent2, appsent3, appsent4, appsent5)
SELECT '1/1/2000', '1/2/2000', '1/3/2000', '1/4/2000', '1/5/2000' UNION ALL
SELECT '1/1/2001', '1/2/2001', '1/3/2001', '1/4/2001', '1/5/2001' UNION ALL
SELECT '1/1/2000', '1/2/2000', '1/3/2000', '1/4/2000', '1/5/1999' UNION ALL
SELECT '1/1/2000', '1/2/2000', '1/3/2000', '1/4/1960', '1/5/2000'
GO

DECLARE @sql varchar(8000)

SELECT @sql = 'SELECT MAX(dt) AS MostRecentDate FROM ( '
SELECT @sql = @sql + 'SELECT MAX(' + COLUMN_NAME + ') AS dt FROM ' + TABLE_NAME + ' UNION ALL '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'myTable99' AND DATA_TYPE = 'datetime'

SELECT @sql = LEFT(@sql,LEN(@SQL)-10) + ') AS XXX'

SELECT @sql

EXEC(@sql)
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

[/code]

Brett

8-)

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-19 : 13:09:57

I am confused on the coding. Can you fix this for me?

SELECT max(dt) AS MostRecentDate FROM
(SELECT appsent AS dt UNION ALL
SELECT appsent2 UNION ALL
SELECT appsent3 UNION ALL
SELECT appsent4 UNION ALL
SELECT appsent5)
WHERE TABLE_NAME = 'tblCapRec' AND CaseNumber = '021-%'

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-19 : 13:15:55

Did you try running his code in Query Analyzer? He sets up a table that should mimic what you have and then selects the data out. Run it and see, then modify the select statement for your environment.

Tara

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-19 : 13:16:19

there is no way anyone can help you correctly if you don't post your table definitions and sample data.
you should know this by now...

Go with the flow & have fun! Else fight the flow

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-19 : 13:18:32

And the code you have will not work at all, there's no way to "fix" it.

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-19 : 13:29:01

I don't understand what to put in this part:

'SELECT MAX(' + COLUMN_NAME + ') AS dt FROM ' + TABLE_NAME + ' UNION ALL '
FROM INFORMATION_SCHEMA.COLUMNS

Do I need to replace things in there?

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-19 : 13:30:42

Just run his code as is. He sets everything up already. He doesn't use your tables, he creates his own to teach you how to make it work for your environment. Take a look at the data inside myTable99 as well as the result set of his query.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-19 : 13:35:42

Do you have query analyzer running?

Just cut and paste the code as is...and execute it...

You could then take the code snipet and change the table name in the where clause in your environment...and it should just work as well.

You then have the option of taking hwat it generates and using that or leave it as dynamic.

I'd use what it generated if it's for production

Brett

8-)

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-19 : 13:36:26

I ran his code and it works. When I go Northwind in EM, it is not there. I do a refresh too and it still isn't there. Any ideas?

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-19 : 13:37:34

That's because his code drops it at the end. So before the drop do a select * or whatever.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-19 : 13:39:53

If you ran it, then it produced the actual sql in the result query pane...

That's what you want to use.

The code I used, was code to generate code....you don't need to do that...

Also, you could lose the myTable99 bit,

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-20 : 18:43:41

Also, my apologizes for the confudion. I do the "housekeeping" to keep thing clean and the way they were before the example.

Just a holdover from my mainframe roots.

The maineframe you see is very painful...every little detail must be specified...otherwise it doesn't work.

AND...trying to do DB2 Sprocs is the unlike anything I've dealt with.

They call COBOL prgrams, registerd to DB2 as "external" stored procedures.

Makes no sense to me. Why IBM can't install a procedural version is beyond me.

Hell I'll take REXX.

OK. Rant over.

Brett

8-)

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-22 : 11:25:30

Good morning everyone! Isn't everyone glad this is a short week?!

Anyway, this is what I have so far, but it won't work. Why?


SELECT MAX(dt) AS MostRecentDate 
FROM (
SELECT MAX(appsent) AS dt FROM tblCaprec UNION ALL 
SELECT MAX(appsent2) AS dt FROM tblCaprec UNION ALL 
SELECT MAX(appsent3) AS dt FROM tblCaprec UNION ALL 
SELECT MAX(appsent4) AS dt FROM tblCaprec UNION ALL 
SELECT MAX(appsent5) AS dt FROM tblCaprec UNION ALL

Brenda

If it weren't for you guys, where would I be?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources