Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL MySQL T-SQL databse
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 2:47am
Edited 10-Jan-13 3:12am
v4
Comments
André Kraak at 10-Jan-13 8:06am
   
Please give the table structure, as it might influence the answer.
wonder-FOOL at 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 at 11-Jan-13 4:56am
   
Thank you!
Bhushan Shah1988 at 11-Jan-13 4:58am
   
mark as answer if it helps you.
Maciej Los at 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 at 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)



Advertise | Privacy | Mobile
Web04 | 2.8.1411022.1 | Last Updated 12 Jan 2013
Copyright © CodeProject, 1999-2014
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