Start and End Date Groupings

Question

Post reply

Start and End Date Groupings

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 2

Im still going to keep trying with it but Im just getting an error at the moment

Msg 156, Level 15, State 1, Line 24

Incorrect syntax near the keyword 'from'.

Msg 156, Level 15, State 1, Line 30

Incorrect syntax near the keyword 'AS'.

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 3

Sorry that was for the SQL just before the last script was added. I took a comma out and it brought back some data but there isnt a running total count in it. Just the IDs and dates. Ill try the last script too.

Ill be OK I think when I start to understand what the results mean in terms of what I need to do.

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 4

Hi again,

Finally managed to get my head round this and it works excellently with the one pupil in it but as soon as you try using it with more than one pupil the ECAF column all sets as 1.1

Chris Morris (10/14/2008)
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]

I have put together another example of the data that will need to go in with two person IDs

/****** 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] [varchar](6)

) ON [PRIMARY]

INSERT INTO [dbo].[ECAFALL]

(Person, Type, [ID], START_DATE, END_DATE)

SELECT 12054,'ECAFSTD',496815,'2007-08-29 13:37:35.923','2007-08-29 13:38:15.357'

UNION

SELECT 12054,'ECAFMTG',496820,'2007-08-29 13:41:17.713','2008-02-12 13:38:11.180'

UNION

SELECT 12054,'ECAFSTD',599357,'2008-02-16 06:03:48.650','2008-02-16 06:34:27.663'

UNION

SELECT 12054,'ECAFMTG',605097,'2008-02-26 09:55:22.210',NULL

UNION

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'

Is there a way of using the script shown for this data??? It would work perfectly if so.

Debbie

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

What is the expected output based on the provided sample data?

N 56°04'39.16"
E 12°55'05.25"

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 6

Peso (10/21/2008)
What is the expected output based on the provided sample data?

12054ECAFSTD 4968152007-08-29 13:37:35.923 2007-08-29 13:38:15.357 1.1

12054ECAFMTG 4968202007-08-29 13:41:17.713 2008-02-12 13:38:11.180 1.2

12054 ECAFSTD 5993572008-02-16 06:03:48.650 2008-02-16 06:34:27.663 2.1

12054 ECAFMTG 6050972008-02-26 09:55:22.210 NULL2.2

118007ECAFSTD5028582007-09-10 10:31:38.6972007-09-10 15:23:35.4101.1

118007ECAFMTG5033412007-09-10 15:19:50.0472007-12-11 16:11:14.5771.2

118007ECAFSTD5912282008-02-05 20:16:38.0802008-02-05 20:32:16.0902.1

118007ECAFMTG5982352008-02-14 16:09:20.5072008-02-14 16:10:06.9902.2

118007ECAFRVW6164302008-03-12 10:48:36.2232008-03-12 10:49:12.4732.3

118007ECAFRVW6164352008-03-12 10:51:03.5272008-03-12 10:51:24.8872.4

118007ECAFRVW6329852008-04-04 13:56:53.0232008-04-04 13:57:21.4072.5

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Hi Debbie

With a clustered index on your table...

DROP TABLE dbo.ECAFALL

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] [varchar](6)

) ON [PRIMARY]

CREATE CLUSTERED INDEX [ID_START_DATE] ON [dbo].[ECAFALL]([Person], [START_DATE], [END_DATE]) ON [PRIMARY]

And using this statement...

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

SELECT * FROM ECAFALL ORDER BY Person, START_DATE

You get these results...

Person TYPE ID START_DATE END_DATE ECAF

----------- ------- ----------- ----------------------- ----------------------- ------

12054 ECAFSTD 496815 2007-08-29 13:37:35.923 2007-08-29 13:38:15.357 1.1

12054 ECAFMTG 496820 2007-08-29 13:41:17.713 2008-02-12 13:38:11.180 1.2

12054 ECAFSTD 599357 2008-02-16 06:03:48.650 2008-02-16 06:34:27.663 2.1

12054 ECAFMTG 605097 2008-02-26 09:55:22.210 NULL 2.2

118007 ECAFSTD 502858 2007-09-10 10:31:38.697 2007-09-10 15:23:35.410 1.1

118007 ECAFMTG 503341 2007-09-10 15:19:50.047 2007-12-11 16:11:14.577 1.2

118007 ECAFSTD 591228 2008-02-05 20:16:38.080 2008-02-05 20:32:16.090 2.1

118007 ECAFMTG 598235 2008-02-14 16:09:20.507 2008-02-14 16:10:06.990 2.2

118007 ECAFRVW 616430 2008-03-12 10:48:36.223 2008-03-12 10:49:12.473 2.3

118007 ECAFRVW 616435 2008-03-12 10:51:03.527 2008-03-12 10:51:24.887 2.4

118007 ECAFRVW 632985 2008-04-04 13:56:53.023 2008-04-04 13:57:21.407 2.5

How does it look?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 8

excellent,

It worked perfectly apart from one wierd kink. I just ran this script to check the results are OK...

SELECT tblpupil, LEFT(CAF_FIRST_FORM,1)AS CAF_No,COUNT(*)AS NO_OF_EPS

FROM ECAFALL

WHERE tblPUPIL IS NOT NULL

GROUP BY tblpupil, LEFT(CAF_FIRST_FORM,1)

ORDER BY tblPUPIL

And here is an example of the top few rows

PersonCAFEPISODE

39192

121812

1205412

1205422

11800712

11800725

so everything works great, 12054 have 2 CAFS. CAF 1 has 2 episodes within it and CAF 2 also has 2 episodes within it. However, for some reason Person 391 starts at 9 rather than 1 and this is the only one that does it. The only difference I can find is that Person 391 has only 3 digits. Ive tried to go through the script to see if I can find what the problem is but Ive had no luck.

If it wasnt for this though it would be good to go!

Debbie

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 9

Ah ha,

All sorted it was because in dbo.ECAFALL there were 10 rows where Person was NULL. This caused the error with that Person ID. I clear the NULLs up first and it all works perfectly.

🙂

Thanks so much for all your help!

Debbie