August 22, 2008 at 3:01 am
Hi every body!
I am new SQL. I can write some amount of queries. I have this problem and I am looking for a solution.
I have one table which has following
EMPID - WEFDATE - DEPTID
28 - 01/08/2008 - 1
28 - 15/08/2008 - 3
28 - 01/09/2008 - 5
This table is storing information of an employee department transfer with effective date.
I have another table which has following
EMPID - DATE - PROJECTID - MANHRS
28 - 01/08/2008 - 1768 - 8.75
28 - 02/08/2008 - 1758 - 8.75
.
.
.
28 - 15/08/2008 - 1468 - 8.75
.
.
.
28 - 20/08/2008 - 1768 - 8.75
This table is storing information of employee(s) man hour(s) on daily basis . I would like to have a result(ant) or derived table some thing like this
EMPID - DEPTID - DATE - PROJECTID - MANHRS
28 - 01 - 01/08/2008 - 1768 - 8.75
28 - 01 - 02/08/2008 - 1758 - 8.75
.
.
.
.
28 - 03 - 15/08/2008 - 1468 - 8.75
.
.
.
28 - 03 - 20/08/2008 - 1768 - 8.75
The easy way is to create a cursor or temp table which will have the department change for that employee for the full period and then create a join.
My question is "Do we have any other way?". Experts please help me. You can also contact me sksankararaman@gmail.com
August 22, 2008 at 4:11 am
sksankararaman (8/22/2008)
Hi every body!I am new SQL. I can write some amount of queries. I have this problem and I am looking for a solution.
I have one table which has following
EMPID - WEFDATE - DEPTID
28 - 01/08/2008 - 1
28 - 15/08/2008 - 3
28 - 01/09/2008 - 5
This table is storing information of an employee department transfer with effective date.
I have another table which has following
EMPID - DATE - PROJECTID - MANHRS
28 - 01/08/2008 - 1768 - 8.75
28 - 02/08/2008 - 1758 - 8.75
.
.
.
28 - 15/08/2008 - 1468 - 8.75
.
.
.
28 - 20/08/2008 - 1768 - 8.75
This table is storing information of employee(s) man hour(s) on daily basis . I would like to have a result(ant) or derived table some thing like this
EMPID - DEPTID - DATE - PROJECTID - MANHRS
28 - 01 - 01/08/2008 - 1768 - 8.75
28 - 01 - 02/08/2008 - 1758 - 8.75
.
.
.
.
28 - 03 - 15/08/2008 - 1468 - 8.75
.
.
.
28 - 03 - 20/08/2008 - 1768 - 8.75
The easy way is to create a cursor or temp table which will have the department change for that employee for the full period and then create a join.
My question is "Do we have any other way?". Experts please help me. You can also contact me sksankararaman@gmail.com
As per my understanding of the question that you have asked, you can simply join the two tables for the result, the query will be
select a.empid, a.deptid, b.date, b.projectid, b.manhrs from table1 a join table 2 b on a.empid = b.empid
If you have some other requirement, please clarify.
August 22, 2008 at 4:22 am
My understanding is slightly different. Here's what I came up with...
set dateformat dmy
--Sample Data
declare @t1 table (EMPID int, WEFDATE datetime, DEPTID int)
insert @t1
select 28, '01/08/2008', 1
union all select 28, '15/08/2008', 3
union all select 28, '01/09/2008', 5
union all select 1, '01/08/2008', 4
declare @t2 table (EMPID int, DATE datetime, PROJECTID int, MANHRS decimal(4, 2))
insert @t2
select 28, '01/08/2008', 1768, 8.75
union all select 28, '02/08/2008', 1758, 8.75
union all select 28, '15/08/2008', 1468, 8.75
union all select 28, '20/08/2008', 1768, 8.75
union all select 1, '20/08/2008', 1768, 8.75
--Query
select
EMPID,
(select top 1 DEPTID from @t1
where EMPID = a.EMPID and WEFDATE <= a.DATE
order by WEFDATE desc) as DEPTID,
DATE, PROJECTID, MANHRS
from @t2 a
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
August 22, 2008 at 4:37 am
sksankararaman (8/22/2008)
Hi every body!I am new SQL. I can write some amount of queries. I have this problem and I am looking for a solution.
I have one table which has following
EMPID - WEFDATE - DEPTID
28 - 01/08/2008 - 1
28 - 15/08/2008 - 3
28 - 01/09/2008 - 5
This table is storing information of an employee department transfer with effective date.
I have another table which has following
EMPID - DATE - PROJECTID - MANHRS
28 - 01/08/2008 - 1768 - 8.75
28 - 02/08/2008 - 1758 - 8.75
.
.
.
28 - 15/08/2008 - 1468 - 8.75
.
.
.
28 - 20/08/2008 - 1768 - 8.75
This table is storing information of employee(s) man hour(s) on daily basis . I would like to have a result(ant) or derived table some thing like this
EMPID - DEPTID - DATE - PROJECTID - MANHRS
28 - 01 - 01/08/2008 - 1768 - 8.75
28 - 01 - 02/08/2008 - 1758 - 8.75
.
.
.
.
28 - 03 - 15/08/2008 - 1468 - 8.75
.
.
.
28 - 03 - 20/08/2008 - 1768 - 8.75
The easy way is to create a cursor or temp table which will have the department change for that employee for the full period and then create a join.
My question is "Do we have any other way?". Experts please help me. You can also contact me sksankararaman@gmail.com
CAN YOU PLEASE CLARIFY YOUR REQUIREMENT.
August 25, 2008 at 1:18 am
Thanks every one who are helping me. Again I am attempting to explain my problem.
I need a methodology (and then coding later)
I have one table which stores changes in department of each employee; given below is for sample employee
EMPID - WEFDATE - DEPTID
28 - 01/08/2008 - 1
28 - 15/08/2008 - 3
28 - 01/09/2008 - 5
I have another table which has man hours (say) for a specific period, but does not have the department id; given below is one sample
EMPID - DATE - PROJECTID - MANHRS
28 - 01/08/2008 - 1768 - 8.75
28 - 02/08/2008 - 1758 - 8.75
28 - 03/08/2008 - 1467 - 4.00
28 - 03/08/2008 - 1536 - 4.75
.
.
.
28 - 15/08/2008 - 1468 - 8.75
.
.
.
28 - 20/08/2008 - 1768 - 8.75
(You may please note that this table has multiple entries for one date for employees).
Now I would like to have a derived table (or query or resultset or etc...) which has the combination of both these tables; the derived table should have department id for all the dates, for any particular employee or all employees. See this is not happening when I join empid + date from table A & empid + date of table B.
So I have this option. create a cursor or temp table which will have the empid,date,deptid for the full period and then create a join this temp table with table B.
What I am asking is, any there other METHOD? (What I am looking at is querying tables which as with effective date and other table which has data with date.)
You can also contact me at sksankararaman@gmail.com
Thanks again.
K. Sankara Raman
Please wear helmet when riding two wheelers.
August 25, 2008 at 5:05 am
Please could you post create statements for the tables, sample data (as insert statements) and your expected output.
Having all that makes it much easier and faster for us to get a tested solution that meets your needs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2008 at 6:34 am
I guess the trick is to generate a view which shows the start date and end date of each employee's stay in a department.
Data:
create table empdep(
empid int,
wefdate datetime,
deptid int)
insert into empdep values(28,'20080801',1)
insert into empdep values(28,'20080815',3)
insert into empdep values(28,'20080901',5)
insert into empdep values(28,'20080915',2)
insert into empdep values(28,'20081015',4)
View:
create view empdep_intervals as
select empid,wefdate1,min(wefdate2) wefdate2,deptid
from
(
select distinct e1.empid,e1.wefdate wefdate1,e2.wefdate wefdate2,e1.deptid
from empdep e1,empdep e2
where e1.empid=e2.empid and e1.wefdate<e2.wefdate
) e
group by empid,wefdate1,deptid
union all
select d1.empid,d1.wefdate wefdate1,convert(datetime,'21000101') wefdate2, d1.deptid
from empdep d1
join
(
select empid,max(wefdate) wefdate
from empdep
group by empid
) d2 on d1.empid=d2.empid and d1.wefdate=d2.wefdate
go
The rest is downhill.
August 26, 2008 at 7:13 am
I just tested Ryan Randall's code on SQL 2005, and it appears to work just fine. Is there some issue with it?
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
August 26, 2008 at 7:20 am
On large tables, there might be a performance issue due to the "triangular join" in the correlated subquery.
N 56°04'39.16"
E 12°55'05.25"
August 26, 2008 at 7:29 am
Peso (8/26/2008)
On large tables, there might be a performance issue due to the "triangular join" in the correlated subquery.
It's not one giant triangle but rather many little ones (for each employee).
August 26, 2008 at 7:45 am
Yes.
select top 1 DEPTID from @t1
where EMPID = a.EMPID and WEFDATE <= a.DATE
order by WEFDATE desc
But there is still the "WEFDATE <= a.DATE" predicate, even if only for "EMPID = a.EMPID".
I hope there is at least an index over EmpID.
N 56°04'39.16"
E 12°55'05.25"
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply