Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query Help

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-09-06 : 12:16:51

I have a table looks like this:


Location  EmployeeID       Title                        Year   Month   Activie Termed 
21000	   1001         IT Supervisor                 	2002	9	 1	  0
21000	   1001         Mgr Business Intelligence	2003	4	 1	  0

This table shows the change of title for EMployee ID = 1001

My desired output looks like below.. I would like to display the detailed record for each month until the title change got affected.


Location  EmployeeID       Title                        Year   Month   Activie Termed 
21000	   1001         IT Supervisor                 	2002	9	 1	  0
21000	   1001         IT Supervisor                 	2002	10	 1	  0
21000	   1001         IT Supervisor                 	2002	11	 1	  0
21000	   1001         IT Supervisor                 	2002	12	 1	  0
21000	   1001         IT Supervisor                 	2003	1	 1	  0
21000	   1001         IT Supervisor                 	2003	2	 1	  0
21000	   1001         IT Supervisor                 	2002	3	 1	  0
21000	   1001         Mgr Business Intelligence	2003	4	 1	  0

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-06 : 13:23:51

What range of results do you want if there are 3, 4 or "N" title changes for an employee?

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-06 : 13:32:03

[code]
SELECT Location, EmployeeID, Title, Year, Month, Activie, Termed
FROM MyTable
WHERE EmployeeID = @EmployeeID
AND DATEADD(mm, Month, DATEADD(yy, Year-1900, '')) BETWEEN @StartDate AND @FinishDate

ORDER BY Year ASC, Month ASC[/code]

So, you'll need @StartDate and @FinishDate to get the above to work...

[code]
SELECT @StartDate = MIN(DATEADD(mm, Month, DATEADD(yy, Year-1900, ''))) -- Earliest date for this employee
FROM MyTable
WHERE EmployeeID = @EmployeeID
GROUP BY EmployeeID

SELECT @FinishDate = MIN(DATEADD(mm, Month, DATEADD(yy, Year-1900, ''))) -- Earliest date that has a different title
FROM MyTable
WHERE EmployeeID = @EmployeeID
AND Title <> (SELECT Title FROM MyTable WHERE EmployeeID = @EmployeeID AND DATEADD(mm, Month, DATEADD(yy, Year-1900, '')) = @StartDate)
GROUP BY EmployeeID
[/code]
Edit: corrected the conversion of integer Year to DATETIME format.

VIG
Yak Posting Veteran

86 Posts

Posted - 2005-09-06 : 15:02:16

[code]declare @t table (Location int,EmployeeID int,Title varchar(50),[Year] int,[Month] int,Activie int,Termed int)
insert @t
select 21000,1001,'IT Supervisor',2002,9,1,0 union
select 21000,1001,'Mgr Business Intelligence',2003,4,1,0

select t.Location,t.EmployeeID,t.Title,
t.[year]+ ((t.month+n.n)/12) [Year]
,((t.month-1+n.n)%12) +1 [Month]
,t.Activie
,t.Termed
from @t t
left join @t t1
on
t1.location=t.location and t.EmployeeID=t.EmployeeID and
(t.[year]<t1.[year]) or (t.[year]=t1.[year] and t.[month]<t1.[month])
join tally n on n.n<=isnull(12*(t1.[year]-t.[year]) + t1.[month]-t.[month]-1,0)
[/code]
tally - table with numbers from 0 to .....

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-09-06 : 16:06:23

drop table tally
create table tally ( n int)
go
set nocount on
declare @n int
set @n = 1
while @n < 12
begin
insert into tally values(@n)
set @n = @n + 1
end

--Create test table

declare @t table (Location int,EmployeeID int,Title varchar(50),[Year] int,[Month] int,Active int,Termed int)
insert @t
select 21000,1001,'IT Supervisor',2002,9,1,0 union all
select 21000,1001,'Mgr Business Intelligence',2003,4,1,0
union all select 21000,1001,'CEO',2005,3,1,0

select t.Location,t.EmployeeID,t.Title,
t.[year]+ ((t.month+n.n)/12) [Year]
,((t.month-1+n.n)%12) +1 [Month]
,t.Active
,t.Termed
from @t t
left join @t t1
on
t1.location=t.location and t.EmployeeID=t.EmployeeID and
(t.[year]<t1.[year]) or (t.[year]=t1.[year] and t.[month]<t1.[month])
join tally n on n.n <= isnull(12*(t1.[year]-t.[year]) + t1.[month]-t.[month]-1,0)

this script gives me:


21000	1001	IT Supervisor	2002	10	1	0
21000	1001	IT Supervisor	2002	11	1	0
21000	1001	IT Supervisor	2003	12	1	0
21000	1001	IT Supervisor	2003	1	1	0
21000	1001	IT Supervisor	2003	2	1	0
21000	1001	IT Supervisor	2003	3	1	0
21000	1001	IT Supervisor	2002	10	1	0
21000	1001	IT Supervisor	2002	11	1	0
21000	1001	IT Supervisor	2003	12	1	0
21000	1001	IT Supervisor	2003	1	1	0
21000	1001	IT Supervisor	2003	2	1	0
21000	1001	IT Supervisor	2003	3	1	0
21000	1001	IT Supervisor	2003	4	1	0
21000	1001	IT Supervisor	2003	5	1	0
21000	1001	IT Supervisor	2003	6	1	0
21000	1001	IT Supervisor	2003	7	1	0
21000	1001	IT Supervisor	2003	8	1	0
21000	1001	Mgr Business Intelligence	2003	5	1	0
21000	1001	Mgr Business Intelligence	2003	6	1	0
21000	1001	Mgr Business Intelligence	2003	7	1	0
21000	1001	Mgr Business Intelligence	2003	8	1	0
21000	1001	Mgr Business Intelligence	2003	9	1	0
21000	1001	Mgr Business Intelligence	2003	10	1	0
21000	1001	Mgr Business Intelligence	2003	11	1	0
21000	1001	Mgr Business Intelligence	2004	12	1	0
21000	1001	Mgr Business Intelligence	2004	1	1	0
21000	1001	Mgr Business Intelligence	2004	2	1	0
21000	1001	Mgr Business Intelligence	2004	3	1	0

But, this is what I really want to get as the output..


21000	1001	IT Supervisor	2002	9	1	0 
21000	1001	IT Supervisor	2002	10	1	0
21000	1001	IT Supervisor	2002	11	1	0
21000	1001	IT Supervisor	2003	12	1	0
21000	1001	IT Supervisor	2003	1	1	0
21000	1001	IT Supervisor	2003	2	1	0
21000	1001	IT Supervisor	2003	3	1	0
21000	1001	Mgr Business Intelligence	2003	4	1	0
21000	1001	Mgr Business Intelligence	2003	5	1	0
21000	1001	Mgr Business Intelligence	2003	6	1	0
21000	1001	Mgr Business Intelligence	2003	7	1	0
21000	1001	Mgr Business Intelligence	2003	8	1	0
21000	1001	Mgr Business Intelligence	2003	9	1	0
21000	1001	Mgr Business Intelligence	2003	10	1	0
21000	1001	Mgr Business Intelligence	2003	11	1	0
21000	1001	Mgr Business Intelligence	2003	12	1	0
21000	1001	Mgr Business Intelligence	2004	1	1	0
21000	1001	Mgr Business Intelligence	2004	2	1	0
21000	1001	Mgr Business Intelligence	2004	3	1	0
21000	1001	Mgr Business Intelligence	2004	4	1	0
21000	1001	Mgr Business Intelligence	2004	5	1	0
21000	1001	Mgr Business Intelligence	2004	6	1	0
21000	1001	Mgr Business Intelligence	2004	7	1	0
21000	1001	Mgr Business Intelligence	2004	8	1	0
21000	1001	Mgr Business Intelligence	2004	9	1	0
21000	1001	Mgr Business Intelligence	2004	10	1	0
21000	1001	Mgr Business Intelligence	2004	11	1	0
21000	1001	Mgr Business Intelligence	2004	12	1	0
21000	1001	Mgr Business Intelligence	2005	1	1	0
21000	1001	Mgr Business Intelligence	2005	2	1	0
21000	1001	CEO                             	2005	3	1	0

VIG
Yak Posting Veteran

86 Posts

Posted - 2005-09-06 : 17:17:37

Sorry

declare @tally table ( n int)

set nocount on
declare @n int
set @n = 0 /* Important!!! */
while @n < 8000 /*The range of numbers should be big enough */
begin
insert into @tally values(@n)
set @n = @n + 1
end

--Create test table

declare @t table (Location int,EmployeeID int,Title varchar(50),[Year] int,[Month] int,Active int,Termed int)
insert @t
select 21000,1001,'IT Supervisor',2002,9,1,0 union all
select 21000,1001,'Mgr Business Intelligence',2003,4,1,0
union all select 21000,1001,'CEO',2005,3,1,0



select t.Location,t.EmployeeID,t.Title,
       year(dateadd(m,tl.n,t.dt)) [year],
       month(dateadd(m,tl.n,t.dt)) [month],
       t.Active,t.Termed
from
(
 select t.Location,t.EmployeeID,t.Title,
       dateadd(m,t.[month]-1,dateadd(yy,t.[year]-1900,0)) dt
      ,datediff(m,dateadd(m,t.[month]-1,dateadd(yy,t.[year]-1900,0))
       ,isnull((select top 1 dateadd(m,t1.[month]-2,dateadd(yy,t1.[year]-1900,0))
     from @t t1 
     where t1.location=t.location and t1.EmployeeID=t.EmployeeID 
           and dateadd(m,t.[month],dateadd(yy,t.[year]-1900,0)) < dateadd(m,t1.[month],dateadd(yy,t1.[year]-1900,0))),dateadd(m,t.[month]-1,dateadd(yy,t.[year]-1900,0)))) n
     ,t.Active
,t.Termed
from @t t
) t
join @tally tl on t.n>= tl.n

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-09-08 : 12:06:26

thank!
,but I am not quite understand this part:
,datediff(m,dateadd(m,t.[month]-1,dateadd(yy,t.[year]-1900,0))
,isnull((select top 1 dateadd(m,t1.[month]-2,dateadd(yy,t1.[year]-1900,0))
from @t t1
where t1.location=t.location and t1.EmployeeID=t.EmployeeID
and dateadd(m,t.[month],dateadd(yy,t.[year]-1900,0)) < dateadd(m,t1.[month],dateadd(yy,t1.[year]-1900,0))),dateadd(m,t.[month]-1,dateadd(yy,t.[year]-1900,0)))) n

Can you explain to me what you are trying to do here?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources