get squence of date 1

fikir · Oct 23, 2007

I have this table, with site id, visitstartdate and visitenddate

I am trying to get the sequence of visit, and wrote this query

Code:

DECLARE @TBL TABLE (ID INT, VSTARTDATE DATETIME, VENDDATE DATETIME)

INSERT INTO @TBL
SELECT 1, '01/01/2007', '01/10/2007'
UNION ALL SELECT 1, '01/03/2007', '01/15/2007'
UNION ALL SELECT 1, '01/04/2007', '01/16/2007'
UNION ALL SELECT 2, '01/01/2007', '01/15/2007'

SELECT TBL.ID, TBL.VSTARTDATE, 'v' + CONVERT(VARCHAR(10), COUNT(TBL2.VSTARTDATE)) AS VISIT
FROM @TBL TBL LEFT JOIN @TBL TBL2
     ON TBL.ID = TBL2.ID
     AND DATEPART(QUARTER, TBL.VSTARTDATE) = DATEPART(QUARTER, TBL2.VSTARTDATE)
     AND DATEPART(YEAR, TBL.VSTARTDATE) = DATEPART(YEAR, TBL2.VSTARTDATE)
     AND TBL.VSTARTDATE >= TBL2.VSTARTDATE
GROUP BY TBL.ID, TBL.VSTARTDATE
ORDER BY TBL.ID

this workis fine if the site have different visitstartdate, the proble comes if the site has two differnt visits with the same visitdate. In this case I have to consider the visitenddate and visitenddate of lesser date become the first and the later will be the second visit

example for the table

Code:

DECLARE @TBL TABLE (ID INT, VSTARTDATE DATETIME, VENDDATE DATETIME)

INSERT INTO @TBL
SELECT 1, '01/01/2007', '01/10/2007'
UNION ALL SELECT 1, '01/03/2007', '01/15/2007'
UNION ALL SELECT 1, '01/03/2007', '01/16/2007'
UNION ALL SELECT 2, '01/01/2007', '01/15/2007'

the output should look like

id     visitstartdate  visit

1	2007-01-01 	v1
1	2007-01-03 	v2
1	2007-01-03 	v3
2	2007-01-01 	v1

how I could I do that

Thanks

AlexCuse · Oct 23, 2007

What version of SQL Server are you usint?

[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.

AlexCuse · Oct 23, 2007

Anyways I think this will do it for you. THANK YOU SO MUCH for posting sample data on the first try

Code:

[COLOR=blue]DECLARE[/color] @TBL [COLOR=blue]TABLE[/color] (ID [COLOR=blue]INT[/color], VSTARTDATE [COLOR=#FF00FF]DATETIME[/color], VENDDATE [COLOR=#FF00FF]DATETIME[/color])

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @TBL
[COLOR=blue]SELECT[/color] 1, [COLOR=red]'01/01/2007'[/color], [COLOR=red]'01/10/2007'[/color]
UNION ALL [COLOR=blue]SELECT[/color] 1, [COLOR=red]'01/03/2007'[/color], [COLOR=red]'01/15/2007'[/color]
UNION ALL [COLOR=blue]SELECT[/color] 1, [COLOR=red]'01/03/2007'[/color], [COLOR=red]'01/16/2007'[/color]
UNION ALL [COLOR=blue]SELECT[/color] 2, [COLOR=red]'01/01/2007'[/color], [COLOR=red]'01/15/2007'[/color]

[COLOR=blue]SELECT[/color] TBL.ID, TBL.VSTARTDATE, TBL.VENDDATE 
	,[COLOR=red]'v'[/color] + [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]VARCHAR[/color](10), [COLOR=#FF00FF]COUNT[/color](TBL2.VSTARTDATE)) [COLOR=blue]AS[/color] VISIT
[COLOR=blue]FROM[/color] @TBL TBL [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] @TBL TBL2
     [COLOR=blue]ON[/color] TBL.ID = TBL2.ID
     AND [COLOR=#FF00FF]DATEPART[/color]([COLOR=#FF00FF]QUARTER[/color], TBL.VSTARTDATE) = [COLOR=#FF00FF]DATEPART[/color]([COLOR=#FF00FF]QUARTER[/color], TBL2.VSTARTDATE)
     AND [COLOR=#FF00FF]DATEPART[/color]([COLOR=#FF00FF]YEAR[/color], TBL.VSTARTDATE) = [COLOR=#FF00FF]DATEPART[/color]([COLOR=#FF00FF]YEAR[/color], TBL2.VSTARTDATE)
     AND TBL.VSTARTDATE >= TBL2.VSTARTDATE
     AND TBL.VENDDATE >= TBL2.VENDDATE
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] TBL.ID, TBL.VSTARTDATE, TBL.VENDDATE
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] TBL.ID

Hope this helps,

Alex

[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.

fikir · Oct 23, 2007

Alex,

Thank you very much for your swift response, I see one problem with this solution

check it for this data

Code:

DECLARE @TBL TABLE (ID INT, VSTARTDATE DATETIME, VENDDATE DATETIME)

INSERT INTO @TBL
SELECT 1, '01/01/2007', '01/10/2007'
UNION ALL SELECT 1, '01/03/2007', '01/17/2007'
UNION ALL SELECT 1, '01/04/2007', '01/16/2007'
UNION ALL SELECT 2, '01/01/2007', '01/15/2007'

[\code]

Thanks

AlexCuse · Oct 23, 2007

Indeed. What is your SQL SErver version?

[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.

fikir · Oct 23, 2007

sql 2000

AlexCuse · Oct 23, 2007

I was afraid of that :-(

Try this out, it is ugly and involves a temp table, but it may be the only way (you could also explicitly declare a table variable, but this was quicker for me to throw together). If this is to go into production, make sure to test both methods!

Code:

[COLOR=green]--sample data
[/color][COLOR=blue]DECLARE[/color] @TBL [COLOR=blue]TABLE[/color] (ID [COLOR=blue]INT[/color], VSTARTDATE [COLOR=#FF00FF]DATETIME[/color], VENDDATE [COLOR=#FF00FF]DATETIME[/color])

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @TBL
[COLOR=blue]SELECT[/color] 1, [COLOR=red]'01/01/2007'[/color], [COLOR=red]'01/10/2007'[/color]
UNION ALL [COLOR=blue]SELECT[/color] 1, [COLOR=red]'01/03/2007'[/color], [COLOR=red]'01/15/2007'[/color]
UNION ALL [COLOR=blue]SELECT[/color] 1, [COLOR=red]'01/04/2007'[/color], [COLOR=red]'01/16/2007'[/color]
UNION ALL [COLOR=blue]SELECT[/color] 1, [COLOR=red]'01/04/2007'[/color], [COLOR=red]'01/15/2007'[/color]
UNION ALL [COLOR=blue]SELECT[/color] 2, [COLOR=red]'01/01/2007'[/color], [COLOR=red]'01/15/2007'[/color]


[COLOR=green]--query to insert into table ordered by STARTDATE
[/color][COLOR=green]--as well as sequence of end dates within STARTDATE
[/color][COLOR=blue]select[/color] a.ID, a.VSTARTDATE, a.VENDDATE, [COLOR=blue]identity[/color]([COLOR=blue]int[/color], 1,1) ID2 [COLOR=blue]into[/color] #T 
[COLOR=blue]from[/color]  (
	[COLOR=blue]SELECT[/color] TBL.ID, TBL.VSTARTDATE, TBL.VENDDATE, [COLOR=#FF00FF]COUNT[/color](b.ID) SDSEQ
	[COLOR=blue]from[/color] @TBL TBL
	[COLOR=blue]inner[/color] [COLOR=blue]join[/color] @TBL b
	[COLOR=blue]on[/color] TBL.VSTARTDATE = b.VSTARTDATE
	and TBL.VENDDATE >= b.VENDDATE
	[COLOR=blue]group[/color] [COLOR=blue]by[/color] TBL.ID, TBL.VSTARTDATE, TBL.VENDDATE
) a
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] @TBL b
     [COLOR=blue]ON[/color] a.ID = b.ID
     AND [COLOR=#FF00FF]DATEPART[/color]([COLOR=#FF00FF]QUARTER[/color], a.VSTARTDATE) = [COLOR=#FF00FF]DATEPART[/color]([COLOR=#FF00FF]QUARTER[/color], b.VSTARTDATE)
     AND [COLOR=#FF00FF]DATEPART[/color]([COLOR=#FF00FF]YEAR[/color], a.VSTARTDATE) = [COLOR=#FF00FF]DATEPART[/color]([COLOR=#FF00FF]YEAR[/color], b.VSTARTDATE)
     AND a.VSTARTDATE >= b.VSTARTDATE
[COLOR=blue]group[/color] [COLOR=blue]by[/color] a.ID, a.VSTARTDATE, a.VENDDATE, a.SDSEQ

[COLOR=green]--get desired result from temp table
[/color][COLOR=blue]select[/color] a.ID, a.VSTARTDATE, a.VENDDATE, [COLOR=red]'v'[/color] + [COLOR=#FF00FF]cast[/color]([COLOR=#FF00FF]count[/color](b.ID) [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](10)) [COLOR=blue]as[/color] vID
[COLOR=blue]from[/color] #T a
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] #T b
     [COLOR=blue]ON[/color] a.ID = b.ID
     and a.ID2 >= b.ID2
[COLOR=blue]group[/color] [COLOR=blue]by[/color] a.ID, a.VSTARTDATE, a.VENDDATE

[COLOR=green]--drop temp table
[/color][COLOR=blue]drop[/color] [COLOR=blue]table[/color] #t

Hope this helps (post back with any questions, I gotta run now though),

Alex

[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.

fikir · Oct 23, 2007

Here is the ** for you

Thank you

ESquared · Oct 23, 2007

Here's another way to do it. I have no idea if it's better or worse. I'm gathering that you want to restart your visit numbering by quarter? If not, just remove the DateAdd group by clause.

Code:

DECLARE @TBL TABLE (ID INT, VSTARTDATE DATETIME, VENDDATE DATETIME)

INSERT INTO @TBL
SELECT 1, '01/01/2007', '01/10/2007'
UNION ALL SELECT 1, '01/03/2007', '01/15/2007'
UNION ALL SELECT 1, '01/03/2007', '01/17/2007'
UNION ALL SELECT 1, '01/04/2007', '01/16/2007'
UNION ALL SELECT 2, '01/01/2007', '01/15/2007'

SELECT ID2 = identity(int, 1, 1), ID, VSTARTDATE, VENDDATE
INTO #seq
FROM @TBL

SELECT
   S.ID, S.VStartDate, S.VEndDate, 'v' + cast(ID2 - Q.N + 1 as varchar(10)) as vID
FROM
   #seq S
   INNER JOIN (
      SELECT N = Min(ID2), X = Max(ID2)
      FROM #seq
      GROUP BY ID, DateAdd(qq, DateDiff(qq, 0, VStartDate), 0)
   ) Q ON S.ID2 BETWEEN Q.N AND Q.X

DROP TABLE #seq

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

ESquared · Oct 23, 2007

er, I forgot the order by clause somehow. Which actually makes me rethink... does a select into honor order by? I am not sure that it does. I am sure that an insert honors order by. So it might have to be something like this. Or if you didn't want to create the table using DDL you could DML it with SELECT INTO FROM @TBL WHERE 1 = 0, then do the insert.

Code:

CREATE TABLE #seq (ID2 int identity(1,1), ID int, VStartDate datetime, VEndDate datetime)

INSERT #seq
SELECT ID, VStartDate, VEndDate
INTO #seq
FROM @TBL
ORDER BY ID, VStartDate, VEndDate

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

get squence of date 1

fikir

Programmer

AlexCuse

Programmer

AlexCuse

Programmer

fikir

Programmer

AlexCuse

Programmer

fikir

Programmer

AlexCuse

Programmer

fikir

Programmer

ESquared

Programmer

ESquared

Programmer

Similar threads

Part and Inventory Search

Sponsor