Advertisement
Guest User

Untitled

a guest
Jul 19th, 2017
48
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.86 KB | None | 0 0
  1. --Set up our test data (testing only, not needed for your final code)
  2. CREATE TABLE #project (projectid int, projectname varchar(20))
  3.  
  4. INSERT INTO #project SELECT 1, 'Test project'
  5. INSERT INTO #project SELECT 2, 'Test project 2'
  6.  
  7. CREATE TABLE #activity (projectid int, activityname varchar(20), ID int, datecreated datetime)
  8.  
  9. INSERT INTO #activity SELECT 1, 'Activity 1', 10, getdate()
  10. INSERT INTO #activity SELECT 1, 'Activity 2', 11, getdate()
  11. INSERT INTO #activity SELECT 1, 'Activity 3', 12, getdate()
  12. INSERT INTO #activity SELECT 1, 'Activity 4', 13, getdate()
  13. INSERT INTO #activity SELECT 2, 'Activity 6', 14, getdate()
  14. INSERT INTO #activity SELECT 2, 'Activity 7', 15, getdate()
  15. INSERT INTO #activity SELECT 2, 'Activity 8', 16, getdate()
  16. INSERT INTO #activity SELECT 2, 'Activity 9', 17, getdate()
  17.  
  18. --Set up results temp table (You will need to keep this in your final code)
  19. CREATE TABLE #results (projectid int, projectname varchar(20), activityname varchar(20), activityid int, datecreated datetime)
  20.  
  21. --Cursor through the table, one ProjectID at a time. Building our results table
  22. DECLARE @ProjectID int
  23.  
  24. DECLARE ProjectReport CURSOR FOR
  25.  
  26. SELECT ProjectID
  27. FROM #project p
  28.  
  29. OPEN ProjectReport
  30.  
  31. FETCH NEXT FROM ProjectReport into @ProjectID
  32.  
  33. WHILE @@FETCH_STATUS = 0
  34. BEGIN
  35.  
  36. INSERT INTO #results
  37. SELECT p.projectid, p.projectname, a.activityname, a.ID, a.datecreated
  38. FROM #project p
  39. INNER JOIN (SELECT TOP(3) projectid, activityname, ID, datecreated
  40. FROM #activity
  41. WHERE projectid = @ProjectID <-- Gives you the top 3 most recent by Project, via ID. You can change this to by date if that is more accurate
  42. ORDER BY ID DESC) a on a.projectid = p.projectid
  43.  
  44. FETCH NEXT FROM ProjectReport INTO @ProjectID
  45.  
  46. END
  47.  
  48. CLOSE ProjectReport
  49.  
  50. DEALLOCATE ProjectReport
  51.  
  52. --Get your results
  53. SELECT * FROM #results
  54.  
  55. --Clean up temp table
  56. DROP TABLE #results
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement