March 5, 2009 at 5:26 am
Hi All,
I have one scenario.
say for example,
create table emp
(
eno int,
dept varchar(50),
age int
)
insert into emp
select 10,'Acct',40
union
select 20,'Corp',40
union
select 30,'Acct',50
union
select 400,'Corp',25
union
select 200,'Acct',25
union
select 100,'Corp',35
union
select 600,'Acct',50
create procedure p1
(
@empid varchar(255) ,
@dept varchar(255) ,
@age varchar(100)
)
as
begin
declare @eempid varchar(255),
@edept varchar(255),
@eage varchar(25)
select @eempid = ',' + @empid + ','
select @edept = ',' + @dept + ','
select @eage = ',' + @age + ','
SELECT * from emp
where patindex('%,' + convert(varchar(5),empid) +',%' ,@eempid ) <> 0
AND patindex('%,' + convert(varchar(5),dept) +',%' ,@edept ) <> 0
AND patindex('%,' + convert(varchar(5),age) +',%' ,@eage ) <> 0
end
Earlier the has to pass all the parameter values, so it works fine.
Now they are asking to change this procedure like they may enter either one value other should accept NULL values and then need to fetch the result based on the given input.
say for example,
exec p1 null,null,40
should retrieve all the employee whose age is 40.
exec p1 null,'Corp',40
should retrieve all the employee whose age is 40 and dept is 'Corp'
so i have changed the above procedure as below
create procedure p1
(
@empid varchar(255) = NULL ,
@dept varchar(255) = NULL,
@age varchar(100) = NULL
)
as
begin
declare @eempid varchar(255),
@edept varchar(255),
@eage varchar(25)
select @eempid = ',' + @empid + ','
select @edept = ',' + @dept + ','
select @eage = ',' + @age + ','
SELECT * from emp
where patindex('%,' + convert(varchar(5),empid) +',%' ,@eempid ) <> 0
OR patindex('%,' + convert(varchar(5),dept) +',%' ,@edept ) <> 0
OR patindex('%,' + convert(varchar(5),age) +',%' ,@eage ) <> 0
end
But it is not working as expected.
if i execute
exec p1 null,'Corp',40 is fetching all the employees where age is 40 and who are not belonging to 'Corp' also.
Can anybody help me to fix this issue?
karthik
March 5, 2009 at 5:28 am
I got the below output
10Acct40
20Corp40
100Corp35
400Corp25
karthik
March 5, 2009 at 5:43 am
CREATE PROCEDURE p2
(@empid varchar(255) = NULL ,
@dept varchar(255) = NULL,
@age varchar(100) = NULL)
as
SELECT *
from CTS_emp
WHERE (@empid = eno or @empid is null)
AND
(@dept = dept or @dept is null)
AND
(@age = age or @age is null)
exec p2 null,'Corp',40
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 5, 2009 at 8:57 am
Oh...i think I forgot to mention...the user may choose more than one dept or age or eno.
karthik
March 5, 2009 at 8:59 am
Ah I see ok cool I'll look into it
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 5, 2009 at 9:17 am
--DELIMIT FUNCTION
--I've made it inline so it performs better when it's joined to
CREATE FUNCTION [dbo].[fn_StringtoTable]
(
@String NVARCHAR(MAX) ,
@Delimiter CHAR(1)
)
RETURNS TABLE
AS RETURN
(
SELECT SUBSTRING(@String+@Delimiter, n,
CHARINDEX(@Delimiter, @String+@Delimiter, n) - n) as [String]
FROM tally
WHERE n <= LEN(@String)
AND SUBSTRING(@Delimiter + @String,
n, 1) = @Delimiter
)
--PROC
CREATE PROCEDURE p2
(@empid varchar(255) = NULL ,
@dept varchar(255) = NULL,
@age varchar(100) = NULL)
as
DECLARE @tblDept TABLE (dept VARCHAR(100))
DECLARE @tblAge TABLE (age VARCHAR(100))
DECLARE @tblEmpId TABLE (empid VARCHAR(100))
INSERT INTO @tblDept
SELECT String FROM [dbo].[fn_StringtoTable] (@dept,',')
INSERT INTO @tblAge
SELECT String FROM [dbo].[fn_StringtoTable] (@age,',')
INSERT INTO @tblEmpId
SELECT String FROM [dbo].[fn_StringtoTable] (@empid,',')
SELECT i.*
FROM CTS_emp i
LEFT JOIN @tblDept d ON d.dept = i.dept
LEFT JOIN @tblAge a ON a.age = i.age
LEFT JOIN @tblEmpId e ON e.empid = i.eno
WHERE
(d.dept is not null or @dept is null)
AND
(a.age is not null or @age is null)
AND
(e.empid is not null or @empid is null)
GO
--EXAMPLES
exec p2 NULL,'corp','40'
exec p2 NULL,'Corp,Acct','40'
exec p2 NULL,'Corp,Acct',NULL
exec p2 NULL,'Corp,Acct','40,25'
exec p2 '20,200','Corp,Acct','40,25'
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 6, 2009 at 3:36 am
Chris!
It is perfect! Thanks!
I still need some help.
employee's daily working hrs will be captured in Dlywork table
create table Dlywork
(
eno int,
dt datetime,
dlyhr decimal(4,2)
)
insert into Dlywork
select 10,'02/mar/2009',8
union
select 10,'03/mar/2009',8
union
select 10,'04/mar/2009',8
union
select 10,'05/mar/2009',8
union
select 10,'06/mar/2009',8
union
select 10,'27/feb/2009',9
union
select 10,'26/feb/2009',7
union
select 10,'25/feb/2009',8
union
select 10,'24/feb/2009',8
union
select 10,'23/feb/2009',8
union
select 20,'02/mar/2009',6
union
select 20,'03/mar/2009',10.5
union
select 20,'04/mar/2009',9
union
select 20,'05/mar/2009',10.5
union
select 20,'06/mar/2009',9
employee's weekly working hrs details will be captured in wklywork table
create table Wklywork
(
eno int,
dt datetime,
wklyhr decimal(4,2)
)
insert into Wklywork
select 10,'06/mar/2009',40(sum of daily hr)
union
select 20,'06/mar/2009',45(sum of daily hr)
I need to display the report in the below format.
------------------------------------------
eno |ename |age DailyWork_Hr |WeeklyWork_Hr
------------------------------------------
As i said earlier user may pass any value.
create table #result
(
eno int,
dept varchar(50),
age int,
dlyhr decimal(4,2) null,
wklyhr decimal(6,2) null
)
insert into #result (eno,dept,age)
SELECT i.*
FROM CTS_emp i
LEFT JOIN @tblDept d ON d.dept = i.dept
LEFT JOIN @tblAge a ON a.age = i.age
LEFT JOIN @tblEmpId e ON e.empid = i.eno
WHERE
(d.dept is not null or @dept is null)
AND
(a.age is not null or @age is null)
AND
(e.empid is not null or @empid is null)
I have to update the dailyhr & wklyhr columns.
I have decided to use th UPDATE statement to update the dailyhr column. I think the simple UPDATE will help us to do this.
Update #result
set dlyhr = a.dlyhr
from DlyWork a
where #result.eno = a.eno
and a.Date = @givendate
Update #result
set wklyhr = a.Wklyhr
from WklyWork a
where #result.eno = a.eno
and a.Date = @givendate
But the problem is, if data is not available in WklyWork table , we need to calculate it from DlyWork table.
say for example, if i give '27/feb/2009' as input.
we do have the records in DlyWork table but not in WklyWork table,so we need to add the values from 23/feb/2009 to 27/feb/2009 and need to update Wklyhr column.
so i think above UPDATE will fail.
like that i have to calculate Monthlyhr and YearlyHr, we have two seperate tables.
while calculating YearlyHr values, if we dont have any record we need to refer Monthhr table, if we don't have any record in MonthlyHr table then we need to refer WklyHr table and so on.
I have planned to use CURSOR to do this, but i kept it as the last option.
karthik
March 9, 2009 at 2:45 am
Any idea? or inputs?
karthik
March 11, 2009 at 11:12 pm
Any inputs?
karthik
March 12, 2009 at 3:04 am
Sorry I have been away and I'm pretty busy today but will try and have a look later
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply