RT

sjob

 

sjob2
////////////////////////
USE msdb ;
GO
EXEC dbo.sp_add_job
@job_name = N’BB’ ;
GO
EXEC sp_add_jobstep
@job_name = N’BB’,
@step_name = N’S1′,
@subsystem = N’TSQL’,
@command=N’exec [tst]..[si]’,
@database_name=N’tst’,
@retry_attempts = 5,
@retry_interval = 5 ;
GO

EXEC dbo.sp_add_schedule
@schedule_name = N’tsch’,

@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,

@active_start_date=20140617,
@active_end_date=99991231,

@active_start_time=0,
@active_end_time=235959;

USE msdb ;
GO
EXEC sp_attach_schedule
@job_name = N’BB’,
@schedule_name = N’tsch’;
GO
EXEC dbo.sp_add_jobserver
@job_name = N’BB’;
GO

///////////////////////////////////////////

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’every year’,
@enabled=1,
@freq_type=16,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=12,
@active_start_date=20150101,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N’7eee6bd6-0147-4433-915f-4ed86137ff46′

//////////////////////////////////////////////////////////////////////

@enabled=1,
@freq_type=16,
@freq_interval=1,
@freq_recurrence_factor=12,
@active_start_date=20150101,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959;

//////////////////////////////////////////////////////////////////////////

Select frequency as Monthly

and Selecting “Day” ratio button 1st day of Every 12 Months and select start date as 1st Jan 2012

////////////////////////////

IF (datepart(MM,getdate()) = 1 and datepart(DD,getdate()) = 1)
BEGIN
EXEC xxxxxxxxx

///////////////evry day

@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,

//////////////////////////////////////////////////
IF OBJECT_ID(‘tempdb..#staff’) IS NOT NULL DROP TABLE #staff
create table #staff
(id int identity, F1 varchar(10),F2 varchar(10),F3 varchar(10),F4 varchar(10),F5 varchar(10),hdr bit)

insert into #staff values(‘A’, null, ’10’, ’20’, ’30’,1)
insert into #staff values(‘B’, ‘A’,’100′, ‘200’, ‘300’,0)
insert into #staff values(‘D’, ‘B’,’110′, ‘120’, ‘130’,0)
insert into #staff values(‘E’, ‘B’, ‘210’, ‘220’, ‘230’,0)
insert into #staff values(‘C’, ‘A’,’130′, ‘320’, ‘303’,0)

select id, F1,F2,F3,F4,F5, (cast(F5 as decimal(12,2)) + cast(F3 as decimal(12,2)) + cast(F4 as decimal(12,2))) as total
,
((select cast(F5 as decimal(12,2)) from #staff where hdr=1)/100)* (cast(F5 as decimal(12,2)) )+((select cast(F3 as decimal(12,2)) from #staff where hdr=1)/100)* cast(F3 as decimal(12,2)) +((select cast(F4 as decimal(12,2)) from #staff where hdr=1)/100)* cast(F4 as decimal(12,2)) as vat from #staff where hdr=0

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: