Need Help on Query - 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

General SQL Server Forums

New to SQL Server Programming

Need Help on Query

Author

Topic

vk59
Starting Member

38 Posts

Posted - 2010-06-11 : 02:46:15

Can you guys please me with a query. Below is my sample table with data. EmpId is connected to ManagerId based on ValidFrom and ValidTo dates.

CREATE TABLE #Emp(RowId INT,EmpId INT,MgrId INT,ValidFrom DATETIME,ValidTo DATETIME)

INSERT INTO #Emp VALUES (1,10,5,'01/01/2006','12/31/2006')
INSERT INTO #Emp VALUES (2,10,5,'01/01/2007','10/31/2007')
INSERT INTO #Emp VALUES (3,10,6,'11/01/2007','12/31/2007')
INSERT INTO #Emp VALUES (4,10,6,'01/01/2008','06/30/2008')
INSERT INTO #Emp VALUES (5,10,5,'07/01/2008','12/31/2008')
INSERT INTO #Emp VALUES (6,10,5,'01/01/2009','12/31/2009')
INSERT INTO #Emp VALUES (7,10,5,'01/01/2010','12/31/2010')

DROP TABLE #Emp

I want to have results which displays relationship between Emp and Manager based on a Period. Expected output should be like below.

EMP-->MgrId-->PeriodStart-->PeriodEnd
10-->5-->2006-01-01-->2007-10-31 --( Aggregate of RowID 1 and 2)
10-->6-->2007-11-01-->2008-06-30 --( Aggregate of RowID 3 and 4)
10-->5-->2008-07-01-->2010-21-31 --( Aggregate of RowID 5 and 7)
Thanks in advance

Vk

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-06-11 : 02:59:35

As i understand you wanted to see result for each Empid and Manager the Minimum startPeriod and Maximum EndPeriod.

Select
EmpId ,MgrId ,Min(ValidFrom) ,Max(ValidTo)
From #Emp
GROUP BY Empid, MgrId
--------------------------------------
Let me know if any more details required.

I am not very sure
--( Aggregate of RowID 1 and 2)
--( Aggregate of RowID 3 and 4)
--( Aggregate of RowID 5 and 7)

Whats the logic for this

Lets unLearn

vk59
Starting Member

38 Posts

Posted - 2010-06-11 : 03:13:51

Hi Naveen

If I use your select query I would get 2 rows as below and also periods would be overlapped.

EmpId-->MgrId-->PeriodStart-->PeriodEnd
10-->5-->2006-01-01 00:00:00.000-->2010-12-31 00:00:00.000
10-->6-->2007-11-01 00:00:00.000-->2008-06-30 00:00:00.000

But what I need is 3 rows as stated below

EmpId-->MgrId-->PeriodStart-->PeriodStart
10-->5-->2006-01-01 00:00:00.000-->2010-10-31 00:00:00.000
10-->6-->2007-11-01 00:00:00.000-->2008-06-30 00:00:00.000
10-->5-->2008-11-01 00:00:00.000-->2010-12-31 00:00:00.000

By Aggregate of RowID 1 and 2, I mean that PeriodStart and PeriodEnd are caluculated based on Aggregation of first 2 rows. later 3rd and 4th Row and finally rows 5 to 7

Thanks
VK

quote:
Originally posted by naveengopinathasari

As i understand you wanted to see result for each Empid and Manager the Minimum startPeriod and Maximum EndPeriod.

Select
EmpId ,MgrId ,Min(ValidFrom) ,Max(ValidTo)
From #Emp
GROUP BY Empid, MgrId
--------------------------------------
Let me know if any more details required.

I am not very sure
--( Aggregate of RowID 1 and 2)
--( Aggregate of RowID 3 and 4)
--( Aggregate of RowID 5 and 7)

Whats the logic for this

Lets unLearn

Sachin.Nand

2937 Posts

Posted - 2010-06-11 : 03:52:01

[code]
select empid,mgrid,MIN(validfrom) as PeriodStart,MAX(validto)as PeriodEnd from
(
select *,rowid-row_number()over(partition by mgrid order by rowid)as rid from #Emp

)t group by rid,mgrid,empid
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

vk59
Starting Member

38 Posts

Posted - 2010-06-11 : 04:53:17

Hi,

Thank you very much. It works.

Regards,
VK

quote:
Originally posted by Idera

select empid,mgrid,MIN(validfrom) as PeriodStart,MAX(validto)as PeriodEnd from
(
select *,rowid-row_number()over(partition by mgrid order by rowid)as rid from #Emp

)t group by rid,mgrid,empid

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-06-11 : 05:55:02

quote:
Originally posted by vk59

Hi,

Thank you very much. It works.

Regards,
VK

Welcome

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources