Get the latest datetime daily for given period

Question

4.00/5 (1 vote)

See more:

Hello all,

With the following query I am trying to pull some data from a table:

SQL

SELECT c.CHANGEDBY, c.CODE, c.CHANGEDAT
FROM logs c
WHERE c.CLIENT='00' AND c.DB='DBNAME' AND c.CLASS='CLASSNAME' AND c.FUNCTION='FUNCNAME'

The table consists of the following columns
ID(int), DB(varchar), CLIENT(varchar), CLASS(varchar),FUNCTION(varchar), CODE(varchar), CREATEDBY(varchar), CREATEDAT(datetime), CHANGEDBY(varchar), CHANGEDAT(datetime)

When I run this query I have the output:

CHANGEDBY      CODE    CHANGEDAT
John-----------xyz-----2012-07-09 10:04:00
Ben------------abc-----2012-07-09 08:04:00
Walter---------def-----2012-07-08 07:15:00
Kate-----------ghf-----2012-07-08 11:35:00

------------------------------------------------------------------
What I want is I need the latest datetime for each day in this select query. So the output should be like:

CHANGEDBY      CODE    CHANGEDAT
John-----------xyz-----2012-07-09 10:04:00
Kate-----------ghf-----2012-07-08 11:35:00

When I use MAX(Date) it only brings 1 so can someone help me figure it out.
Thanks

Posted 10-Jan-13 1:47am

wonder-FOOL

Updated 10-Jan-13 2:12am

v4

Add a Solution

Comments

André Kraak 10-Jan-13 8:06am

Please give the table structure, as it might influence the answer.

wonder-FOOL 10-Jan-13 8:13am

I have added the columns that table has. I will be working on the same table so there is no need to join external tables.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-01-12T11:03:00

The best way to achieve that is to create stored procedure[^], like this one:

SQL

USE  YourDatabase;
GO

CREATE PROCEDURE GetLatestDailyChange
    @client VARCHAR(10),
    @db VARCHAR(30),
    @class VARCHAR(30),
    @function (30)

AS
    SELECT CHANGEDBY, CODE, CHANGEDAT
    FROM(
        SELECT ROW_NUMBER() OVER(PARTITION BY CHANGEDATE ORDER BY CHANGEDATE DESC) AS ID, * 
        FROM logs
        WHERE CLIENT=@client AND T.DB=@db AND T.CLASS=@class AND T.FUNCTION=@functions) AS T
    WHERE T.ID = 1
GO

More about CREATE PROCEDURE[^] commad.

After SP creation, you need to know how to call it. For example:

SQL

EXEC YourDatabase.GetLatestDailyChange @client = N'00', @db = N'DBNAME', @class=N'CLASSNAME', @function=N'FUNCNAME';

Tejal S · Answer 2 · 2013-01-10T03:17:00

SQL

SELECT C.ChangedBy, C.Code, C.ChangeDate
FROM dbo.logs c WITH(NOLOCK)
WHERE C.ChangeDate IN
(
SELECT MAX(C.ChangeDate)
                FROM dbo.logs c WITH(NOLOCK)
                WHERE CONVERT(DATE, , C.ChangeDate) = CONVERT(DATE, , C.ChangeDate)
                GROUP CONVERT(DATE, , C.ChangeDate)
)
AND c.CLIENT = ‘00’
AND c.DB = ‘DBNAME’
AND c.CLASS = ‘CLASSNAME’
AND c.FUNCTION = ‘FUNCNAME’

try this, you will get the data

Bhushan Shah1988 · Answer 3 · 2013-01-10T02:41:00

hey try below T-SQL Code :

SQL

CREATE TABLE #TEMP
(
    ID  INT,
    CHANGEDATE  DATETIME
)

INSERT INTO #TEMP VALUES(1,GETDATE())

INSERT INTO #TEMP VALUES(2,'2013-01-09 10:04:00')

INSERT INTO #TEMP VALUES(4,GETDATE())

INSERT INTO #TEMP VALUES(3,'2013-01-09 08:11:00')

SELECT DISTINCT ID, CHANGEDATE
FROM
(
    SELECT ID,CHANGEDATE,ROW_NUMBER() OVER(PARTITION BY CONVERT(VARCHAR(10),CHANGEDATE,103) ORDER BY CHANGEDATE DESC) AS ROWNUM
    FROM #TEMP
) TMP
WHERE ROWNUM = 1

DROP TABLE #TEMP

Get the latest datetime daily for given period

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0