how 2 get consective /non-consective dates? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how 2 get consective /non-consective dates?

Author

Topic

majidbhutta
Starting Member

13 Posts

Posted - 2006-03-03 : 07:54:44

Wel consider the table structure with data is like below

Pk(int auto) DateField
1 02/12/2006
2 02/13/2006
3 02/14/2006
4 02/15/2006
5 02/17/2006
6 02/19/2006
7 02/20/2006
8 02/22/2006
9 02/24/2006
10 02/25/2006
11 02/26/2006
12 02/27/2006
13 02/28/2006

wel now my means by patch is that no. of consective occurances of dates now in the above
table there are THREE PATCHES(three occurances of consective dates)
FIRST PATCH
===========
1 02/12/2006
2 02/13/2006
3 02/14/2006
4 02/15/2006

SECOND PATCH
============
6 02/19/2006
7 02/20/2006

THRID PATCH
===========
9 02/24/2006
10 02/25/2006
11 02/26/2006
12 02/27/2006
13 02/28/2006

Now the Required out put is

pATCHID pATCHstartDate PATCHEndDate
1 02/12/2006 02/15/2006
2 02/19/2006 02/20/2006
3 02/24/2006 02/28/2006

also one more thing that i want also how to get non-consective dates in the above mentioned
table

How to get this
thnx in advance

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-03 : 08:58:47

Interesting situation. For anyone else trying to help, here's some DDL and DML:

create table tmp (pk int primary key, DateField datetime)

insert into tmp
select 1, '2/12/2006' union all
select 2,'02/13/2006' union all
select 3,'02/14/2006' union all
select 4,'02/15/2006' union all
select 5,'02/17/2006' union all
select 6,'02/19/2006' union all
select 7,'02/20/2006' union all
select 8,'02/22/2006' union all
select 9,'02/24/2006' union all
select 10,'02/25/2006' union all
select 11,'02/26/2006' union all
select 12,'02/27/2006' union all
select 13,'02/28/2006'

My first instinct was to apply the methodology I presented in my article on detecting streaks in your data, which you can see here: http://www.sqlteam.com/item.asp?ItemID=12654

However, the existing data that you have doesn't quite work for that. We can "pad" the data by adding at least 1 row between two dates by doing something like this:


select DateField, max([Exists]) as [Exists]
from
(
  select DateField, 1 as [Exists]
  from tmp
  union all
  select DateField+1,0
  from tmp
) a
group by DateField

That returns, for your data:


DateField                                              Exists      
------------------------------------------------------ ----------- 
2006-02-12 00:00:00.000                                1
2006-02-13 00:00:00.000                                1
2006-02-14 00:00:00.000                                1
2006-02-15 00:00:00.000                                1
2006-02-16 00:00:00.000                                0
2006-02-17 00:00:00.000                                1
2006-02-18 00:00:00.000                                0
2006-02-19 00:00:00.000                                1
2006-02-20 00:00:00.000                                1
2006-02-21 00:00:00.000                                0
2006-02-22 00:00:00.000                                1
2006-02-23 00:00:00.000                                0
2006-02-24 00:00:00.000                                1
2006-02-25 00:00:00.000                                1
2006-02-26 00:00:00.000                                1
2006-02-27 00:00:00.000                                1
2006-02-28 00:00:00.000                                1
2006-03-01 00:00:00.000                                0

(18 row(s) affected)

Now we have a format that works with my article. But it will be pretty inefficient, depending on how much data you have. Counting things "consecutively" in a set-based language like SQL isn't always easy.

So, with that sql I gave you, create a view from it. Call it whatever you want, but I will refer to it as "TempView" in the following code. Once that is done, you read my article and apply the technique shown on the view we created to produce the following:


select Min(DateField) as Start, Max(DateField) as [End], count(*) as DateCount
from
(
select a.*, (select count(*) from TempView b
           where b.[Exists] <> a.[Exists] and b.DateField <= a.DateField) as RunGroup
from TempView a
) a
where [Exists] = 1
group by RunGroup

Note the filter on [Exists]=1 -- we don't want to see the "blanks" that we padded in order to calculate the runs. The above returns everything, even "runs" that only have 1 consecutive date:

  
Start               End                       DateCount
------------------- ------------------------- ----------- 
2006-02-12          2006-02-15                4
2006-02-17          2006-02-17                1
2006-02-19          2006-02-20                2
2006-02-22          2006-02-22                1
2006-02-24          2006-02-28                5

(5 row(s) affected)

So, by looking at the count(*) -- the DateCount column -- we see that the 1's are the dates that are non-consectutive, and anything >1 is that many consectutive dates.

You also want to return a PatchID it appears, so we need to put the results into a temp table and let an identity calculate that. Our Final SQL, then, becomes:


select identity(int, 1,1) as PatchID, 
       Min(DateField) as PatchStartDate, 
       Max(DateField) as PatchEndDate
into 
       #tmp
from
(
   select a.*,  (select count(*) from TempView b
                 where b.[Exists] <> a.[Exists] and 
                       b.DateField <= a.DateField) as RunGroup
   from 
        TempView a
) a
where [Exists] = 1
group by RunGroup
having count(*) >1
order by Min(DateField)

select * from #tmp

which returns:


PatchID     Start                  End                                                    
----------- ---------------------- ------------------ 
1           2006-02-12             2006-02-15 
2           2006-02-19             2006-02-20 
3           2006-02-24             2006-02-28 

(3 row(s) affected)

which are the result you want. To see non-consecutive dates, you use a similiar SELECT but filter for Count(*) = 1.

As mentioned, for a lot of rows, this will be inefficient. But it does make for an interesting challenge and you'll learn a heck of a lot about sql if you can follow all this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources