Hi

Similar to this question I posted https://social.msdn.microsoft.com/Forums/sqlserver/en-US/325e0672-8d9d-4052-bf80-0d3bf727600e/assign-values-based-on-data-range?forum=transactsql

I now have another simlar problem but based on weekly data.

I have a table which dictates a workers Monday (day week 1) to Friday (day week 5) work routine and the date this routine this was started. If the routine changes (it is normally changed on a monday) then another 5 records goes in the table. I would like the routine to be duplicated against a calendar table.

I have found that if the routine is changed mid week then the change is instant.

Sample data #contract data

create table #contract (EarnerCode int not null,startdate date not null,weekday int not null, targetvalue int not null)

insert into #contract values ('10943','2012-01-01','1','1')

insert into #contract values ('10943','2012-01-01','2','1')

insert into #contract values ('10943','2012-01-01','3','1')

insert into #contract values ('10943','2012-01-01','4','0')

insert into #contract values ('10943','2012-01-01','5','1')

insert into #contract values ('10943','2014-05-01','1','1')

insert into #contract values ('10943','2014-05-01','2','1')

insert into #contract values ('10943','2014-05-01','3','1')

insert into #contract values ('10943','2014-05-01','4','1')

insert into #contract values ('10943','2014-05-01','5','0')

insert into #contract values ('10040','2011-01-01','1','0')

insert into #contract values ('10040','2011-01-01','2','1')

insert into #contract values ('10040','2011-01-01','3','1')

insert into #contract values ('10040','2011-01-01','4','1')

insert into #contract values ('10040','2011-01-01','5','0')

insert into #contract values ('10040','2012-11-05','1','1')

insert into #contract values ('10040','2012-11-05','2','1')

insert into #contract values ('10040','2012-11-05','3','1')

insert into #contract values ('10040','2012-11-05','4','1')

insert into #contract values ('10040','2012-11-05','5','0')

insert into #contract values ('10040','2012-12-17','1','0')

insert into #contract values ('10040','2012-12-17','2','1')

insert into #contract values ('10040','2012-12-17','3','1')

insert into #contract values ('10040','2012-12-17','4','1')

insert into #contract values ('10040','2012-12-17','5','0')

insert into #contract values ('10040','2012-12-25','1','0')

insert into #contract values ('10040','2012-12-25','2','1')

insert into #contract values ('10040','2012-12-25','3','1')

insert into #contract values ('10040','2012-12-25','4','1')

insert into #contract values ('10040','2012-12-25','5','0')

insert into #contract values ('10040','2013-01-07','1','1')

insert into #contract values ('10040','2013-01-07','2','1')

insert into #contract values ('10040','2013-01-07','3','1')

insert into #contract values ('10040','2013-01-07','4','1')

insert into #contract values ('10040','2013-01-07','5','0')

insert into #contract values ('10040','2013-02-12','1','0')

insert into #contract values ('10040','2013-02-12','2','1')

insert into #contract values ('10040','2013-02-12','3','1')

insert into #contract values ('10040','2013-02-12','4','1')

insert into #contract values ('10040','2013-02-12','5','0')

insert into #contract values ('10040','2013-02-18','1','1')

insert into #contract values ('10040','2013-02-18','2','1')

insert into #contract values ('10040','2013-02-18','3','1')

insert into #contract values ('10040','2013-02-18','4','1')

insert into #contract values ('10040','2013-02-18','5','0')

insert into #contract values ('10040','2013-03-26','1','0')

insert into #contract values ('10040','2013-03-26','2','1')

insert into #contract values ('10040','2013-03-26','3','1')

insert into #contract values ('10040','2013-03-26','4','1')

insert into #contract values ('10040','2013-03-26','5','0')

insert into #contract values ('10040','2013-04-08','1','1')

insert into #contract values ('10040','2013-04-08','2','1')

insert into #contract values ('10040','2013-04-08','3','1')

insert into #contract values ('10040','2013-04-08','4','1')

insert into #contract values ('10040','2013-04-08','5','0')

insert into #contract values ('10040','2013-05-28','1','0')

insert into #contract values ('10040','2013-05-28','2','1')

insert into #contract values ('10040','2013-05-28','3','1')

insert into #contract values ('10040','2013-05-28','4','1')

insert into #contract values ('10040','2013-05-28','5','0')

insert into #contract values ('10040','2013-06-10','1','1')

insert into #contract values ('10040','2013-06-10','2','1')

insert into #contract values ('10040','2013-06-10','3','1')

insert into #contract values ('10040','2013-06-10','4','1')

insert into #contract values ('10040','2013-06-10','5','0')

My calendar table was too big to post but it could have a day of the week where Monday is 1

for fee earner 10943 the output would be

2014-04-21,10943,1

2014-04-22,10943,1

2014-04-23,10943,1

2014-04-24,10943,0

2014-04-25,10943,1

2014-04-26,10943,NULL

2014-04-27,10943,NULL

2014-04-28,10943,1

2014-04-29,10943,1

2014-04-30,10943,1

2014-05-01,10943,1 ---- date has changed so Thursday is now a 1

2014-05-02,10943,0

2014-05-03,10943,NULL

2014-05-04,10943,NULL

2014-05-05,10943,1

2014-05-06,10943,1

2014-05-07,10943,1

2014-05-08,10943,1

2014-05-09,10943,0

I hope this is do able

Regards

Can you explain the logic behind the scene?

Running the below select I got

EarnerCode startdate weekday targetvalue
10943 2012-01-01 1 1
10943 2012-01-01 2 1
10943 2012-01-01 3 1
10943 2012-01-01 4 0
10943 2012-01-01 5 1
10943 2014-05-01 1 1
10943 2014-05-01 2 1
10943 2014-05-01 3 1
10943 2014-05-01 4 1
10943 2014-05-01 5 0

Why in the desired result you show April's data?

Can you explain the logic behind the scene?

Running the below select I got

EarnerCode startdate weekday targetvalue
10943 2012-01-01 1 1
10943 2012-01-01 2 1
10943 2012-01-01 3 1
10943 2012-01-01 4 0
10943 2012-01-01 5 1
10943 2014-05-01 1 1
10943 2014-05-01 2 1
10943 2014-05-01 3 1
10943 2014-05-01 4 1
10943 2014-05-01 5 0

Why in the desired result you show April's data?

Still not sure, see the query below, 

SELECT *, DATENAME(weekday,DATEPART(weekday, DATEADD(day, @@DATEFIRST - 1, newdate))) weekdate
FROM
(
SELECT *,DATEADD(d,ROW_NUMBER() OVER (PARTITION BY startdate ORDER BY weekday)-1 ,startdate) AS newdate
 FROM #contract
WHERE EarnerCode=10943
) AS Der