June 22, 2011 at 2:44 am
Hi all experts... I am new to sql .. and need ur help in writing the SP for the below:
I have two tables T1 and T2 below.
create table T1 ( Ac_id int, date datetime, hours int,eid int);
insert into T1 values(1,'2011-05-08',8,101);
insert into T1 values(2,'2011-05-10',10,102);
insert into T1 values(3,'2011-05-09',12,102);
insert into T1 values(2,'2011-05-12',12,103);
insert into T1 values(2,'2011-05-15',8,104);
insert into T1 values(3,'2011-05-13',10,103);
create table T2(ac_id int, descr varchar(20));
insert into T2 values(1,'holiday');
insert into T2 values(2,'weekend');
insert into T2 values(3,'weekday');
I should pull a report by passing startdate and enddate as parameters in a SP and the result should have the columns
mentioned below:
eid weekdayweekendholiday
The particular employee ID worked overtime during a specific day(weekday, weekend, holiday) then it should reflect in the
table above table.
Also, if we pass startdate parameter as 10th June 2011 and if it falls on thursday then the first week should start from
thursday till saturday and next week should start from sunday(13th June 2011).
I think for this we need to set number for each Day(sunday 1, monday 2 and so on)
thank you in advance for your help.
June 22, 2011 at 10:34 am
HI,
looking at your tables and the information you requested here is some basic syntax for a simple SP you will might want to pad it out a little
Also, if we pass startdate parameter as 10th June 2011 and if it falls on thursday then the first week should start from.
create proc report1 (@startdate datetime, @enddate datetime)
as
begin
select eid,descr
from t1 a (nolock)
inner join t2 b (nolock) on a.ac_id=b.ac_id
where date between @startdate and @enddate
end
thursday till saturday and next week should start from sunday(13th June 2011).
I think for this we need to set number for each Day(sunday 1, monday 2 and so on)
How are you calling this SP? by an application or as a SQL scheduled job, if the job fails then you will need some way of logging out to another table with date failed etc, this will help diagnosis of why and to use as a mechanism to work out when to run the job again.
SQL server already assings each day of the week a numerical value and you can use the following to retrive this ie Sunday =1 Monday = 2 and so on
select datepart(dw,getdate())
***The first step is always the hardest *******
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply