Hello Everyone!!
So I have a database and need some design tips on running some queries. It basically works as it collects all raw data from an excel file that is imported. Nothing really makes them unique as a few columns can have different information, but for the most part the same. So here is what I am trying to do. An sales guy goes out and opens up issues with a vendor. The next sales guy goes out and opens up new issues and closes some old issues. I want a query that will take the time of when the issue opened and then when it closed.
The data that goes into the database is 8 columns and it has to be matched by col1, col2, col3, col4, col5, col6 and then see if col7 was opened vs closed and spit out the date between the same column, but stored with different information.
So an example would be sales guy uploads original excel file -
Column 1 - sales call # - 15C1XXXX
Column 2 - sales type - General
Column 3 - Sales topic - product
Column 4 - Sales description - blah..blah..blah
Column 5 - Vendor notes - blah blah blah
Column 6 - Sales Notes - blah blah blah
Column 7 - status - Open
Column 8 - uploaded date 1/1/15
Column 9 - uploaded time 02:00pm
Then sales guy 2 comes along and closes the above item with new Sales Notes, Status, uploaded date and uploaded time. The columns 1, 2, 3, 4, and 5 in theory should stay the same. Now the issue is that the sales call # 15C1XXXX could be entered in 1 or 100 times depending on how many issues are created that visit. So we have to match other columns as well to make sure we close that exact issue. So once we do that then I want to take the two different dates and subtract them. So I know how many days it took to close that issue. I would like to then display the columns from the entry of when it was closed and not originally opened. So this is complicated I know, but if anyone can help to get me on the right path that would be greatly appreciated. I will need to do something similar, but for other reports. If I can see one example, I hope I can figure out the rest. Thanks a bunch!!
OR
Would it make more sense to re-write my import code to have all "open" issues go into one database and then all "closed" issues go into a different database and then form relationships between the two and write my queries that way? I am not sure how I would attempt to write a query based on relationships, but maybe easier??
So I have a database and need some design tips on running some queries. It basically works as it collects all raw data from an excel file that is imported. Nothing really makes them unique as a few columns can have different information, but for the most part the same. So here is what I am trying to do. An sales guy goes out and opens up issues with a vendor. The next sales guy goes out and opens up new issues and closes some old issues. I want a query that will take the time of when the issue opened and then when it closed.
The data that goes into the database is 8 columns and it has to be matched by col1, col2, col3, col4, col5, col6 and then see if col7 was opened vs closed and spit out the date between the same column, but stored with different information.
So an example would be sales guy uploads original excel file -
Column 1 - sales call # - 15C1XXXX
Column 2 - sales type - General
Column 3 - Sales topic - product
Column 4 - Sales description - blah..blah..blah
Column 5 - Vendor notes - blah blah blah
Column 6 - Sales Notes - blah blah blah
Column 7 - status - Open
Column 8 - uploaded date 1/1/15
Column 9 - uploaded time 02:00pm
Then sales guy 2 comes along and closes the above item with new Sales Notes, Status, uploaded date and uploaded time. The columns 1, 2, 3, 4, and 5 in theory should stay the same. Now the issue is that the sales call # 15C1XXXX could be entered in 1 or 100 times depending on how many issues are created that visit. So we have to match other columns as well to make sure we close that exact issue. So once we do that then I want to take the two different dates and subtract them. So I know how many days it took to close that issue. I would like to then display the columns from the entry of when it was closed and not originally opened. So this is complicated I know, but if anyone can help to get me on the right path that would be greatly appreciated. I will need to do something similar, but for other reports. If I can see one example, I hope I can figure out the rest. Thanks a bunch!!
OR
Would it make more sense to re-write my import code to have all "open" issues go into one database and then all "closed" issues go into a different database and then form relationships between the two and write my queries that way? I am not sure how I would attempt to write a query based on relationships, but maybe easier??
Last edited:
