Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --Set up our test data (testing only, not needed for your final code)
- CREATE TABLE #project (projectid int, projectname varchar(20))
- INSERT INTO #project SELECT 1, 'Test project'
- INSERT INTO #project SELECT 2, 'Test project 2'
- CREATE TABLE #activity (projectid int, activityname varchar(20), ID int, datecreated datetime)
- INSERT INTO #activity SELECT 1, 'Activity 1', 10, getdate()
- INSERT INTO #activity SELECT 1, 'Activity 2', 11, getdate()
- INSERT INTO #activity SELECT 1, 'Activity 3', 12, getdate()
- INSERT INTO #activity SELECT 1, 'Activity 4', 13, getdate()
- INSERT INTO #activity SELECT 2, 'Activity 6', 14, getdate()
- INSERT INTO #activity SELECT 2, 'Activity 7', 15, getdate()
- INSERT INTO #activity SELECT 2, 'Activity 8', 16, getdate()
- INSERT INTO #activity SELECT 2, 'Activity 9', 17, getdate()
- --Set up results temp table (You will need to keep this in your final code)
- CREATE TABLE #results (projectid int, projectname varchar(20), activityname varchar(20), activityid int, datecreated datetime)
- --Cursor through the table, one ProjectID at a time. Building our results table
- DECLARE @ProjectID int
- DECLARE ProjectReport CURSOR FOR
- SELECT ProjectID
- FROM #project p
- OPEN ProjectReport
- FETCH NEXT FROM ProjectReport into @ProjectID
- WHILE @@FETCH_STATUS = 0
- BEGIN
- INSERT INTO #results
- SELECT p.projectid, p.projectname, a.activityname, a.ID, a.datecreated
- FROM #project p
- INNER JOIN (SELECT TOP(3) projectid, activityname, ID, datecreated
- FROM #activity
- 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
- ORDER BY ID DESC) a on a.projectid = p.projectid
- FETCH NEXT FROM ProjectReport INTO @ProjectID
- END
- CLOSE ProjectReport
- DEALLOCATE ProjectReport
- --Get your results
- SELECT * FROM #results
- --Clean up temp table
- DROP TABLE #results
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement