March 3, 2006 at 5:39 am
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(Occurance)
===========
1 02/12/2006
2 02/13/2006
3 02/14/2006
4 02/15/2006
SECOND PATCH(Occurance)
============
6 02/19/2006
7 02/20/2006
THRID PATCH(Occurance)
===========
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 a query for how to get non-consective dates in the above mentioned
table
How to get this
thnx in advance
March 3, 2006 at 8:20 am
This should get you there but I'm sure there is a more elegant way. Watch out if you have lots of data - I think the the 2 * 3 self joins are not very good for performance.
NB I used an INT in place of your dates but the principle should stay the same.
CREATE TABLE t (i INT)
INSERT INTO t VALUES (0)
INSERT INTO t VALUES (2)
INSERT INTO t VALUES (3)
INSERT INTO t VALUES (4)
INSERT INTO t VALUES (5)
INSERT INTO t VALUES (7)
INSERT INTO t VALUES (8)
INSERT INTO t VALUES (10)
INSERT INTO t VALUES (12)
INSERT INTO t VALUES (14)
INSERT INTO t VALUES (15)
INSERT INTO t VALUES (16)
INSERT INTO t VALUES (19)
CREATE VIEW vwStarters
AS
SELECT
t1.i
FROM
t t1 LEFT OUTER JOIN t t2
ON t1.i = t2.i -1
LEFT OUTER JOIN t t3
ON t1.i = t3.i +1
WHERE
t2.i IS NOT NULL AND t3.i IS NULL
-- these are group starters
-- need to join to group enders , reverse it ?
CREATE VIEW vwEnders
AS
SELECT
t1.i
FROM
t t1 LEFT OUTER JOIN t t2
ON t1.i = t2.i -1
LEFT OUTER JOIN t t3
ON t1.i = t3.i +1
WHERE
t2.i IS NULL AND t3.i IS NOT NULL
CREATE TABLE #tblGroups
(GroupID INT IDENTITY,
Start INT,
[End] INT)
INSERT INTO #tblGroups
(Start,
[End])
SELECT
s.i AS Startt,
MIN(e.i) AS [End]
FROM
vwStarters s INNER JOIN vwEnders e
ON s.i < e.i
GROUP BY
s.i
SELECT * FROM #tblGroups
March 3, 2006 at 9:19 am
I can cut it down by one view which should improve performance.
CREATE VIEW vwStartAndEnd
AS
SELECT
t1.i,
CASE
WHEN t2.i IS NOT NULL THEN 'Start'
WHEN t3.i IS NOT NULL THEN 'End'
ELSE 'Error'
END AS StartEnd
FROM
t t1 LEFT OUTER JOIN t t2
ON t1.i = t2.i -1
LEFT OUTER JOIN t t3
ON t1.i = t3.i +1
WHERE
(t2.i IS NULL AND t3.i IS NOT NULL)
OR (t2.i IS NOT NULL AND t3.i IS NULL)
SELECT
v1.i,
MIN(v2.i)
FROM vwStartAndEnd v1 INNER JOIN vwStartAndEnd v2
ON v1.i < v2.i
WHERE
v1.StartEnd = 'Start'
AND v2.StartEnd = 'End'
GROUP BY
v1.i
March 3, 2006 at 10:39 am
declare @table table
(ID_VAL INT identity(1,1),
Date_Field Datetime
)
insert into @table values ('02/12/2006')
insert into @table values ('02/13/2006')
insert into @table values ('02/14/2006')
insert into @table values ('02/15/2006')
insert into @table values ('02/17/2006')
insert into @table values ('02/19/2006')
insert into @table values ('02/20/2006')
insert into @table values ('02/22/2006')
insert into @table values ('02/24/2006')
insert into @table values ('02/25/2006')
insert into @table values ('02/26/2006')
insert into @table values ('02/27/2006')
insert into @table values ('02/28/2006')
select tab.*
from
@table tab
where exists (select 1 from
(select a.id_val
from @table a
where coalesce(datediff (dd,
(select max(date_field) from @table where date_field < a.date_field),
a.date_field), 1) = 1) X
where tab.id_val = x.id_val OR TAB.ID_VAL = x.id_val - 1)
--Output
ID_VAL Date_Field
----------- -----------------------
1 2006-02-12 00:00:00.000
2 2006-02-13 00:00:00.000
3 2006-02-14 00:00:00.000
4 2006-02-15 00:00:00.000
6 2006-02-19 00:00:00.000
7 2006-02-20 00:00:00.000
9 2006-02-24 00:00:00.000
10 2006-02-25 00:00:00.000
11 2006-02-26 00:00:00.000
12 2006-02-27 00:00:00.000
13 2006-02-28 00:00:00.000
March 3, 2006 at 5:09 pm
Try this without using views:
declare @table table
(ID_VAL INT identity(1,1),
Date_Field Datetime
)
insert into @table values ('02/12/2006')
insert into @table values ('02/13/2006')
insert into @table values ('02/14/2006')
insert into @table values ('02/15/2006')
insert into @table values ('02/17/2006')
insert into @table values ('02/19/2006')
insert into @table values ('02/20/2006')
insert into @table values ('02/22/2006')
insert into @table values ('02/24/2006')
insert into @table values ('02/25/2006')
insert into @table values ('02/26/2006')
insert into @table values ('02/27/2006')
insert into @table values ('02/28/2006')
select min(x.Date_Field) StartDate, x.EndDate
from (select t.Date_Field, min(e.Date_Field) EndDate
from @table t
inner join (select t1.*
from @table t1
left outer join @table t2
on t1.ID_Val + 1 = t2.ID_Val and dateadd(dd, 1, t1.Date_Field) = t2.Date_Field
where t2.Date_Field is NULL) e
on t.Date_Field <= e.Date_Field
group by t.Date_Field) x
where x.Date_Field <> x.EndDate
group by x.EndDate
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply