MAX(Date) - what about the time? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

MAX(Date) - what about the time?

Author

Topic

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-07-23 : 07:39:53

With the following code, I get an error in that it does not differentiate in the time. Instead of giving me only 1 record per PlayersID, it gives multiples when there is more than one Training on the same day.
I want MAX of the date and the time. IE, the latest training.
Where am I going wrong?
I have tried to convert the time too.

SELECT     PlayersID, Training, Date
FROM         ArchivePlayerTraits AS APT
WHERE     (Date IN
                          (SELECT     MAX(Date) AS Expr1
                            FROM          ArchivePlayerTraits
                            GROUP BY PlayersID))

SELECT     PlayersID, Training, Date
FROM         ArchivePlayerTraits AS APT
WHERE     (Date IN
                          (SELECT     MAX(CONVERT(DATETIME, Date, 102)) AS date
                            FROM          ArchivePlayerTraits AS APT
                            GROUP BY PlayersID))
ORDER BY PlayersID

Neither of these examples give me the correct data.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 07:47:36

Which version of SQL Server?

No, you're never too old to Yak'n'Roll if you're too young to die.

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-07-23 : 07:52:38

sql server 2008

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 07:55:05

select APT.PlayersID, APT.Training, APT.Date
from ArchivePlayerTraits AS APT
join
(select PlayersID, max(Date) as Date from ArchivePlayerTraits group by PlayersID)dt
on dt.PlayersID = APT.PlayersID and dt.Date = APT.Date

should work in each version.

Edit: ALIAS to columnname

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 07:57:17

There are more ways to do this, but first try if this is ok for you.

No, you're never too old to Yak'n'Roll if you're too young to die.

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-07-23 : 07:59:33

It works.
Now to go and figure out what you did.
Clever!

Thanks a million.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 08:12:10

You're welcome

Feel free to ask if you cannot figure out.

(I can imagine there will come some more posts in this thread where our mates will show other ways to do that

)

No, you're never too old to Yak'n'Roll if you're too young to die.

memphiz
Starting Member

3 Posts

Posted - 2009-08-06 : 18:44:00

I had a similar problem wherein I am trying to use the timestamp to update the latest status in another table. There are some records which have the same date and thats where I am trying to get the 'time' part to differentiate them and pick the latest one.

Update Prot
Set latest_status = (Select status.status from status inner join Prot on Status.[pnumber] = Prot2.[pnumber]
and Status.status_date = Prot2.max_status_date
where
status.time_stamp in (select max(status.time_stamp) from status))
Thank you so much for reading. Your time and help are greatly appreciated.
Regards,
M

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-07 : 05:51:39

It is not easy to understand because I am reading the questions here only "by the way" while working on my project.
It would be helpful if you could give in short:
Table structure (only the needed columns but with datatype)
Sample data
Wanted output (in relation to sample data)

Then I will try to code it for you.
Or maybe someone other...here are many mates.

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-07 : 06:56:46

[code]SELECT PlayersID,
Training,
Date
FROM (
SELECT PlayersID,
Training,
Date,
ROW_NUMBER() OVER (PARTITION BY PlayersID ORDER BY Date DESC) AS recID
FROM ArchivePlayerTraits
) AS d
WHERE recID = 1
ORDER BY PlayersID[/code]

N 56°04'39.26"
E 12°55'05.63"

memphiz
Starting Member

3 Posts

Posted - 2009-08-07 : 12:25:20

Sorry bout the lack of details.

Prot2 table is a master table (of sorts) with pnumber as the primary key. Every pnumber can have multiple entries in Status table, with different statuses and status_dates.

I am trying to pull the latest status date and the corresponding latest status in the prot2 table on an ongoing basis (by running the query in a job).

I got the query to update the latest status_date but unable to get the corresponding status.
Here is the structure of the 2 tables, followed by the query that I wrote for updating the status_date
Prot2 table
[pnumber] [varchar] (10), [study_title] [varchar] (500),
[time_stamp] [datetime] NOT NULL CONSTRAINT DEFAULT (getdate()),
[latest_status] [varchar](40) NULL, [max_status_date] [datetime] NULL

Status
[pnumber] [varchar] (10), [status] [varchar] (40),
[status_date] [datetime] NULL,
[time_stamp] [datetime] NOT NULL DEFAULT (getdate()),
[status_id] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT

--update prot2 max_status_date
update prot2
set max_status_date = (select max(status_date) from status where status.[pnumber] = prot2.[pnumber] group by pnumber)

memphiz
Starting Member

3 Posts

Posted - 2009-08-07 : 12:39:35

Apologize, I forgot to add: In the status table, there could also be multiple status entries for the same status_date.

A example would be:
pnumber Status Status_Date
E1234567 Active 2009-07-11 11:00:03
E1234567 Pending 2009-07-18 10:00:00
E1234567 Closed 2009-07-18 10:34:55

This is what the prot2 entry for this pnumber should look like if the queries work perfectly.
Prot2
pnumber latest_status max_status_date
E1234567 Closed 2009-07-18 10:34:55

Thank you once again for all the help.
Regards,
M

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-08 : 13:31:56

[code]declare @Prot2 table(
[pnumber] [varchar] (10),
[latest_status] [varchar](40) NULL,
[max_status_date] [datetime] NULL )

declare @Status table(
[pnumber] [varchar] (10),
[status] [varchar] (40),
[status_date] [datetime] NULL)

insert @Prot2
select 'E1234567','any status', null

insert @Status
select 'E1234567', 'Active', '2009-07-11 11:00:03' union all
select 'E1234567', 'Pending', '2009-07-18 10:00:00' union all
select 'E1234567', 'Closed', '2009-07-18 10:34:55'

--select * from @Prot2
--select * from @Status

-- Creating the solution using the testdata
update P2
set latest_status = dt.[Status],
max_status_date = dt.Status_date
from @Prot2 P2
join
(
select pnumber,
[Status],
Status_date,
row_number() over (partition by pnumber order by Status_date desc) as rownum
from @Status
)dt
on dt.pnumber = P2.pnumber and dt.rownum = 1

select * from @Prot2
result:
E1234567 Closed 2009-07-18 10:34:55.000
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources