October 3, 2014 at 1:07 am
I have this table:
CREATE TABLE [dbo].[save_schema_totals](
[Save_Schema_Id] [int] IDENTITY(1,1) NOT NULL,
[Timesum_string] [varchar](12) NULL,
[Normtime_String] [varchar](12) NULL,
[plus_minus_string] [varchar](12) NULL,
[Roster_Type] [tinyint] NULL,
[roster_id] [int] NULL,
[Employee_Id] [int] NULL,
[saved_datetime] [datetime] NULL,
[period_startdate] [datetime] NULL,
[period_Enddate] [datetime] NULL,
CONSTRAINT [PK_save_schema_totals] PRIMARY KEY CLUSTERED
(
[Save_Schema_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
This query gives me the correct post for employee_id 52
select top 1 employee_id,Timesum_string,Normtime_String,plus_minus_string from dbo.save_schema_totals where roster_id = 3 and period_startdate = '2014-05-05' and period_Enddate = '2014-10-19' And Employee_Id = 52 order by saved_datetime desc
TOP 1 is used for getting the newest record for employee 52
But what i need is the newest record for all employee's
How to?
Best regards
Edvard Korsbæk
For info, is the first 25 records in the table:
Save_Schema_IdTimesum_stringNormtime_Stringplus_minus_stringRoster_Typeroster_idEmployee_Idsaved_datetimeperiod_startdateperiod_Enddate
1 872:00 816:00 56:0003522014-06-19 10:50:09.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
2 872:00 816:00 56:0003522014-06-19 10:51:53.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
3 907:00 816:00 91:0003522014-06-20 10:23:14.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
4 907:00 816:00 91:0003522014-06-30 14:45:27.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
5 888:00 888:0003142014-07-02 20:25:05.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
6 300:00 300:0003272014-07-02 20:25:05.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
7 720:00 720:0003452014-07-02 20:25:05.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
8 888:00 888:0003632014-07-02 20:25:05.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
9 96:00 96:0003622014-07-02 20:25:05.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
10 943:00 888:00 55:0003602014-07-02 20:25:05.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
11 360:00 360:0003642014-07-02 20:25:05.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
12 360:00 360:0003612014-07-02 20:25:05.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
13 888:00 888:0003712014-07-02 20:25:06.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
14 888:00 888:0003382014-07-02 20:25:06.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
15 1 073:29 888:00 185:2903422014-07-02 20:25:06.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
16 888:00 888:0003672014-07-02 20:25:06.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
17 648:00 648:0003692014-07-02 20:25:06.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
18 888:00 888:0003482014-07-02 20:25:06.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
19 888:00 888:0003662014-07-02 20:25:06.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
20 916:00 888:00 28:0003432014-07-02 20:25:06.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
21 516:30 408:00 108:3003592014-07-02 20:25:06.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
22 957:24 888:00 69:2403532014-07-02 20:25:06.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
23 1 073:29 888:00 185:2903422014-07-02 20:25:06.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
24 860:30 888:00 27:300392014-07-02 20:25:06.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
25 765:00 720:00 45:0003512014-07-02 20:25:06.0002014-05-05 00:00:00.0002014-10-19 00:00:00.000
October 3, 2014 at 5:17 am
Which column do you use to determine the "newest record"?
October 3, 2014 at 5:37 am
Going off your existing query, I presume you're using saved_datetime to determine which row is the last one. If that's true, then this should get you the most recent row for all employees. If not, you can update the column on which to sort as needed.
WITH cte AS (
SELECT employee_id, Timesum_string, Normtime_String, plus_minus_string,
ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY saved_datetime DESC) row_num
FROM dbo.save_schema_totals
)
SELECT employee_id, Timesum_string, Normtime_String, plus_minus_string
FROM cte
WHERE row_num = 1;
The real work is done by the ROW_NUMBER function's OVER clause.
October 4, 2014 at 2:54 pm
Thanks a lot!
Did the trick
And I learned something new today - Both the WITH and The ROW_Number() OVER.
Best regards
Edvard Korsbæk
October 6, 2014 at 6:45 am
Edvard Korsbæk (10/4/2014)
Thanks a lot!Did the trick
And I learned something new today - Both the WITH and The ROW_Number() OVER.
Best regards
Edvard Korsbæk
I'm glad it helped. Thanks for the feedback.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply