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

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

smh9000
Starting Member

3 Posts

Posted - 2012-04-27 : 19:34:19

I have a table of values that needs to be evaluated in order to create another table and I'm having trouble figuring out how to get the desired results.
Here is a sample of my table:
EMPLOYEE POSITION EFFECT_DATE END_DATE
1 1 8/5/07 5/17/08
1 1 5/18/08 6/21/08
1 1 6/22/08 11/8/08
1 1 1/17/10 7/3/10
1 1 2/12/12 4/7/12
1 1 4/12/12 1/1/1753

Notice how the first 3 records are basically consecutive as the effect_date of next record is 1 day greater than the end_date previous record. I need to collapse these records into one, showing only distince effect and end dates for each employee, position combination.
Results need to be
EMPLOYEE POSITION EFFECT_DATE END_DATE
1 1 8/5/07 11/8/08
1 1 1/17/10 7/3/10
1 1 2/12/12 1/1/1753

Once I have a table like this, I will use the data in a later query. Thanks for any help anyone may have. I've been stuck on this for a couple of weeks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-28 : 09:17:24

I thought I was following what you are describing util the last two rows in the sample input and the resulting last row in the sample output.

In the row before last, the end_date is 4/7/12 and the effect_date in the last row is 4/12/12, which even though not consecutive were considered as consecutive dates. What is the rule/logic that you used to arrive at that?

Also, I initially assumed that the EFFECT_DATE would be earlier than (or equal to the END_DATE), but the last row has 1/1/1753 which seems to poke a hole in my assumption.

If you can clarify these, I am sure someone on the forum would be able to offer solutions that work.

smh9000
Starting Member

3 Posts

Posted - 2012-04-28 : 10:19:28

I apologize for the typo in my original post. The last row of the sample set should have an effect date of 4/8/12, not 4/12/12 which would make the last 2 rows have the consecutive dates.

Also, the 1/1/1753 date is the default null date in the database. The software package that writes these records to the database enters a date of 1/1/1753 when a user leaves a date field blank on their input screen. So the last record for all employees that are still active will be 1/1/1753 to represent that the record is still open and active. In the past I've used CASE statements to change the evaluation of this date: CASE WHEN END DATE = '1753-01-01' THEN '2099-12-31' ELSE END DATE END AS END_DATE. This way I can slot today's date (or some other specified date) between to the effect and end dates to evaluate.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-28 : 10:22:22

[code]
;with
cte as
(
select EMPLOYEE, POSITION, EFFECT_DATE, END_DATE,
rn= row_number() over (partition by EMPLOYEE order by EFFECT_DATE)
from tbl
),
rcte as
(
select EMPLOYEE, POSITION, EFFECT_DATE, END_DATE, rn, grp = 1
from cte
where rn = 1

union all

select c.EMPLOYEE, c.POSITION, c.EFFECT_DATE, c.END_DATE, c.rn,
grp = case when r.END_DATE = dateadd(day, -1, c.EFFECT_DATE)
then r.grp
else r.grp + 1
end
from rcte r
inner join cte c on r.EMPLOYEE = c.EMPLOYEE
and r.rn = c.rn - 1
)
select EMPLOYEE, POSITION, EFFECT_DATE = min(EFFECT_DATE), END_DATE = max(END_DATE)
from rcte
group by EMPLOYEE, POSITION, grp
[/code]

KH
[spoiler]Time is always against us[/spoiler]

smh9000
Starting Member

3 Posts

Posted - 2012-04-29 : 08:47:53

KH,
Thanks for the reply, I appreciate your help. I do have a question though. I've tried running the your suggested query against my sample table which only has about 80 records in it for 3 employees and it just keeps running and running. Over 15 minutes before I stop it. Is there something missing from the query to tell it to stop or something. I wouldn't have expected to run for so long and my real table will have about 30000 records. Any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-29 : 09:22:06

edited the line in red

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources