March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Here is the cenario that I need help:
I have 2 tables.
Table-1: WorkItems. This table has all latest Workitems from specific project with isCurrent = TRUE for all records.
WorItemId | Title | WorkIemType | State | Priority | ParentWorkItemId | Program | AssignedTo | Story Points | OriginalEstimate | CompletedWork | RemainingWork | IsCurrent |
40001 | Task1 | Task | New | 30001 | Project1 | Prabhat | 5 | 0 | 5 | TRUE | ||
40002 | Task2 | Task | Active | 30001 | Project1 | Prabhat | 6 | 2 | 4 | TRUE | ||
40003 | Task3 | Task | Closed | 30001 | Project1 | Rupak | 7 | 7 | 0 | TRUE | ||
40004 | Task4 | Task | Active | 30002 | Project1 | Alok | 2 | 1 | 1 | TRUE | ||
40005 | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 0 | 6 | TRUE | ||
40006 | Task6 | Task | Closed | 30003 | Project1 | Prabhat | 4 | 4 | 0 | TRUE | ||
40007 | Task7 | Task | Active | 30003 | Project1 | Prabhat | 8 | 2 | 6 | TRUE | ||
40008 | Task8 | Task | Active | 30003 | Project1 | Prabhat | 3 | 1 | 2 | TRUE | ||
40009 | Task9 | Bug | New | 30004 | Project1 | Prabhat | 1 | 6 | 0 | 6 | TRUE | |
40010 | Task10 | Task | Active | 30007 | Project2 | Lokesh | 3 | 1 | 2 | TRUE | ||
40011 | Task11 | Task | Active | 30007 | Project2 | Hari | 5 | 3 | 2 | TRUE | ||
40012 | Task12 | Task | Active | 30007 | Project2 | Srinivas | 8 | 1 | 7 | TRUE | ||
40013 | Task13 | Task | Active | 30008 | Project2 | Rajesh | 3 | 0 | 3 | TRUE | ||
40014 | Task14 | Task | Active | 30008 | Project2 | Suma | 3 | 3 | 0 | TRUE |
Table-2: Revisions. This table has History records for all workitems from ALL projects where isCurrent = FALSE for all records.
WorItemId | Rev | RevDate | IsCurrent | Title | WorkIemType | State | Priority | ParentWorkItemId | Program | AssignedTo | Story Points | OriginalEstimate | CompletedWork | RemainingWork |
40001 | 1 | 1-Jan-24 | FALSE | Task1 | Task | New | 30001 | Project1 | Prabhat | 5 | 0 | 5 | ||
40001 | 2 | 2-Jan-24 | FALSE | Task1 | Task | New | 30001 | Project1 | Prabhat | 5 | 1 | 4 | ||
40001 | 3 | 3-Jan-24 | FALSE | Task1 | Task | New | 30001 | Project1 | Prabhat | 5 | 1 | 4 | ||
40001 | 4 | 4-Jan-24 | FALSE | Task1 | Task | New | 30001 | Project1 | Prabhat | 5 | 2 | 3 | ||
40005 | 1 | 1-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 0 | 6 | ||
40005 | 2 | 4-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 1 | 5 | ||
40005 | 3 | 5-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 1 | 5 | ||
40005 | 4 | 6-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 3 | 3 | ||
40005 | 5 | 7-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 3 | 3 | ||
40005 | 6 | 8-Jan-24 | FALSE | Task5 | Task | New | 30002 | Project1 | Prabhat | 6 | 4 | 2 | ||
40011 | 1 | 7-Jan-24 | FALSE | Task11 | Task | Active | 30007 | Project2 | Hari | 5 | 3 | 2 | ||
40012 | 1 | 8-Jan-24 | FALSE | Task12 | Task | Active | 30007 | Project2 | Srinivas | 8 | 1 | 7 | ||
40012 | 2 | 9-Jan-24 | FALSE | Task12 | Task | Active | 30007 | Project2 | Srinivas | 8 | 2 | 6 | ||
40012 | 3 | 10-Jan-24 | FALSE | Task12 | Task | Active | 30007 | Project2 | Srinivas | 8 | 2 | 6 | ||
40015 | 1 | 7-Jan-24 | FALSE | Task100 | Task | Active | 30089 | Project20 | Srinivas | 8 | 2 | 6 | ||
40016 | 1 | 10-Jan-24 | FALSE | Task101 | Task | Active | 30078 | Project20 | Srinivas | 8 | 2 | 6 |
I want to have a new table that will store ALL records from WorkItems and ONLY Matching records (Based on WorkItemId) from Revisions table. The Rev and RevDate columns can be NULL in the new table for the record that is coming from WorkItems.
Thanks,
Prabhat
Solved! Go to Solution.
Hey @prabhatnath ,
I think I got your question.
You can first do an inner of the second table with the first, then only the IDs will be left that exist in the first table.
Afterwards you can do an append.
Please see the attached file for details.
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
Hey @prabhatnath ,
I think I got your question.
You can first do an inner of the second table with the first, then only the IDs will be left that exist in the first table.
Afterwards you can do an append.
Please see the attached file for details.
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
90 | |
86 | |
77 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |