-- need a list of employee ids with a basedate set to when they start with shift_code=1, unit=1
-- this is a minimal tale to show the format
-- extra columns could be added with other info (e.g. name)
create table empbase (
empid int,
basedate datetime
)

-- fill with test data
insert empbase (empid,basedate) values (12345,'2007/1/1')
insert empbase (empid,basedate) values (88877,'2007/1/5')
insert empbase (empid,basedate) values (98765,'2007/1/20')
insert empbase (empid,basedate) values (99994,'2007/6/5')
go
-------------------------------
create function shifts (
@mth tinyint,
@yr smallint
)
returns
@table_var
table (
empid int,
date datetime,
shift_code int,
unit int)
as
-- generate daily shift pattern 1,1,2,2,3,3,4,5,... changing units 1,2,3,4,... every 30 days.
begin
declare @d1 datetime
declare @d31 datetime
set @d1=convert(datetime,convert(char(8),@yr*10000+@mth*100+1))
set @d31=dateadd(dd,-1,dateadd(mm,1,@d1))

;with n01 (i) as (select 0 as 'i' union all select 1)
,seq (n) as (
select 
d1.i+(2*d2.i)+(4*d3.i)+(8*d4.i)+(16*d5.i) as 'n'
from 
n01 as d1
cross join
n01 as d2
cross join
n01 as d3
cross join
n01 as d4
cross join
n01 as d5)
,dates (dt) as (
select
dateadd(dd,n,@d1) as 'dt'
from
seq
where
dateadd(dd,n,@d1) <= @d31)
,modval (mod,val) as (
select 0,1 union all
select 1,1 union all
select 2,2 union all
select 3,2 union all
select 4,3 union all
select 5,3 union all
select 6,4 union all
select 7,5)
insert @table_var
select
b.empid,
d.dt,
(select val from modval where mod=(datediff(dd,b.basedate,d.dt) % 8)),
((convert(int,(datediff(dd,b.basedate,d.dt) / 30)) % 4) + 1)
from
empbase b, dates d
where
b.basedate <= d.dt
return
end
go





-- test for various months
select * from shifts(1,2007) order by empid,date
select * from shifts(2,2007) order by empid,date
select * from shifts(3,2007) order by empid,date
select * from shifts(4,2007) order by empid,date
select * from shifts(5,2007) order by empid,date
select * from shifts(12,2007) order by empid,date

((convert(int,(datediff(dd,b.basedate,d.dt) / 30)) % 4) + 1)

insert empbase (empid,basedate,unit_date) values (12345,'2007/1/1','2007/3/1')

quote:

---

Originally posted by khtan

declare	@i int

select	@i = 0

while @i <= 10
begin
	print (@i % 4) + 1
	select	@i = @i + 1
end

---

KH
tnx but i must to check what was the last unit value last month from the filed "unit_date"
doesn't matter the day in the month
even if it 12/12/2007
or 29/12/2007
check the last month
start a series 1,2,3,4
start from "unit_date"

[spoiler]Time is always against us[/spoiler]

---

quote:

---

tnx but i must to check what was the last unit value last month from the filed "unit_date"
doesn't matter the day in the month
even if it 12/12/2007
or 29/12/2007
check the last month
start a series 1,2,3,4
start from "unit_date"

---

create table sample
(
    colname  int,
    . . . 
)

insert into sample values ( . . . )
insert into sample values ( . . . )
insert into sample values ( . . . )

col1   col2   col3   col4
xxx    xxx    xxx    xxx
xxx    xxx    xxx    xxx
xxx    xxx    xxx    xxx
xxx    xxx    xxx    xxx

DECLARE @tb_employee TABLE
(
	empid		int,
	basedate 	datetime,
	unit_date	datetime
)
INSERT INTO @tb_employee
SELECT	25555472, '20070101', '20070215' UNION ALL
SELECT	55555581, '20070101', '20070515' UNION ALL
SELECT	56555555, '20070101', '20070612' UNION ALL
SELECT	51555555, '20070101', '20070730' UNION ALL
SELECT	54655555, '20070101', '20070731'

SELECT	e.empid, 
	d.[DATE],
	shift	= (row_number() OVER (PARTITION BY e.empid ORDER BY d.[DATE]) + 1) / 2,
	unit	= DATEDIFF(MONTH, e.unit_date, d.[DATE]) % 4
FROM	F_TABLE_DATE('20070501', '20070531') d
	INNER JOIN @tb_employee e
	ON	d.[DATE]	>= e.unit_date
ORDER BY e.empid, d.[DATE]

declare @year	int,
	@month	int

select	@year 	= 2007,
	@month	= 2

select	[1st of month] 	= dateadd(year, @year - 1900, dateadd(month, @month - 1, 0)),
	[last of month]	= dateadd(year, @year - 1900, dateadd(month, @month, -1))