在给定时间内每天获取最新的日期时间 [英] Get the latest datetime daily for given period
问题描述
大家好,
通过以下查询,我试图从表中提取一些数据:
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'
该表由以下列组成
ID(int),DB(varchar ),CLIENT(varchar),CLASS(varchar),FUNCTION(varchar),CODE(varchar),CREATEDBY(varchar),CREATEDAT(datetime),CHANGEDBY(varchar),CHANGEDAT(datet) ime)
当我运行此查询时,我有输出:
CHANGEDBY 你> 代码 CHANGEDAT
John ----------- xyz ----- 2012-07-09 10:04:00
Ben ------------ abc ----- 2012-07-09 08:04:00
沃尔特--------- def-- --- 2012-07-08 07:15:00
Kate ----------- ghf ----- 2012-07-08 11:35:00
---------------------------------------- --------------------------
我想要的是在这个选择查询中我需要每天的最新日期时间。所以输出应该是这样的:
CHANGEDBY 代码 CHANGEDAT $ b $约翰----------- xyz ----- 2012-07-09 10:04:00
Kate ----------- ghf ---- -2012-07-08 11:35:00
当我使用MAX(日期)时它只带1,所以有人可以帮我搞清楚。
谢谢
嘿试试下面的T-SQL代码:
创建 表 #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
SELECT C.ChangedBy,C.Code,C.ChangeDate
FROM dbo.logs c WITH ( NOLOCK )
WHERE C.ChangeDate IN
(
< span class =code-keyword> SELECT MAX(C.ChangeDate)
FROM dbo.logs c WITH ( NOLOCK )
WHERE < span class =code-keyword> 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'
尝试这个,你将获得数据
实现这一目标的最佳方法是创建store d程序 [ ^ ],就像这样:
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 记录
WHERE CLIENT = @ client AND T.DB =@db AND T. CLASS = @ class AND T.FUNCTION=@functions) AS T
WHERE T.ID = 1
GO
有关的更多信息CREATE PROCEDURE [ ^ ] commad。
创建SP后,您需要知道如何调用它。例如:
EXEC YourDatabase.GetLatestDailyChange @ client = N ' 00', @ db = N ' DBNAME',@ class = N ' CLASSNAME',@ function = N ' FUNCNAME'跨度>;
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
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
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
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';
这篇关于在给定时间内每天获取最新的日期时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!