Generate intervals

13 Sep 2006 8:41 AM

dragos.hilbert

In SQL Server 2000 I have a table:
Place
Id    Place Date
1    10       2006-01-01
2    10       2006-02-01
3    20       2006-03-01
4    20       2006-04-01
5    10       2006-06-01
6    10       2006-07-01
7    10       2006-08-01
Date is always 'yyyy-MM-01', no duplicate ( Place, Date ), Id is PK.

I want to generate intervals for every place.
I want to generate something like this:
Place    DateBegin     DateEnd
10         2006-01-01    2006-02-01
20         2006-03-01    2006-04-01
10         2006-06-01    2006-08-01

Is possible with one SQL without SP or function?

13 Sep 2006 9:49 AM

Uri Dimant

Hi
I guess you need
SELECT place ,MIN(date),MAX(date) FROM table
GROUP BY place

<dragos.hilb***@gmail.com> wrote in message
Show quoteHide quote

news:1158136901.655248.278470@i42g2000cwa.googlegroups.com...
> In SQL Server 2000 I have a table:
> Place
> Id    Place Date
> 1    10       2006-01-01
> 2    10       2006-02-01
> 3    20       2006-03-01
> 4    20       2006-04-01
> 5    10       2006-06-01
> 6    10       2006-07-01
> 7    10       2006-08-01
> Date is always 'yyyy-MM-01', no duplicate ( Place, Date ), Id is PK.
>
> I want to generate intervals for every place.
> I want to generate something like this:
> Place    DateBegin     DateEnd
> 10         2006-01-01    2006-02-01
> 20         2006-03-01    2006-04-01
> 10         2006-06-01    2006-08-01
>
> Is possible with one SQL without SP or function?
>

13 Sep 2006 10:53 AM

Roy Harvey

This seems to do the job.

CREATE TABLE Example
(ID int identity not null,
Place tinyint not null,
XDate datetime not null)

INSERT Example (Place, XDate) VALUES (10, '1 Jan 2006')
INSERT Example (Place, XDate) VALUES (10, '2 Jan 2006')
INSERT Example (Place, XDate) VALUES (20, '3 Jan 2006')
INSERT Example (Place, XDate) VALUES (20, '4 Jan 2006')
INSERT Example (Place, XDate) VALUES (10, '6 Jan 2006')
INSERT Example (Place, XDate) VALUES (10, '7 Jan 2006')
INSERT Example (Place, XDate) VALUES (10, '8 Jan 2006')

SELECT Place, Xdate as StartDate,
       (select MIN(XDate)
          from Example as E1
          where E1.Place = S1.Place
            and E1.XDate >= S1.XDate
            and NOT EXISTS
                (select * from Example as E2
                  where E1.Place = E2.Place
                    and E1.XDate =
                        DATEADD(day,-1,E2.XDate)))
         as EndDate
FROM Example as S1
WHERE NOT EXISTS
       (select * from Example as S2
         where S1.Place = S2.Place
           and S1.XDate = DATEADD(day,+1,S2.XDate))

The outer query finds the rows with the start dates, the subquery in
the SELECT list finds the matching end date.

Roy Harvey
Beacon Falls, CT

On 13 Sep 2006 01:41:41 -0700, dragos.hilb***@gmail.com wrote:

Show quoteHide quote

>In SQL Server 2000 I have a table:
>Place
>Id    Place Date
>1    10       2006-01-01
>2    10       2006-02-01
>3    20       2006-03-01
>4    20       2006-04-01
>5    10       2006-06-01
>6    10       2006-07-01
>7    10       2006-08-01
>Date is always 'yyyy-MM-01', no duplicate ( Place, Date ), Id is PK.
>
>I want to generate intervals for every place.
>I want to generate something like this:
>Place    DateBegin     DateEnd
>10         2006-01-01    2006-02-01
>20         2006-03-01    2006-04-01
>10         2006-06-01    2006-08-01
>
>Is possible with one SQL without SP or function?

13 Sep 2006 11:05 AM

Uri Dimant

Roy
I have been thinking for similar solution , however looking at 'place'
column ( value 10) I was thinking the OP had just mistaken puting 10 value
twice
so , now that I see your solution I reread the OP's post and see he writes
<no duplicate ( Place, Date )>

You are right , thanks for solution

Show quoteHide quote

"Roy Harvey" <roy_har***@snet.net> wrote in message
news:amofg2hk2jj8ljfalq233bu8tmtjq4n60s@4ax.com...
> This seems to do the job.
>
> CREATE TABLE Example
> (ID int identity not null,
> Place tinyint not null,
> XDate datetime not null)
>
> INSERT Example (Place, XDate) VALUES (10, '1 Jan 2006')
> INSERT Example (Place, XDate) VALUES (10, '2 Jan 2006')
> INSERT Example (Place, XDate) VALUES (20, '3 Jan 2006')
> INSERT Example (Place, XDate) VALUES (20, '4 Jan 2006')
> INSERT Example (Place, XDate) VALUES (10, '6 Jan 2006')
> INSERT Example (Place, XDate) VALUES (10, '7 Jan 2006')
> INSERT Example (Place, XDate) VALUES (10, '8 Jan 2006')
>
> SELECT Place, Xdate as StartDate,
>       (select MIN(XDate)
>          from Example as E1
>          where E1.Place = S1.Place
>            and E1.XDate >= S1.XDate
>            and NOT EXISTS
>                (select * from Example as E2
>                  where E1.Place = E2.Place
>                    and E1.XDate =
>                        DATEADD(day,-1,E2.XDate)))
>         as EndDate
> FROM Example as S1
> WHERE NOT EXISTS
>       (select * from Example as S2
>         where S1.Place = S2.Place
>           and S1.XDate = DATEADD(day,+1,S2.XDate))
>
> The outer query finds the rows with the start dates, the subquery in
> the SELECT list finds the matching end date.
>
> Roy Harvey
> Beacon Falls, CT
>
> On 13 Sep 2006 01:41:41 -0700, dragos.hilb***@gmail.com wrote:
>
>>In SQL Server 2000 I have a table:
>>Place
>>Id    Place Date
>>1    10       2006-01-01
>>2    10       2006-02-01
>>3    20       2006-03-01
>>4    20       2006-04-01
>>5    10       2006-06-01
>>6    10       2006-07-01
>>7    10       2006-08-01
>>Date is always 'yyyy-MM-01', no duplicate ( Place, Date ), Id is PK.
>>
>>I want to generate intervals for every place.
>>I want to generate something like this:
>>Place    DateBegin     DateEnd
>>10         2006-01-01    2006-02-01
>>20         2006-03-01    2006-04-01
>>10         2006-06-01    2006-08-01
>>
>>Is possible with one SQL without SP or function?