Avoid Cursor: detect change in a timeline hierarchy

Hi everybody,
I was wondering if I have another choice then using cursor here... see
this simple exemple:
Employee,Date,Department
1234,2001-01-01,10
1234,2001-01-02,11
1234,2001-01-03,10
1234,2001-01-04,10
1234,2001-01-05,10
1234,2001-01-06,10
1234,2001-01-07,10
1234,2001-01-08,10
1234,2001-01-09,10
I want to get a recordset like this one
Employee,From,To,Department
1234,2001-01-01,2001-01-01,10
1234,2001-01-02.2001-02-02,11
1234,2001-01-03,2001-01-09,10
Currently I use a cursor that "scan" for everyday if there is a change
and then append a row in a table but this is so slow... Is there a
better "way" to proceed?
Thanks!If the number of columns returned should vary depending on the content of
the data, then what you want is called a crosstab. There are ways to
implement this using SQL:
http://www.aspfaq.com/show.asp?id=2462
However, perhaps the best method (most flexible, easiest to maintain, etc)
would be to use an Excel pivot table, Crystal Reports crosstab, etc.
http://www.cpearson.com/excel/pivots.htm
"Mathieu Dumais-Savard" <matdumsa@.gmail.com> wrote in message
news:1140462555.378614.215210@.g43g2000cwa.googlegroups.com...
> Hi everybody,
> I was wondering if I have another choice then using cursor here... see
> this simple exemple:
> Employee,Date,Department
> 1234,2001-01-01,10
> 1234,2001-01-02,11
> 1234,2001-01-03,10
> 1234,2001-01-04,10
> 1234,2001-01-05,10
> 1234,2001-01-06,10
> 1234,2001-01-07,10
> 1234,2001-01-08,10
> 1234,2001-01-09,10
>
> I want to get a recordset like this one
> Employee,From,To,Department
> 1234,2001-01-01,2001-01-01,10
> 1234,2001-01-02.2001-02-02,11
> 1234,2001-01-03,2001-01-09,10
> Currently I use a cursor that "scan" for everyday if there is a change
> and then append a row in a table but this is so slow... Is there a
> better "way" to proceed?
> Thanks!
>|||Hi JT, thanks for your answer...
However, the number of columns is not subject to any change and the
statement must be usable in a store proc to "compress" the table... I
did a simple schema to explain it but the table contains millions of
rows of department/day/employee... All I wanna do is to "shrink down"
this table into a more friendly from..to manner...
JT wrote:
> If the number of columns returned should vary depending on the content of
> the data, then what you want is called a crosstab. There are ways to
> implement this using SQL:
> http://www.aspfaq.com/show.asp?id=2462
> However, perhaps the best method (most flexible, easiest to maintain, etc)
> would be to use an Excel pivot table, Crystal Reports crosstab, etc.
> http://www.cpearson.com/excel/pivots.htm
>
> "Mathieu Dumais-Savard" <matdumsa@.gmail.com> wrote in message
> news:1140462555.378614.215210@.g43g2000cwa.googlegroups.com...|||"Mathieu Dumais-Savard" <matdumsa@.gmail.com> wrote in message
news:1140462555.378614.215210@.g43g2000cwa.googlegroups.com...
> Hi everybody,
> I was wondering if I have another choice then using cursor here... see
> this simple exemple:
> Employee,Date,Department
> 1234,2001-01-01,10
> 1234,2001-01-02,11
> 1234,2001-01-03,10
> 1234,2001-01-04,10
> 1234,2001-01-05,10
> 1234,2001-01-06,10
> 1234,2001-01-07,10
> 1234,2001-01-08,10
> 1234,2001-01-09,10
>
> I want to get a recordset like this one
> Employee,From,To,Department
> 1234,2001-01-01,2001-01-01,10
> 1234,2001-01-02.2001-02-02,11
> 1234,2001-01-03,2001-01-09,10
> Currently I use a cursor that "scan" for everyday if there is a change
> and then append a row in a table but this is so slow... Is there a
> better "way" to proceed?
> Thanks!
>
Try the following. If you include DDL and sample data like this in future we
won't have to guess stuff like keys, datatypes, etc. You'll find you usually
get faster and better answers that way. Note my assumption about your key.
CREATE TABLE employees (employee INTEGER NOT NULL, date DATETIME NOT NULL,
department INTEGER NOT NULL, PRIMARY KEY (employee, date));
INSERT INTO employees VALUES (1234,'20010101',10);
INSERT INTO employees VALUES (1234,'20010102',11);
INSERT INTO employees VALUES (1234,'20010103',10);
INSERT INTO employees VALUES (1234,'20010104',10);
INSERT INTO employees VALUES (1234,'20010105',10);
INSERT INTO employees VALUES (1234,'20010106',10);
INSERT INTO employees VALUES (1234,'20010107',10);
INSERT INTO employees VALUES (1234,'20010108',10);
INSERT INTO employees VALUES (1234,'20010109',10);
SELECT employee,
MIN(date) AS from_dt, MAX(date) AS to_dt,
department
FROM
(SELECT E1.employee, E1.date,
MIN(E2.date) AS xdate, E1.department
FROM employees AS E1
LEFT JOIN employees AS E2
ON E1.employee = E2.employee
AND E1.date < E2.date
AND E1.department <> E2.department
GROUP BY E1.employee, E1.date, E1.department) AS T
GROUP BY employee, xdate, department ;
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Wow you really rock!
Your guess about my DDL is right...
Now How can I adapt this query if I have many hierarchy field (more
then just department, let's say I have supervisor_1, supervisor_2,
title, etc...)?|||If this is your desired result:
Employee
From
To
Department
.. then what you need is a group by query.
select
EmployeeID,
DepartmentID,
min(Date) as FromDate,
max(Date) as ToDate,
from
Employee
group by
EmployeeID,
DepartmentID
"Mathieu Dumais-Savard" <matdumsa@.gmail.com> wrote in message
news:1140465351.266005.229520@.f14g2000cwb.googlegroups.com...
> Hi JT, thanks for your answer...
> However, the number of columns is not subject to any change and the
> statement must be usable in a store proc to "compress" the table... I
> did a simple schema to explain it but the table contains millions of
> rows of department/day/employee... All I wanna do is to "shrink down"
> this table into a more friendly from..to manner...
>
> JT wrote:
>|||Glad I helped.

> Now How can I adapt this query if I have many hierarchy field (more
> then just department, let's say I have supervisor_1, supervisor_2,
> title, etc...)?
I'm sure I covered that too:

> If you include DDL and sample data like this in future we
> won't have to guess stuff like keys, datatypes, etc. You'll find you
> usually
> get faster and better answers that way.
To extend my query add the constant values on which you want to group (like
employee) to the GROUP BYs and the values for which you want to track
changes (department) to the join using <>. Post some sample data and show
your required end result if you need more.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Excellent, it worked David and I understood "the logic behind"...
However, it's a little slow with a big table but I guess that my table
is too big (1755202 rows) for that kind of logic to be effective... But
still beter then the cursor :p
BTW Do you have a site/repository where you can discover all those new
way of using T-SQL in a more effective way? Like challenge with answer
and so on? I would like to improve my skill so my designs ability will
be better.
Thanks!|||Advanced Transact-SQL for SQL Server 2000
http://www.amazon.com/gp/product/18...glance&n=283155
Joe Celko's SQL for Smarties: Advanced SQL Programming
http://www.amazon.com/gp/product/15...glance&n=283155
Joe Celko's SQL Puzzles and Answers
http://www.amazon.com/gp/product/15...glance&n=283155
Joe Celko's Trees and Hierarchies in SQL for Smarties
http://www.amazon.com/gp/product/15...glance&n=283155
AMB
"Mathieu Dumais-Savard" wrote:

> Excellent, it worked David and I understood "the logic behind"...
> However, it's a little slow with a big table but I guess that my table
> is too big (1755202 rows) for that kind of logic to be effective... But
> still beter then the cursor :p
> BTW Do you have a site/repository where you can discover all those new
> way of using T-SQL in a more effective way? Like challenge with answer
> and so on? I would like to improve my skill so my designs ability will
> be better.
> Thanks!
>|||David gave you an answer, but you need to change the schema now.
You want a Base table with the (start_time, end_time) times in it and
use a NULL for the current row You can then use COALESE( end_time,
CURRENT_TIMESTAMP) in queries and reports. It will run a lot faster
than cratign it on the fly over and over.
First buy a copy of SQL FOR SMARTIES. It is a great classic. And I
need the money.
Then look for a seocnd edition of my SQL PUZZLES within a year.