Moving Excel application to SQL Server, date and identity issue when due date extended

[wbodger]

I have an Excel application where the users can currently enter a new date (appended) to the Due Date field, so Due Date might have as many as for or five dates and the one on the bottom is the most recent (in the spreasdsheet they are stacked, not linear). Now I have been asked to move this application to SQL Server and I am trying to come up with a solution for this issue. Another complicating factor is that the ID needs to be an auto-increment field and we need to keep the history of the Due Dates when they have changed (not the date they were changed, just the actual change). I had considered using a status field Active, Inactive, Closed and then writing a new line whenever the Due Date was changed, but then realized that would change the ID and I would no longer have my history. A few sample records would look like this, where the first field is the identity that is actually used to identify the record:

00404	9/3/2014	"Internal Audit Findings 2015.08.01 Nonconforming Product & Materials"	        Name Changed	3	"10/18/14 Extension to 11/17/14"
00405	9/4/2014	"Internal Audit Findings 2015.09.01 Manufacturing"	                        Name Changed	3	10/19/2014 
00406	9/23/2014	"Internal Audit Findings 2015.10.01Inspection"	                                Name Changed	3	"11/17/14 Extension to 11/24/14"
00407	9/23/2014	"Internal Audit Findings 2015.10.01Inspection (Header Tools for Inspection)"	Name Changed	3	"11/17/14 Extension to 11/24/14 Extension to 12/16/2014"

I know I could change the Due Date field to a nvarchar field, but I have to do date comparisons on that field and would rather not have to try to parse out the last date entered if I can avoid it. However, I can not currently see a way to avoid it unless I make a changelog table and write the original record to that table and then update record in the main table? Any other ideas?

Thanks,
Willie

 

[softhemc]

Could you not have a separate table linked to the table in your post by the ID column which holds the date in question.

[tt]ID[tab] FKey[tab] TheDate
001[tab]00404[tab]9/3/2014
002[tab]00404[tab]10/3/2014
003[tab]00405[tab]9/4/2014
004[tab]00405[tab]10/4/2014
etc., etc
[/tt]

 

[wbodger]

That was really the only viable solution that I could come up with, so I was wondering if there were any other ideas out there. The problem that I see with this solution is actually the original loading of the data. How would I split out the extension date on the initial load?

Thanks,
wb

 

[philhege]

You would have to parse the Excel cell that contains the dates. If you're using SSIS (and in this case I recommend that you do), insert a script task to parse out the multiple dates and insert into the date/record table.

-----------
With business clients like mine, you'd be better off herding cats.