October 13, 2008 at 5:59 am
Could you post some sample data / table defintions that we can run?(See the link in my signature for an example if you don't know what I mean by this) This seems like something that could be handled fairly handily by Jeff's Advanced Querying Index technique (which I decided to adopt after it smoked my normal method 😉 ) In the meantime, I'll look for a link to that article.
October 13, 2008 at 6:33 am
I will see what I can do,
Just got to go to a meeting now but I will try and get it sorted straight after!!!
I just aded a row identifier but that as far as I could think 🙂
October 13, 2008 at 6:51 am
Hi Debbie
If you are able to add columns to your data set, how about adding a column to identify ECAF groups? This would make reporting on your data much easier. I'd recommend that you add a new SMALLINT column called ECAFgroup and populate it with 1 through n for each person, using the standard running totals method. This will require that your data is already ordered in the correct manner i.e. the same sequence that you would want it numbered (or there is a clustered index in the same sequence), which would probably be startdate.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 13, 2008 at 10:09 am
Thats exactly what I was thinking....
But I would want it to reset for each person every time it got to the next ECAFST
118007ECAFSTD5028582007-09-102007-09-10 1
118007ECAFMTG5033412007-12-11 2007-12-11 2
118007ECAFSTD5912282008-02-05 2008-02-05 1
118007ECAFMTG5982352008-02-14 2008-02-14 2
etc....
October 13, 2008 at 2:36 pm
Here is a shot at it 🙂
declare @tbl TABLE ( person int, [Type] varchar(20), id int, [Start_Date] datetime, End_Date datetime)
insert into @tbl ( person, Type, id, start_date, end_date)
select 118007,'ECAFSTD', 502858,'2007-09-10','2007-09-10'
union all select 118007,'ECAFMTG', 503341,'2007-09-10','2007-12-11'
union all select 118007,'ECAFSTD', 591228,'2008-02-05','2008-02-05'
union all select 118007,'ECAFMTG', 598235,'2008-02-14','2008-02-14'
union all select 118007,'ECAFRVW', 616430,'2008-03-12','2008-03-12'
union all select 118007,'ECAFRVW', 616435,'2008-03-12','2008-03-12'
union all select 118007,'ECAFRVW', 632985,'2008-04-04','2008-04-04'
--select * from @tbl
select t1.person,
t1.id,
t1.start_date,
(select top 1 t2.end_date
from @tbl t2
where t1.person = t2.person
and t2.type <> 'ECAFSTD'
and t2.end_date > t1.end_date
and t2.end_date <= COALESCE(( select min (t3.start_date)
from @tbl t3
where t3.person = t1.person
and t3.type = 'ECAFSTD'
and t3.start_date > t1.start_date ),'50000101')
order by t2.end_date desc ) end_date
from @tbl t1
where t1.type = 'ECAFSTD'
order by t1.start_date
Enjoy!
* Noel
October 14, 2008 at 12:36 am
Debbie Edwards (10/13/2008)
Thats exactly what I was thinking....But I would want it to reset for each person every time it got to the next ECAFST
118007ECAFSTD5028582007-09-102007-09-10 1
118007ECAFMTG5033412007-12-11 2007-12-11 2
118007ECAFSTD5912282008-02-05 2008-02-05 1
118007ECAFMTG5982352008-02-14 2008-02-14 2
etc....
Like this...
[font="Courier New"]DROP TABLE #ECAF
CREATE TABLE #ECAF (person INT, TYPE CHAR(7), [id] INT, Start_Date DATETIME, End_Date DATETIME, ECAF SMALLINT)
INSERT INTO #ECAF (person, TYPE, [id], Start_Date, End_Date)
SELECT 118007, 'ECAFSTD', 1, '2007-09-10', '2007-09-10' UNION ALL
SELECT 118007, 'ECAFMTG', 2, '2007-09-10', '2007-12-11' UNION ALL
SELECT 118007, 'ECAFSTD', 3, '2008-02-05', '2008-02-05' UNION ALL
SELECT 118007, 'ECAFMTG', 4, '2008-02-14', '2008-02-14' UNION ALL
SELECT 118007, 'ECAFRVW', 5, '2008-03-12', '2008-03-12' UNION ALL
SELECT 118007, 'ECAFRVW', 6, '2008-03-12', '2008-03-12' UNION ALL
SELECT 118007, 'ECAFRVW', 7, '2008-04-04', '2008-04-04' UNION ALL
SELECT 118008, 'ECAFSTD', 8, '2007-09-10', '2007-09-10' UNION ALL
SELECT 118008, 'ECAFMTG', 9, '2007-09-10', '2007-12-11' UNION ALL
SELECT 118008, 'ECAFSTD', 10, '2008-02-05', '2008-02-05' UNION ALL
SELECT 118008, 'ECAFMTG', 11, '2008-02-14', '2008-02-14' UNION ALL
SELECT 118008, 'ECAFRVW', 12, '2008-03-12', '2008-03-12' UNION ALL
SELECT 118008, 'ECAFRVW', 13, '2008-03-12', '2008-03-12' UNION ALL
SELECT 118008, 'ECAFRVW', 14, '2008-04-04', '2008-04-04'
DECLARE @person INT, @ECAF SMALLINT
SET @ECAF = 0
UPDATE #ECAF SET @ECAF = CASE WHEN @person <> person THEN 1 WHEN TYPE = 'ECAFSTD' THEN @ECAF+1 ELSE @ECAF END,
@person = person,
ECAF = @ECAF
SELECT person, ECAF, MIN(Start_Date) AS Start_Date, MAX(End_Date) AS End_Date
FROM #ECAF
GROUP BY person, ECAF
ORDER BY person, ECAF
[/font]
Results:
person ECAF Start_Date End_Date
----------- ------ ------------------------ -----------------------
118007 1 2007-09-10 00:00:00.000 2007-12-11 00:00:00.000
118007 2 2008-02-05 00:00:00.000 2008-04-04 00:00:00.000
118008 1 2007-09-10 00:00:00.000 2007-12-11 00:00:00.000
118008 2 2008-02-05 00:00:00.000 2008-04-04 00:00:00.000
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 14, 2008 at 2:00 am
Fantastic,
I will try these out this morning and let you know how I get on!
October 14, 2008 at 2:23 am
I think Im doing something very wrong.... :crazy:
I got the first example to bring back data but I dont understand the results. The 2nd example worked also but It just added 1 to every Ecaf row....
October 14, 2008 at 2:41 am
Hi Debbie
The "running total" update statement - the one with the variables in it - can be a bit quirky. It's best if the data is physically ordered in the order in which you expect the rows to be updated. You can force an order into the UPDATE statement by joining to a derived table:
[font="Courier New"]DECLARE @person INT, @ECAF SMALLINT
SET @ECAF = 0
UPDATE e SET @ECAF = CASE WHEN @person <> person THEN 1 WHEN TYPE = 'ECAFSTD' THEN @ECAF+1 ELSE @ECAF END,
@person = person,
ECAF = @ECAF
FROM #ECAF e
INNER JOIN (
SELECT TOP 100 PERCENT [id] FROM #ECAF
ORDER BY person, Start_Date
) f ON f.[id] = e.[id]
SELECT person, ECAF, MIN(Start_Date) AS Start_Date, MAX(End_Date) AS End_Date
FROM #ECAF
GROUP BY person, ECAF
ORDER BY person, ECAF[/font]
Give this a try, it's almost certainly the cause of screwy results.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 14, 2008 at 2:43 am
Debbie Edwards (10/14/2008)
I think Im doing something very wrong.... :crazy:I got the first example to bring back data but I dont understand the results. The 2nd example worked also but It just added 1 to every Ecaf row....
BTW here's an excellent article written by Jeff Moden describing running totals, will help you understand...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 14, 2008 at 3:35 am
Still just populating the column with 1's for everything.
The site isnt allowing me to look at the article about running totals either. an error has occured when I click on the link.
Who would have thought this would be so difficult :doze:
October 14, 2008 at 3:36 am
Debbie, how about posting a chunk of sample data?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 14, 2008 at 4:23 am
Is this any good?
/****** Object: Table [dbo].[ECAFALL] Script Date: 10/14/2008 11:10:05 ******/
CREATE TABLE [dbo].[ECAFALL](
[Person] [int] NULL,
[TYPE] [varchar](16) COLLATE Latin1_General_CI_AS NULL,
[id] [numeric](9, 0) NOT NULL,
[START_DATE] [datetime] NULL,
[END_DATE] [datetime] NULL,
[ECAF] [int] NULL
) ON [PRIMARY]
INSERT INTO [dbo].[ECAFALL]
(Person, Type, [ID], START_DATE, END_DATE)
SELECT 118007,'ECAFSTD',502858,'2007-09-10 10:31:38.697','2007-09-10 15:23:35.410'
UNION
SELECT 118007,'ECAFMTG',503341,'2007-09-10 15:19:50.047','2007-12-11 16:11:14.577'
UNION
SELECT 118007,'ECAFSTD',591228,'2008-02-05 20:16:38.080','2008-02-05 20:32:16.090'
UNION
SELECT 118007,'ECAFMTG',598235,'2008-02-14 16:09:20.507','2008-02-14 16:10:06.990'
UNION
SELECT 118007,'ECAFRVW',616430,'2008-03-12 10:48:36.223','2008-03-12 10:49:12.473'
UNION
SELECT 118007,'ECAFRVW',616435,'2008-03-12 10:51:03.527','2008-03-12 10:51:24.887'
UNION
SELECT 118007,'ECAFRVW',632985,'2008-04-04 13:56:53.023','2008-04-04 13:57:21.407'
It would be really good if the results came back as...
118007ECAFSTD5028582007-09-10 2007-09-10 1.1
118007ECAFMTG5033412007-09-10 2007-12-11 1.2
118007ECAFSTD5912282008-02-05 2008-02-05 2.1
118007ECAFMTG5982352008-02-14 2008-02-14 2.2
118007ECAFRVW6164302008-03-12 2008-03-12 2.3
118007ECAFRVW6164352008-03-12 2008-03-12 2.4
118007ECAFRVW6329852008-04-04 2008-04-04 2.5
So then you could go select a.Start_date
FROM [dbo].[ECAFALL] a WHERE CAF LIKE '1.%' AND TYPE = 'ECAFSTD'
SELECT b.End_DATE
FROM [dbo].[ECAFALL] b
WHERE CAF LIKE '1.%' AND MAX(RIGHT,CAF,1) Or something to that affect
October 14, 2008 at 4:30 am
DECLARE@Sample TABLE
(
person INT,
[Type] VARCHAR(20),
id INT,
[Start_Date] DATETIME,
End_Date DATETIME,
PRIMARY KEY CLUSTERED
(
person,
[start_date],
id
),
grp int
)
INSERT@Sample
(
person,
[Type],
id,
[Start_Date],
End_Date
)
SELECT118007, 'ECAFSTD', 502858, '2007-09-10', '2007-09-10' UNION ALL
SELECT118007, 'ECAFMTG', 503341, '2007-09-10', '2007-12-11' UNION ALL
SELECT118007, 'ECAFSTD', 591228, '2008-02-05', '2008-02-05' UNION ALL
SELECT118007, 'ECAFMTG', 598235, '2008-02-14', '2008-02-14' UNION ALL
SELECT118007, 'ECAFRVW', 616430, '2008-03-12', '2008-03-12' UNION ALL
SELECT118007, 'ECAFRVW', 616435, '2008-03-12', '2008-03-12' UNION ALL
SELECT118007, 'ECAFRVW', 632985, '2008-04-04', '2008-04-04' UNION ALL
SELECT118008, 'ECAFSTD', 502858, '2007-09-10', '2007-09-10' UNION ALL
SELECT118008, 'ECAFMTG', 503341, '2007-09-10', '2007-12-11'
DECLARE@person INT,
@grp INT
SELECT TOP 1@person = person,
@grp = 0
from@sample
order BYperson,
[start_date],
id
UPDATE@Sample
SET@grp = grp =CASE
WHEN @Person <> person then @grp + 1
when [Type] = 'ECAFSTD' THEN @grp + 1
ELSE @grp
END,
@person = person
SELECTs.person,
s.[Type],
s.id,
s.[Start_Date],
s.End_Date,
from(
SELECTMIN(id) as minID,
MAX(id) AS maxID,
grp
from@sample
group bygrp
) AS d
inner join@sample as s on s.grp = d.grp
wheres.id in (d.minid, d.maxid)
N 56°04'39.16"
E 12°55'05.25"
October 14, 2008 at 4:53 am
Hi Debbie, as Peso points out, your best bet is a clustered index on person + startdate.
This generates your new column. Personally, I'd normalise that into two columns, but here it is anyway:
[font="Courier New"]DROP TABLE #ECAFALL
CREATE TABLE #ECAFALL(
[Person] [int] NULL,
[Type] [varchar](16) COLLATE Latin1_General_CI_AS NULL,
[id] [numeric](9, 0) NOT NULL,
[START_DATE] [datetime] NULL,
[END_DATE] [datetime] NULL,
[ECAF] VARCHAR(6) NULL
) ON [PRIMARY]
INSERT INTO #ECAFALL
(Person, TYPE, [id], START_DATE, END_DATE)
SELECT 118007, 'ECAFSTD',502858,'2007-09-10 10:31:38.697','2007-09-10 15:23:35.410' UNION ALL
SELECT 118007, 'ECAFMTG',503341,'2007-09-10 15:19:50.047','2007-12-11 16:11:14.577' UNION ALL
SELECT 118007, 'ECAFSTD',591228,'2008-02-05 20:16:38.080','2008-02-05 20:32:16.090' UNION ALL
SELECT 118007, 'ECAFMTG',598235,'2008-02-14 16:09:20.507','2008-02-14 16:10:06.990' UNION ALL
SELECT 118007, 'ECAFRVW',616430,'2008-03-12 10:48:36.223','2008-03-12 10:49:12.473' UNION ALL
SELECT 118007, 'ECAFRVW',616435,'2008-03-12 10:51:03.527','2008-03-12 10:51:24.887' UNION ALL
SELECT 118007, 'ECAFRVW',632985,'2008-04-04 13:56:53.023','2008-04-04 13:57:21.407'
DECLARE @person INT, @ECAF SMALLINT, @ECAFno SMALLINT
SET @ECAF = 0
SET @person = 0
UPDATE e SET @ECAF = CASE WHEN @person <> Person THEN 1 WHEN TYPE = 'ECAFSTD' THEN @ECAF+1 ELSE @ECAF END,
@ECAFno = CASE WHEN @person <> Person OR TYPE = 'ECAFSTD' THEN 1 ELSE @ECAFno+1 END,
@person = Person,
ECAF = CAST(@ECAF AS VARCHAR) + '.' + CAST(@ECAFno AS VARCHAR)
FROM #ECAFALL e
INNER JOIN (
SELECT TOP 100 PERCENT [id]
FROM #ECAFALL
ORDER BY Person, START_DATE
) f ON f.[id] = e.[id]
SELECT * FROM #ECAFALL ORDER BY Person, START_DATE
[/font]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply