Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE #temptable
- (
- [ActivityId] int,
- [OpportunityId] int,
- [Appointment_Service] nvarchar(255),
- [CreatedOn] datetime,
- [Appt_Booked_Date] datetime,
- [Appointment_State] nvarchar(255),
- [Appointment_Status] nvarchar(255),
- [RN] bigint,
- [LASTEST_First_Appointment] datetime,
- [LASTEST_Second_Appointment] datetime
- )
- INSERT INTO #temptable
- VALUES
- (4176537, 105994, N'1st Appointment', N'2015-01-27T00:00:00', N'2015-01-27T00:00:00', N'Closed', N'Attended', 1, N'2015-01-27T00:00:00', N'2017-11-21T11:25:46' ),
- (4176536, 105994, N'2nd Appointment', N'2015-01-28T00:00:00', N'2015-01-28T00:00:00', N'Closed', N'Attended', 2, N'2015-01-28T00:00:00', N'2017-11-21T11:25:46' ),
- (19656494, 105994, N'Other Appointment', N'2017-03-31T15:33:26', N'2017-03-31T15:33:26', N'Scheduled', N'In Progress', 3, N'2017-11-21T11:25:46', N'2017-11-21T11:25:46' ),
- (9394803, 105994, N'Other Appointment', N'2017-04-28T06:38:56', N'2017-04-28T06:38:56', N'Scheduled', N'In Progress', 4, N'2017-11-21T11:25:46', N'2017-11-21T11:25:46' ),
- (3774963, 105994, N'Other Appointment', N'2017-04-28T17:20:06', N'2017-04-28T17:20:06', N'Scheduled', N'In Progress', 5, N'2017-11-21T11:25:46', N'2017-11-21T11:25:46' )
- SELECT
- ActivityId, OpportunityId,
- Appointment_Service, CreatedOn,
- Appt_Booked_Date,
- Appointment_State, Appointment_Status,
- ROW_NUMBER() OVER (PARTITION BY OpportunityId ORDER BY CreatedOn, ActivityId) AS RN,
- LAST_VALUE(CreatedOn) OVER (PARTITION BY Appointment_Service ORDER BY CreatedOn, ActivityId ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LASTEST_First_Appointment
- FROM
- dbo.DIM_APPOINTMENT
Add Comment
Please, Sign In to add comment