asked
Finding Max Date
I have a table with branch and creation date and GP. The create date is date and time. I am trying to get the group of records with the highest create date/time of each day. Branch Create Date/time GP --------- -------------------------- ------- 10 2017.03.01 130000 $100 20 2017.03.01 130000 $125 10 2017.03.01 140000 $150 20 2017.03.01 140000 $125 10 2017.03.01 150000 $115 20 2017.03.01 150000 $175 My results should be: 10 2017.03.01 1500 $115 20 2017.03.01 1500 $175
Practically speaking, no. In the source table there could be a single or multiple record(s) (Create Date/time column) for each Branch. The end result would have one record per Branch per Day where the Create Date/time is the Max for that Branch. The process loads once or multiple times a day, so I might have 10 branches all with the same ‘time stamp’ loaded in the morning, then the same 10 Branches loaded in the afternoon, then again later in the afternoon. So I would want the group from the “later in the afternoon” which would be the Max ‘Create Date/time’. All groups have the same Branch and Branch count. Does this help?
See below. A mock up of data with a proposed method / solution at the end in the final SELECT statement.
SET NOCOUNT ON IF OBJECT_ID (N'tempdb.dbo.#temp_example', N'U') IS NOT NULL BEGIN DROP TABLE dbo.#temp_example END CREATE TABLE dbo.#temp_example ( branch INT NOT NULL ,create_date_time DATETIME NOT NULL ,gp DECIMAL (18, 2) NOT NULL ) INSERT INTO dbo.#temp_example ( branch ,create_date_time ,gp ) VALUES (10, N'2017.03.01 13:00:00', 100) ,(20, N'2017.03.01 13:00:00', 125) ,(10, N'2017.03.01 14:00:00', 150) ,(20, N'2017.03.01 14:00:00', 125) ,(10, N'2017.03.01 15:00:00', 115) ,(20, N'2017.03.01 15:00:00', 175) SELECT X.* FROM dbo.#temp_example X INNER JOIN ( SELECT X.branch ,MAX (X.create_date_time) AS create_date_time FROM dbo.#temp_example X GROUP BY X.branch ) sqX ON sqX.branch = X.branch AND sqX.create_date_time = X.create_date_time IF OBJECT_ID (N'tempdb.dbo.#temp_example', N'U') IS NOT NULL BEGIN DROP TABLE dbo.#temp_example END
You can also use Dense_Rank function CREATE TABLE #temp_example ( branch INT NOT NULL ,create_date_time DATETIME NOT NULL ,gp DECIMAL (18, 2) NOT NULL ) INSERT INTO #temp_example ( branch ,create_date_time ,gp ) VALUES (10, N'2017.03.01 13:00:00', 100) ,(20, N'2017.03.01 13:00:00', 125) ,(10, N'2017.03.01 14:00:00', 150) ,(20, N'2017.03.01 14:00:00', 125) ,(10, N'2017.03.01 15:00:00', 115) ,(20, N'2017.03.01 15:00:00', 175) ;with CTE_1 AS ( SELECT DENSE_RANK() OVER( ORder by create_date_time DESC ) AS Rank_ID, * FROM #temp_example ) SELECT * FROM CTE_1 WHERE Rank_ID = 1
20 People are following this question.
