Click here to Skip to main content
12,952,277 members (46,475 online)
Rate this:
 
Please Sign up or sign in to vote.
Hello all,

With the following query I am trying to pull some data from a table:
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
Updated 10-Jan-13 2:12am
v4
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

hey try below T-SQL Code :

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
  Permalink  
Comments
wonder-FOOL 11-Jan-13 4:56am
   
Thank you!
Bhushan Shah1988 11-Jan-13 4:58am
   
mark as answer if it helps you.
Maciej Los 12-Jan-13 17:04pm
   
ROWNUMBER() is good idea, my 5!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
Comments
wonder-FOOL 11-Jan-13 4:56am
   
Thank you!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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

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:
EXEC YourDatabase.GetLatestDailyChange @client = N'00', @db = N'DBNAME', @class=N'CLASSNAME', @function=N'FUNCNAME';
  Permalink  

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

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,239
CHill60 3,490
Maciej Los 3,083
Jochen Arndt 1,975
ppolymorphe 1,880


Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 12 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100