sql server - sql trigger daterange with weekdays -

i have 2 tabels on mssql(2012) db ("tbla" , "tblb"). after insert on "tbla" want trigger autogenerate on "tblb" records dates between startdate , enddate "tbla" on weekdays selected on "tbla"

for example on "tbla":

tbla_id: 99 cat: text1 startdate: 01/01/2015 enddate: 31/01/2015 monday: true tuesday: false wednesday: false thursday: false friday: true saturday: false sunday: false 

"tblb" should get

 1   99    text1   02/01/2015 (=friday)  2   99    text1   04/01/2015 (=monday)  3   99    text1   09/01/2015 (=friday)  4   99    text1   11/01/2015 (=monday)  5   99    text1   16/01/2015 (=friday)  6   99    text1   28/01/2015 (=monday)  7   99    text1   23/01/2015 (=friday)  8   99    text1   25/01/2015 (=monday)  9   99    text1   30/01/2015 (=friday)         create table [dbo].[tbla] (     [tbla_id] int not null identity,     [tbla_cat] nvarchar(50) null,      [tbla_startdate] date null,      [tbla_enddate] date null,      [tbla_monday] bit null,      [tbla_tuesday] bit null,      [tbla_wednesday] bit null,      [tbla_thursday] bit null,      [tbla_friday] bit null,      [tbla_saturday] bit null,      [tbla_sunday] bit null,      constraint [pk_tbla] primary key ([tbla_id]),  )  create table [dbo].[tblb] (     [tblb_id] int not null identity,     [tbla_id] int not null,     [tblb_cat] nvarchar(50) null,      [tblb_date] date null,      constraint [pk_tblb] primary key ([tblb_id]),  ) 

this trigger should work you. uses recursive common table expression generate tblb_date values in range , case expression test if name of day in flagged true. note part depends on system language if you're not using english have adjust date name literals according specific language.

the recursive common table expression serves substitute calendar table, if have appropriate table can act source dates can use instead (it should give better performance).

please know code lacks error checking , hasn't been optimized , can improved. should give idea how can solve problem.

create trigger ins_tblb on [dbo].[tbla] after insert begin      dates (        select            tbla_id, tbla_cat, tbla_startdate, tbla_enddate,            tbla_monday, tbla_tuesday, tbla_wednesday, tbla_thursday,            tbla_friday, tbla_saturday, tbla_sunday,           tbla_startdate tblb_date        inserted        union         select tbla_id, tbla_cat, tbla_startdate, tbla_enddate,            tbla_monday, tbla_tuesday, tbla_wednesday, tbla_thursday,            tbla_friday, tbla_saturday, tbla_sunday,           dateadd(day, 1, tblb_date) tblb_date        dates         dates.tblb_date <= tbla_enddate     )       insert tblb (tbla_id, tblb_cat, tblb_date)     select tbla_id, tbla_cat, tblb_date     dates     datename(dw,tblb_date) in (        case when tbla_monday = 1            n'monday' end,        case when tbla_tuesday = 1            n'tuesday' end,        case when tbla_wednesday = 1            n'wednesday' end,        case when tbla_thursday = 1            n'thursday' end,        case when tbla_friday = 1            n'friday' end,        case when tbla_saturday = 1            n'saturday' end,        case when tbla_sunday = 1            n'sunday' end     )      order tbla_id, tblb_date     option (maxrecursion 0) end