February 10, 2010 at 6:56 am
create table emp
(
eid int,
ename varchar(15),
salary int,
addres Varchar(15),
datett datetime,
act varchar(1)
)
Above is a table schema of a employee table..
following records are inserted in to the table
insert into emp values(1,'AAAAA',10000,'abc','01/01/2005','n')
insert into emp values(1,'AAAAA',10000,'xys','01/11/2005','n')
insert into emp values(1,'AAAAA',20000,'xyz','01/01/2006','y')
insert into emp values (1,'BBBBB',10000,'abc','01/01/2005','n')
insert into emp values(1,'BBBBB',10000,'zzz','01/11/2005','n')
insert into emp values(1,'BBBBB',21000,'zzz','01/01/2007','n')
insert into emp values(1,'BBBBB',21000,'zzz','01/01/2008','n')
The column descriptions are
eid --> employee id
ename --> employee name
salary --> Salary of the employee
addres --> address of the employee
datett --> modified datetime
act --> active flag.. (Y-> when active and N--> not active\retired)
The functionality is
A record is inserted in to the table emp whenever the details of the employee is added\modified. initialy the act flag will be y for the employee.. when his record is modified, the act flag will be changed to n.
When the employee is retired the act flag is updated to n for the latest record..
now the requirement is to get the latest records of all the employees as on 06\06\2007 and the employee should be active on the current date(i.e today)
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
February 10, 2010 at 7:18 am
Not sure if this will help. May be insufficient test data. I am assuming that the latest record (as per date) in emp table indicates the 'current' status of the employee. Correct me if I am wrong.
;WITH CTE AS
(SELECT *, ROW_NUMBER() OVER (Partition by ename ORDER BY datett
desc)as Row_num
FROM #emp )
Select * from #emp T1
Where datett <= '06/06/2007'
AND EXISTS (SELECT 1
FROM CTE T2
WHERE T1.eid= T2.eid
AND T1.ename = T2.ename
AND T2.Row_num = 1
AND T2.Act = 'Y')
---------------------------------------------------------------------------------
February 10, 2010 at 7:24 am
February 10, 2010 at 7:30 am
Sorry Nabha..
Let me explain exactly i want.
If we look the table, we have 3 records for the empid 1,
1st record on the 01-01-2005 was inserted when that employee actually joined.
the 2nd record on 01/11/2005 was inserted when his address was changed from abc to xys
the 3rd record on 01/01/2006 indicates, his salary and address was changed and the status on the last modified date indicates that the employee is still active and not yet retired..
hope this help..
thanks for ur reply.
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
February 10, 2010 at 7:31 am
Maybe I'm missing something but could it be as simple as this?
Regards,
Willem
That would give the records even if the employee is not active now.
and the employee should be active on the current date(i.e today)
---------------------------------------------------------------------------------
February 10, 2010 at 7:35 am
Paarthasarathy (2/10/2010)
Sorry Nabha..Let me explain exactly i want.
If we look the table, we have 3 records for the empid 1,
1st record on the 01-01-2005 was inserted when that employee actually joined.
the 2nd record on 01/11/2005 was inserted when his address was changed from abc to xys
the 3rd record on 01/01/2006 indicates, his salary and address was changed and the status on the last modified date indicates that the employee is still active and not yet retired..
hope this help..
thanks for ur reply.
The query which I've given wont work?
---------------------------------------------------------------------------------
February 10, 2010 at 7:36 am
Help us help you, based on the sample data in the original post what is the expected results?
February 10, 2010 at 7:36 am
@Paarthasarathy: can it be there is an error in your sample data? The employees with ename AAAAA and BBBBB both have eid 1.
To test my solution, I changed the eid for BBBBB to 2.
Can you try this:
SELECT *
FROM emp m
WHERE EXISTS ( SELECT *
FROM emp a
WHERE m.eid = a.eid
AND a.act = 'y' )
AND NOT EXISTS ( SELECT *
FROM emp d
WHERE m.eid = d.eid
AND d.datett > m.datett
AND d.datett <= '20070606' )
AND datett <= '20070606'
February 10, 2010 at 7:41 am
Hi Wschampler
but the query does not work for this sample data..
insert into emp values(1,'CCCCC',10000,'abc','01/01/2005','n')
insert into emp values(1,'CCCCC',10000,'xys','01/11/2005','n')
insert into emp values(1,'CCCCC',20000,'xyz','01/01/2006','n')
insert into emp values(1,'CCCCC',20000,'xyy','01/01/2008','y')
i need this employee to get returned in the result.. as he is still active
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
February 10, 2010 at 7:42 am
Again, based on the sample data, please show us what the expected results of the query would be. This will help us help you.
February 10, 2010 at 7:44 am
Wschampheleer,
Sorry for misspelling your name in the first reply...
i am getting the expected result...
Thanks a million!
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
February 10, 2010 at 7:45 am
Paarthasarathy (2/10/2010)
Hi Wschamplerbut the query does not work for this sample data..
insert into emp values(1,'CCCCC',10000,'abc','01/01/2005','n')
insert into emp values(1,'CCCCC',10000,'xys','01/11/2005','n')
insert into emp values(1,'CCCCC',20000,'xyz','01/01/2006','n')
insert into emp values(1,'CCCCC',20000,'xyy','01/01/2008','y')
i need this employee to get returned in the result.. as he is still active
I guess you still refer to my first post - I misinterpreted your requirement the first time. Please try the second query I posted. Looks like it is working just fine on this new data set:
eidenamesalaryaddresdatettact
1AAAAA20000xyz2006-01-01 00:00:00.000y
3CCCCC20000xyz2006-01-01 00:00:00.000n
February 10, 2010 at 7:46 am
Lynn
Sorry for the delay:
This is the required output
eid ename salary addres datett act
----------- --------------- ----------- --------------- ----------------------- ----
1 BBBBB 21000 zzz 2007-01-01 00:00:00.000 n
3 CCCCC 20000 xyz 2006-01-01 00:00:00.000 n
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
February 10, 2010 at 7:46 am
February 10, 2010 at 8:46 am
Yet another way 🙂
select A.* from emp A
where A.datett=(select max(C.datett)
from emp C
where A.eid = C.eid
and C.datett <= '06/06/2006'
)
and exists
(
select '' from emp B
where A.eid = B.eid
and B.act='Y'
)
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply