help with TSQL query 1

[fikir]

Hello everyone,
I have one question,

we have a table called organizations and It has three columns (actually it has more than three column but therese are enough for the query

orgId visitStartdate visitEndDate
1 01/01/2007 01/05/2007
1 02/05/2007 02/15/2007
2 01/01/2007 01/10/2007
2 01/01/2007 01/12/2007
2 02/15/2007 02/28/2007
3 03/01/2007 03/11/2007
3 04/05/2007 04/10/2007

I am trying to get the visit squence for each orgnization

the output should look like

orgId visitStartdate visitEndDate visit
1 01/01/2007 01/05/2007 v1
1 02/05/2007 02/15/2007 v2
2 01/01/2007 01/10/2007 v1
2 01/01/2007 01/12/2007 v2
2 02/15/2007 02/28/2007 v3
3 03/01/2007 03/11/2007 v1
3 04/05/2007 04/10/2007 v2

I wrote this query

select orgId, visitStartdate, visitEndDate,
'V' + count(visitStartdate) as visit
from organizations org1 inner join organizations org2
on org1.orgId = org2.orgId
and org1.visitStartdate >= org2.visitStartdate
group by orgId, visitStartdate, visitEndDate

it works fine if the the different visits have different visitStartDate, but not quite working if it is the same

the above query's resultset looks like

orgId visitStartdate visitEndDate visit
1 01/01/2007 01/05/2007 v1
1 02/05/2007 02/15/2007 v2
2 01/01/2007 01/10/2007 v2 wrong
2 01/01/2007 01/12/2007 v2
2 02/15/2007 02/28/2007 v3
3 03/01/2007 03/11/2007 v1
3 04/05/2007 04/10/2007 v2

how do I correct my query so that those visits with the same start date, by looking at their visitEndDate, get the right sequence.

Thanks

 

[bborissov]

What version of SQL Server?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[fikir]

sql 2000

 

[bborissov]

try:

[COLOR=blue]DECLARE[/color] @TestMain [COLOR=blue]TABLE[/color] (orgId [COLOR=blue]int[/color], visitStartdate [COLOR=#FF00FF]datetime[/color], visitEndDate [COLOR=#FF00FF]datetime[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @TestMain [COLOR=blue]VALUES[/color] (1,[COLOR=red]'01/01/2007'[/color], [COLOR=red]'01/05/2007'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @TestMain [COLOR=blue]VALUES[/color] (1,[COLOR=red]'02/05/2007'[/color], [COLOR=red]'02/15/2007'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @TestMain [COLOR=blue]VALUES[/color] (2,[COLOR=red]'01/01/2007'[/color], [COLOR=red]'01/10/2007'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @TestMain [COLOR=blue]VALUES[/color] (2,[COLOR=red]'01/01/2007'[/color], [COLOR=red]'01/12/2007'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @TestMain [COLOR=blue]VALUES[/color] (2,[COLOR=red]'02/15/2007'[/color], [COLOR=red]'02/28/2007'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @TestMain [COLOR=blue]VALUES[/color] (3,[COLOR=red]'03/01/2007'[/color], [COLOR=red]'03/11/2007'[/color])

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (orgId [COLOR=blue]int[/color], visitStartdate [COLOR=#FF00FF]datetime[/color], visitEndDate [COLOR=#FF00FF]datetime[/color], Id [COLOR=blue]int[/color] [COLOR=blue]IDENTITY[/color](1,1))

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @test
[COLOR=blue]SELECT[/color] OrgId, visitStartdate, visitEnddate
 [COLOR=blue]FROM[/color] @TestMain
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] OrgId, visitStartdate, visitEnddate


[COLOR=blue]SELECT[/color] * [COLOR=blue]from[/color] @test


[COLOR=blue]SELECT[/color] TestMain.*,
       [COLOR=red]'v'[/color]+[COLOR=#FF00FF]CAST[/color](TestMe.Id-TestMe.MinId+1 [COLOR=blue]as[/color] [COLOR=blue]varchar[/color](50)) [COLOR=blue]as[/color] Test
[COLOR=blue]FROM[/color] @TestMain TestMain
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] test.OrgId,
                   test.visitStartdate,
                   test.visitEndDate,
                   test.Id,
                   Test2.Id [COLOR=blue]AS[/color] MinId
            [COLOR=blue]FROM[/color] @Test test
            [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] OrgId, [COLOR=#FF00FF]MIN[/color](Id) [COLOR=blue]AS[/color] Id
                               [COLOR=blue]FROM[/color] @Test
                        [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] OrgId) Test2
            [COLOR=blue]ON[/color] Test.OrgId = Test2.OrgId) TestMe
[COLOR=blue]ON[/color] TestMain.OrgId = TestMe.OrgId AND
   TestMain.visitStartdate = TestMe.visitStartdate AND
   TestMain.visitEndDate = TestMe.visitEndDate

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[fikir]

Thank you Borislav, That was really a great help

Thanks again

 

[ESquared]

declare @temp table (orgid int, visitStartDate datetime, visitEndDate datetime)

insert @temp
select 1,          '01/01/2007',        '01/05/2007'
union all select 1,          '02/05/2007',        '02/15/2007'
union all select 2,          '01/01/2007',        '01/10/2007'
union all select 2,          '01/01/2007',        '01/12/2007'
union all select 2,          '02/15/2007',        '02/28/2007'
union all select 3,          '03/01/2007',        '03/11/2007'
union all select 3,          '04/05/2007',        '04/10/2007'


select
   t.*,
   visit = 'v' + convert(varchar(11), x.cnt)
from
   @temp t
   inner join (
      select t1.orgid, t1.visitstartdate, t1.visitenddate, cnt = count(*)
      from
         @temp t1
         inner join @temp t2 on t1.orgid = t2.orgid and (t1.visitstartdate > t2.visitstartdate or (t1.visitstartdate = t2.visitstartdate and t1.visitenddate >= t2.visitenddate))
      group by 
         t1.orgid, t1.visitstartdate, t1.visitenddate
   ) x on t.orgid = x.orgid and x.visitstartdate = t.visitstartdate and x.visitenddate = t.visitenddate         
order by
   t.orgid, t.visitstartdate, t.visitenddate


select
   t.*,
   visit = 'v' + convert(varchar(11), (select count(*) from @temp x where t.orgid = x.orgid and (t.visitstartdate > x.visitstartdate or (t.visitstartdate = x.visitstartdate and t.visitenddate >= x.visitenddate))))
from
   @temp t

If performance matters, be sure to test to see what works best. Each approach given can be the best in different circumstances and with different criteria and amounts of data.

And experiment with indexes on your tables, source and temp tables, as they can drastically affect performance.

[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]