SQLTeam.com | Weblogs | Forums

Create view showing current status on each day


I have a table showing status history with the following fields: id, status, date.

In order to fix another problem I have, I have decided that it would be good to create a list showing for every id and every day what the relevant status would be.

Can anyone advise how I would plug in a status value for each day depending on when the new status entered up until when it changed again?

What I would like to see would be the below:

id status date
A Enquiry 01/03/2017
A Enquiry 02/03/2017
A Enquiry 03/03/2017
A Offer 04/03/2017
A Offer 05/03/2017

Thanks in advance

Please post CREATE TABLE statement or declare table variable, inserts with your data, what queries you've tried and what results you want.