October 22, 2014 at 12:02 am
Hi all!
I have this table:
CREATE TABLE [dbo].[DutyRosterShiftHisto](
[Comment] [char](120) NULL,
[Time_Stamp] [datetime] NULL,
[StatusNo] [int] NULL,
[LoginID] [int] NULL,
[Std] [tinyint] NULL,
[SpecialVagt] [tinyint] NULL,
[DaekBemand] [tinyint] NULL,
[ExtraTimer] [real] NULL,
[Manuel] [tinyint] NULL,
[VacationType] [char](50) NULL,
[DutyRosterShiftId] [int] NULL,
[EmployeeId] [int] NULL,
[EmployeeGroupId] [int] NULL,
[ChildForCareDayId] [int] NULL,
[ShiftType] [int] NULL,
[FromTime] [int] NULL,
[ToTime] [int] NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
[DutyRosterId] [int] NULL,
[Is_Free_sat] [tinyint] NULL,
[Is_Center_Opening] [tinyint] NULL,
[is_fo_day] [tinyint] NULL,
[SavedDuty_Id] [int] NULL,
[OverArbTimer] [real] NULL,
[Beskyttet] [tinyint] NULL,
[Confirmed] [tinyint] NULL,
[BreaksWish] [tinyint] NULL,
[OriginatingStaffingRequirementId] [int] NULL,
[mTid_Id] [int] NULL,
[duty_released] [tinyint] NULL,
[Dato] [datetime] NULL,
CONSTRAINT [PK_DutyRosterShiftHisto] PRIMARY KEY CLUSTERED
(
[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]
The interesting fields are:
[Manuel], which can be 0,1 and 10. There can only be one field with the value 10
And
[TIMESTAMP], which of cause is date and time for creating the record.
If i ORDER BY [TIMESTAMP], I want the record after the one with [MANUEL] = 10
In my normal programming its just a loop, but how to make a query?
Best Regards
Edvard Korsbæk
October 22, 2014 at 1:02 am
Add a row number with ROW_NUMBER() OVER (ORDER BY Timestamp).
Find the row where Manual = 10 and get its row number x. The row you seek has row number (x + 1).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 30, 2014 at 2:36 am
If I understand your question then this should suffice
SELECT TOP 1
*
FROM
DutyRosterShiftHisto
WHERE
Time_Stamp > (SELECT Time_Stamp FROM DutyRosterShiftHisto WHERE Manuel=10)
ORDER BY
Time_Stamp
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply