Desgining the table and joining to the existing table

1

Hi,

I need help with organizing data or desginig new data.
we already have a table with a sample of jobs coming in and with different statuses.

Below is the sample of existing table:

Job ID, Job Number, Completion Date, Status,Analyst Name
111, TW-DH-27, 12/12/2018, Completed, John1
112,TW-DH-28,1/1/2018, Completed, John2
113, TW-DH-29, 12/13/2018, Completed, John1
114,TW-DH-30,1/2/2018, Completed, John2
115, TW-DH-31, 12/14/2018, Completed, John1
116,TW-DH-32,1/21/2018, Completed, John2

Now, I need to give users some kind of flexibility to input # of working days for each month for all analysts in the above table.

So, In the new table, I need Analyst Name, Month Name, # of days worked.

I have some flexibility in the new 2nd table to design. After desigining the 2nd table, I need to join both and see, how many completed jobs are there for each analyst and how many hours worked for each month and year.

Not sure how to design the 2nd table to join to the 1st table. Please help!

2

IMO, you should have several different tables and normalize it. JobID and Jobnumber shouldn't be in this table. This is a status table. Even the status itself should be in a separate table as well as the Analyst's name. The Analysts table should contain info about the analyst, then a child table to house the dates and times they worked.

3

I don't have flexibility to change the 1st table as they are being administered and designed by DBA. They designed this view to solve our requirements by putting everything regarding job in the view. However, we do not have # of days worked per month in that view.

So, I need to design a table or view so that I can merge both the tables and find out how many hours an analyst worked and how many jobs in a month. And similar one for year. Please help!

4

so, that's a view and not a table? if so, what are the underlying tables and structures?