quote:

---

Originally posted by santana

Hi,

I have one history table with those information:

Emp Dept Change_date (the date when the employee changed his department)
2899 632 200901
2899 637 200903
2899 632 200908
2899 723 200908

But I need to know for each month on 2009, in which dept this employee was working.

How can I do this with query? I only have the date when the employee changed.

I would like to have this:


Emp Dept Change_date Date
2899 632 200901 200901
2899 632 200901 200902
2899 637 200903 200903
2899 637 200903 200904
2899 637 200903 200905
2899 637 200903 200906
2899 637 200903 200907
2899 632 200908 ->I Dont need this,only the max in the month. The employee changed twice in the same month.
2899 723 200908 200908
2899 723 200908 200909
2899 723 200908 200910
2899 723 200908 200911
2899 723 200908 200912

Could you help me, please?

Thanks.

Regards,

Santana

---

Select * from
(
  select emp,dept,change_date,row_number()over(partition by emp,dept order by change_date desc)as seq from tblanme

)as t where t.seq <=2

quote:

---

Originally posted by santana

Hi,

could you help me again, please?
Because I dont know what I did wrong.

I run this query:

Select * from
(
select emp,dep,changedate,row_number()over(partition by emp,dep
order by changedate desc)as seq from [HISTORY] where fieldname = 'COSTCODE'

)as t where t.seq <=2 and emp in( '2899','3023')

and I receive this result:

emp dep changedate seq
2899 632 2009-08 1
2899 637 2009-03 2
2899 632 2009-01 1
2899 723 2009-08 1

How can I use this seq?

Sorry I don't understand!!!

Thanks a lot!

Regards

---

select * from
(
  select emp,dep,changedate,row_number()over(partition by emp,dep 
order by changedate desc)as seq from [HISTORY] where fieldname = 'COSTCODE'

)as t where  emp in( '2899','3023') 

quote:

---

Originally posted by santana

Hi,

Maybe there are no way to have what I need by query.

Are there any way that I could populate this table?
In the table I have only the record when I insert / change the department by employee - the date when the department has changed by employee.
I would like to populate this table with the months between the alterations.


eg.: I know that the employee has department 632 at 200901, 633 after 200903 and 723 after 200908.
I would like to populate the department between 200901 and 200902 with 632, between 200903 and 200907 with 633, and 200908 to now with with 723.
Is this possible? Can I do this? How?

I appreciate any help! Please!!!!!
Regards,

Renata

---

DECLARE @Year int

SELECT @year=2009--your passed value

DECLARE @Calendar table
(
Period int
)

INSERT INTO @Calendar
SELECT (100 * y.[Year]) +m.[Month] 
FROM (SELECT @year AS [Year]) y
CROSS JOIN (SELECT 1 AS [Month] UNION ALL
            SELECT 2 UNION ALL
            SELECT 3 UNION ALL
            ...
            SELECT 12)m    

SELECT r.Emp,c.Period,r.Dept
FROM @CALENDAR c
CROSS JOIN (SELECT t.Emp,t.Dept,t.Change_date as start,t1.Change_date AS End
      FROM YourTable t
      OUTER APPLY (SELECT TOP 1 Change_Date 
                   FROM YourTable
                   WHERE Emp=t.Emp
                   AND Dept <> t.Dept
                   ORDER BY Change_Date ASC)t1    
     )r
WHERE c.Period BETWEEN r.start AND COALESCE(r.End-1,r.start)

quote:

---

Originally posted by santana

I need something like this:

While date for greater or equal to 200903 (change_date) then dept receive 639
else
while date for greater or equal to 200911 (change_date) then department receive 642
...

This for the example

empref dept changedate
3077 639 200903
3077 642 200911

??????

Thanks

---