self-joining derived tables?
-
Heh... sorry Lynn... wasn't directed specifically at you... was a general comment. I agree with the idea of them allowing folks to "build comples queries incrementally".
The only thing I don't like about them is that they're more fleeting than a variable 'cause they only last for a single query even if multiple queries are in the same proc. They are very handy if you only need them like that.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
As a test, I refactored the CTE as a variable, and it close to doubled the execution time.
Is there a way to use a CTE to create another CTE?
-
Take this and run with it. You may need to tweak it to meet your needs but it is as close as i can get it for you.
set nocount on
DECLARE @data TABLE(
id1 INT NOT NULL,
id2 TINYINT NOT NULL,
INT,
[assignDate] SMALLDATETIME,
[cType] CHAR(2),
[closeDate] SMALLDATETIME);
INSERT @data VALUES (2101,5,15123,'2005-03-04','CC',NULL);
INSERT @data VALUES (2101,5,15123,'2006-02-07','CC','2006-07-01');
INSERT @data VALUES (2499,4,07195,'2006-01-04','DP',NULL);
INSERT @data VALUES (2499,4,10068,'2006-01-10','DP',NULL);
INSERT @data VALUES (2499,4,09573,'2006-01-17','DP',NULL);
INSERT @data VALUES (2499,4,09573,'2006-09-25','DC',NULL);
INSERT @data VALUES (3327,5,07957,'2005-09-08','CC','2006-10-31');
INSERT @data VALUES (3352,4,07801,'2005-09-20','DC',NULL);
INSERT @data VALUES (3352,4,07801,'2006-05-17','DC',NULL);
INSERT @data VALUES (3352,4,08328,'2006-07-01','DC',NULL);
INSERT @data VALUES (3352,4,17698,'2006-10-20','DC',NULL);
INSERT @data VALUES (3606,3,09573,'2005-05-17','UC','2006-05-15');
INSERT @data VALUES (3687,0,84085,'2006-07-17','CF','2006-08-07');
INSERT @data VALUES (3687,2,03327,'2005-12-23','DC',NULL);
INSERT @data VALUES (3687,2,03327,'2006-08-15','DC',NULL);
INSERT @data VALUES (3702,0,08327,'2006-03-17','CF','2007-05-17'); --should be skipped
INSERT @data VALUES (3702,4,89515,'2006-06-05','UY','2007-01-29'); --should be skipped
INSERT @data VALUES (3963,5,12241,'2006-06-16','DP','2007-01-02'); --should be skipped
INSERT @data VALUES (4000,2,05233,'2006-01-18','DC',NULL);
INSERT @data VALUES (4000,2,19408,'2006-07-03','DC',NULL);
INSERT @data VALUES (7772,2,15463,'2004-03-04','DC',NULL);
INSERT @data VALUES (7772,2,15463,'2006-02-28','DC',NULL);
INSERT @data VALUES (7772,2,10035,'2006-06-30','DC','2006-09-08');
INSERT @data VALUES (7772,5,15463,'2006-02-06','CC','2006-05-31');
INSERT @data VALUES (8477,0,17673,'2006-07-17','CF','2007-04-12'); --should be skipped
INSERT @data VALUES (8554,3,12461,'2006-10-18','UC','2006-10-19');
INSERT @data VALUES (8557,3,12338,'2006-01-08','DC','2006-02-17');
INSERT @data VALUES (8557,3,12338,'2006-02-17','DP',NULL);
INSERT @data VALUES (8557,3,12338,'2006-06-15','DC',NULL);
INSERT @data VALUES (11753,4,80325,'2005-10-31','UC','2006-05-10');
INSERT @data VALUES (11753,4,80325,'2006-05-10','US',NULL);
INSERT @data VALUES (11909,0,06290,'2006-01-01','CF','2006-01-24'); --should be skipped
INSERT @data VALUES (11909,5,06290,'2006-01-06','UC',NULL);
INSERT @data VALUES (11909,5,60447,'2006-01-24','UC',NULL);
INSERT @data VALUES (11909,5,46342,'2006-01-25','UC','2006-04-05');
INSERT @data VALUES (11979,2,03327,'2005-05-10','CC',NULL);
INSERT @data VALUES (11979,2,03327,'2006-05-09','CC','2006-07-03');
declare @start datetime,
@end datetime;
set @start = '2006-01-01';
set @end = '2006-12-31';
with BaseData_cte (
id1,
id2,
userId,
cType,
assignDate,
closeDate
) as (
select
id1,
id2,
,
cType,
min(assignDate) as assignDate,
max(closeDate) as closeDate
from
where
cType like '_C%'
and (assignDate between @start and @end
or (closeDate between @start and @end
or closeDate is null))
group by
id1,
id2,
,
cType
),
WorkingData_cte (
RowNumber,
id1,
id2,
userId,
cType,
assignDate,
closeDate
) as (
select
row_number() over(partition by id1, id2 order by id1, id2,cType, assignDate) as RowNumber,
id1,
id2,
userId,
cType,
assignDate,
closeDate
from BaseData_cte
)
select
a.id1,
a.id2,
a.userId,
a.cType,
a.assignDate,
coalesce(a.closeDate,b.assignDate) as closeDate
from
WorkingData_cte a
left outer join WorkingData_cte b
on (a.RowNumber = b.RowNumber -1
and a.id1 = b.id1
and a.id2 = b.id2)
set nocount off
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply