Assign Sequential Number to Dates

[sharkey_lsu]

I am working with a developer to create a database. One thing that needs to happen is for there to be Day numbers that are determined by the sequential order of the Dates of the records. Could someone please provide a little information as to how to achieve this? An example of the result that I am hoping to get:



June 3, 2016 Day 1
June 3, 2016 Day 1
June 3, 2016 Day 1
June 5, 2016 Day 2
June 5, 2016 Day 2
June 5, 2016 Day 2
June 8, 2016 Day 3
June 8, 2016 Day 3
June 8, 2016 Day 3
June 10, 2016 Day 4
June 10, 2016 Day 4

As shown above, the Day No is based on the sequence of the Date. And if someone were to go in and add a June 2, 2016...then all of the Day Numbers would need to shift accordingly. Is this possible?

[orange]

Hmmm.. Please tell us more.
Perhaps you could describe why there are 3 (June 8, 2016 Day 3) records. Some context for your post would be helpful to readers.

[sharkey_lsu]

My apologies. Here is a description in a nutshell:

The database is an inspection management system. Multiple inspectors will go to a site and check requirements for infractions each using offline backend databases. They will at any time available, connect and sync the data via SQL Server so that it is shared. These inspections will take place over multiple days and each day needs to be assigned a Day Number.

The problem is that the inspectors will not necessarily be working on the same sequential date/day. IE when Inspector A creates a new day (Day 1) and makes findings, the findings will all be associated with the Day/Date that he has created. Inspector B might not start his inspection until several days in, therefore when he creates a New Day, the offline DB sees this as Day 1 (assuming the inspectors do not have internet connection to sync their data). However, once he syncs, all of his data should be associated with the correct Day (if the dates match the Day should be assigned).

[orange]

But won't the inspections(comments/notes/infractions...) all refer to a property or site. (The inspected thingy)
I can see an InspectionStartDate and possibly InspectionEndDate, and individual notes/comments be Inspector-NameStamped and DateTime stamped. I would think each Inspection would have a unique identifier, and each site a unique identifier.

A site has 1 or many inspections
An inspection has 1 or many Inspectors
An Inspector can make 0,1 or many Comments related to an Inspection

If you plan Inspections, I could see a PlannedInspection---proposed StartDate, proposedEnddate sort of thing also.