0

I have two tables that I want to compare but with a twist. One table have all our projects and the other have all the Project Issue statuses.

The Project table have a list of Projects and besides the obvious columns we have a column with assigned Status IDs and each Status ID is separated by pipe. The Issue Status table is a standard table with the Status ID in the first column and the Status Name in a different column.

For every project we can in the UI assign which Statuses that should be seen and used in that project and the list of assigned Status will then be represented in the ProjectStatusIds column in the Project table.

So basically the Project table look like this:

ProjectId, ProjectName, ProjectIssueStatusIds, ... 1, Project1, 1|2|5|6|8|9|100, ... 2, Project2, 1|2|6|7|8|9|100, ... 3, Project3, 1|3|8|9|11|15|100, ... ...

And the Issue Status table basically looks like this:

StatusId, StatusName, ... 1, New, ... 2, Assigned, ... 3, Work in Progress, ... 4, On Hold, ... ... 100, Closed, ...

Does the above explanation make sense to you? If not please ask me to clarify.

Anywho, since this is a system I have inherited I suspect that we have a lot Status:es that are never used so instead of simply adding more statuses I would like to know which Statuses that are not assigned to any project.

Can this be done in a query or do I need to write some kind of script to do the work for me? I would prefer to do this using a query since I then more easily can add this to the "system management report package".

Thank you in advance

Erik
  • 1
  • 2
    See normalisation. – Strawberry Apr 21 '17 at 08:34
  • Storing pipe-separated data in the `ProjectIssueStatusIds` column sounds like an all around bad idea. Don't do that, instead put each status in a separate record. – Tim Biegeleisen Apr 21 '17 at 08:34
  • Tim: I agree it is a bad idea, but I have no control of the SW or the DB. I can only work with what the Vendor provides. Strawberry: What do you mean with "normalisation"? Can you please elaborate? – Erik Apr 24 '17 at 14:48

0 Answers0