Guest User

Untitled

a guest
Nov 25th, 2017
62
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.68 KB | None | 0 0
  1. CREATE TABLE #temptable
  2. (
  3. [ActivityId] int,
  4. [OpportunityId] int,
  5. [Appointment_Service] nvarchar(255),
  6. [CreatedOn] datetime,
  7. [Appt_Booked_Date] datetime,
  8. [Appointment_State] nvarchar(255),
  9. [Appointment_Status] nvarchar(255),
  10. [RN] bigint,
  11. [LASTEST_First_Appointment] datetime,
  12. [LASTEST_Second_Appointment] datetime
  13. )
  14.  
  15. INSERT INTO #temptable
  16. VALUES
  17. (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' ),
  18. (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' ),
  19. (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' ),
  20. (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' ),
  21. (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' )
  22.  
  23. SELECT
  24. ActivityId, OpportunityId,
  25. Appointment_Service, CreatedOn,
  26. Appt_Booked_Date,
  27. Appointment_State, Appointment_Status,
  28. ROW_NUMBER() OVER (PARTITION BY OpportunityId ORDER BY CreatedOn, ActivityId) AS RN,
  29. LAST_VALUE(CreatedOn) OVER (PARTITION BY Appointment_Service ORDER BY CreatedOn, ActivityId ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LASTEST_First_Appointment
  30. FROM
  31. dbo.DIM_APPOINTMENT
Add Comment
Please, Sign In to add comment