Dateproblem

Hello
I'm trying to get data from a view and it's not going well, im trying to
"group" the rows abit so I dont end up with a million rows to display a date
range....
Hope this makes sense....
CREATE TABLE #Test (
Startdate datetime,
Enddate datetime,
Avalible char(1)
)
INSERT INTO #Test
(Startdate,Enddate,Avalible)
SELECT
'2006-01-31 13:00','2006-01-31 13:30','Y'
UNION SELECT
'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
'2006-01-31 14:00','2006-01-31 14:30','N'
UNION SELECT
'2006-01-31 14:30','2006-01-31 15:00','N'
UNION SELECT
'2006-01-31 15:00','2006-01-31 15:30','Y'
UNION SELECT
'2006-01-31 15:30','2006-01-31 16:00','Y'
UNION SELECT
'2006-01-31 16:00','2006-01-31 16:30','N'
UNION SELECT
'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
'2006-02-01 15:30','2006-02-01 16:00','N'
SELECT * FROM #Test
/*
Desired result:
StartDate EndDate
'2006-01-31 13:00' '2006-01-31 14:00'
'2006-01-31 15:00' '2006-01-31 16:00'
'2006-02-01 15:00' '2006-02-01 15:30'
*/
DROP TABLE #Testerr... use this DLL instead..
CREATE TABLE #Test (
ObjectID int,
Startdate datetime,
Enddate datetime,
Avalible char(1)
)
INSERT INTO #Test
(ObjectID,Startdate,Enddate,Avalible)
SELECT
1,'2006-01-31 13:00','2006-01-31 13:30','Y'
UNION SELECT
1,'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
1,'2006-01-31 14:00','2006-01-31 14:30','N'
UNION SELECT
1,'2006-01-31 14:30','2006-01-31 15:00','N'
UNION SELECT
1,'2006-01-31 15:00','2006-01-31 15:30','Y'
UNION SELECT
1,'2006-01-31 15:30','2006-01-31 16:00','Y'
UNION SELECT
1,'2006-01-31 16:00','2006-01-31 16:30','N'
UNION SELECT
1,'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
1,'2006-02-01 15:30','2006-02-01 16:00','N'
SELECT * FROM #Test
/*
Desired result:
StartDate EndDate
'2006-01-31 13:00' '2006-01-31 14:00'
'2006-01-31 15:00' '2006-01-31 16:00'
'2006-02-01 15:00' '2006-02-01 15:30'
*/
DROP TABLE #Test
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23ikIzF$KGHA.3100@.tk2msftngp13.phx.gbl...
> Hello
> I'm trying to get data from a view and it's not going well, im trying to
> "group" the rows abit so I dont end up with a million rows to display a
date
> range....
> Hope this makes sense....
>
> CREATE TABLE #Test (
> Startdate datetime,
> Enddate datetime,
> Avalible char(1)
> )
> INSERT INTO #Test
> (Startdate,Enddate,Avalible)
> SELECT
> '2006-01-31 13:00','2006-01-31 13:30','Y'
> UNION SELECT
> '2006-01-31 13:30','2006-01-31 14:00','Y'
> UNION SELECT
> '2006-01-31 14:00','2006-01-31 14:30','N'
> UNION SELECT
> '2006-01-31 14:30','2006-01-31 15:00','N'
> UNION SELECT
> '2006-01-31 15:00','2006-01-31 15:30','Y'
> UNION SELECT
> '2006-01-31 15:30','2006-01-31 16:00','Y'
> UNION SELECT
> '2006-01-31 16:00','2006-01-31 16:30','N'
> UNION SELECT
> '2006-02-01 15:00','2006-02-01 15:30','Y'
> UNION SELECT
> '2006-02-01 15:30','2006-02-01 16:00','N'
>
> SELECT * FROM #Test
> /*
> Desired result:
> StartDate EndDate
> '2006-01-31 13:00' '2006-01-31 14:00'
> '2006-01-31 15:00' '2006-01-31 16:00'
> '2006-02-01 15:00' '2006-02-01 15:30'
> */
> DROP TABLE #Test
>|||Lasse Edsvik wrote:

> I'm trying to get data from a view and it's not going well, im trying
> to "group" the rows abit so I dont end up with a million rows to
> display a date range....
> Hope this makes sense....
I once had the same problem and question asked: http://tinyurl.com/7gnst
HTH,
Stijn Verrept.|||Or this if it makes it more simple.... :S
CREATE TABLE #Test (
ObjectID int,
Wkday tinyint,
StartTime char(5),
EndTime char(5),
Avalible char(1)
)
INSERT INTO #Test
(ObjectID,Wkday,StartTime,EndTime,Avalib
le)
SELECT
1,1,'13:00','13:30','Y'
UNION SELECT
1,1,'13:30','14:00','Y'
UNION SELECT
1,1,'14:00','14:30','N'
UNION SELECT
1,1,'14:30','15:00','N'
UNION SELECT
1,1,'15:00','15:30','Y'
UNION SELECT
1,1,'15:30','16:00','Y'
UNION SELECT
1,1,'16:00','16:30','N'
UNION SELECT
1,2,'15:00','15:30','Y'
UNION SELECT
1,2,'15:30','16:00','N'
SELECT * FROM #Test
/*
Desired result:
ObjectID Wkday StartTime EndTime
1 1 '13:00' '14:00'
1 1 '15:00' '16:00'
1 2 '15:00' '15:30'
*/
DROP TABLE #Test
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23ikIzF$KGHA.3100@.tk2msftngp13.phx.gbl...
> Hello
> I'm trying to get data from a view and it's not going well, im trying to
> "group" the rows abit so I dont end up with a million rows to display a
date
> range....
> Hope this makes sense....
>
> CREATE TABLE #Test (
> Startdate datetime,
> Enddate datetime,
> Avalible char(1)
> )
> INSERT INTO #Test
> (Startdate,Enddate,Avalible)
> SELECT
> '2006-01-31 13:00','2006-01-31 13:30','Y'
> UNION SELECT
> '2006-01-31 13:30','2006-01-31 14:00','Y'
> UNION SELECT
> '2006-01-31 14:00','2006-01-31 14:30','N'
> UNION SELECT
> '2006-01-31 14:30','2006-01-31 15:00','N'
> UNION SELECT
> '2006-01-31 15:00','2006-01-31 15:30','Y'
> UNION SELECT
> '2006-01-31 15:30','2006-01-31 16:00','Y'
> UNION SELECT
> '2006-01-31 16:00','2006-01-31 16:30','N'
> UNION SELECT
> '2006-02-01 15:00','2006-02-01 15:30','Y'
> UNION SELECT
> '2006-02-01 15:30','2006-02-01 16:00','N'
>
> SELECT * FROM #Test
> /*
> Desired result:
> StartDate EndDate
> '2006-01-31 13:00' '2006-01-31 14:00'
> '2006-01-31 15:00' '2006-01-31 16:00'
> '2006-02-01 15:00' '2006-02-01 15:30'
> */
> DROP TABLE #Test
>|||Lasse,
select ObjectID,Startdate, coalesce(last_available, enddate) enddate
from
(
SELECT t.*,
(select max(startdate) FROM #Test t1
where t1.startdate<t.startdate and t1.available='Y') prev_available,
(select max(startdate) FROM #Test t1
where t1.startdate<t.startdate and t1.available='N')
prev_not_available,
(select max(enddate) FROM #Test t1
where t1.startdate>t.startdate and t1.available='Y'
and not exists(select 1 from #test t2
where t2.available='N' and t2.startdate between t.startdate and
t1.startdate)
) last_available
FROM #Test t
where available='Y'
) t
where prev_available<prev_not_available
or prev_available is null
ObjectID Startdate
enddate
-- ---
---
1 2006-01-31 13:00:00.000
2006-01-31 14:00:00.000
1 2006-01-31 15:00:00.000
2006-01-31 16:00:00.000
1 2006-02-01 15:00:00.000
2006-02-01 15:30:00.000
(3 row(s) affected)
Nice puzzle, thanks!|||hmm,
Server: Msg 207, Level 16, State 3, Line 30
Invalid column name 'available'.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1139324776.706400.210190@.f14g2000cwb.googlegroups.com...
> Lasse,
> select ObjectID,Startdate, coalesce(last_available, enddate) enddate
> from
> (
> SELECT t.*,
> (select max(startdate) FROM #Test t1
> where t1.startdate<t.startdate and t1.available='Y') prev_available,
> (select max(startdate) FROM #Test t1
> where t1.startdate<t.startdate and t1.available='N')
> prev_not_available,
> (select max(enddate) FROM #Test t1
> where t1.startdate>t.startdate and t1.available='Y'
> and not exists(select 1 from #test t2
> where t2.available='N' and t2.startdate between t.startdate and
> t1.startdate)
> ) last_available
> FROM #Test t
> where available='Y'
> ) t
> where prev_available<prev_not_available
> or prev_available is null
> ObjectID Startdate
> enddate
> -- ---
> ---
> 1 2006-01-31 13:00:00.000
> 2006-01-31 14:00:00.000
> 1 2006-01-31 15:00:00.000
> 2006-01-31 16:00:00.000
> 1 2006-02-01 15:00:00.000
> 2006-02-01 15:30:00.000
> (3 row(s) affected)
> Nice puzzle, thanks!
>|||yes the spell checker replaced Avalible with Available. I did not argue
with it ;)|||Alexander,
oh k :) You have any idea why ObjectID=2 doesnt show up?
CREATE TABLE #Test (
ObjectID int,
Startdate datetime,
Enddate datetime,
available char(1)
)
INSERT INTO #Test
(ObjectID,Startdate,Enddate,available)
SELECT
1,'2006-01-31 13:00','2006-01-31 13:30','Y'
UNION SELECT
1,'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
1,'2006-01-31 14:00','2006-01-31 14:30','N'
UNION SELECT
2,'2006-01-31 13:30','2006-01-31 14:00','Y'
UNION SELECT
2,'2006-01-31 14:00','2006-01-31 16:00','Y'
UNION SELECT
1,'2006-01-31 14:30','2006-01-31 15:00','N'
UNION SELECT
1,'2006-01-31 15:00','2006-01-31 15:30','Y'
UNION SELECT
1,'2006-01-31 15:30','2006-01-31 16:00','Y'
UNION SELECT
1,'2006-01-31 16:00','2006-01-31 16:30','N'
UNION SELECT
1,'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
1,'2006-02-01 15:30','2006-02-01 16:00','N'
UNION SELECT
1,'2006-02-01 15:00','2006-02-01 15:30','Y'
UNION SELECT
1,'2006-02-02 15:00','2006-02-02 15:30','Y'
UNION SELECT
1,'2006-02-02 16:00','2006-02-02 16:30','Y'
SELECT ObjectID,Startdate, COALESCE(last_available, Enddate) Enddate
FROM
(
SELECT t.*,
(SELECT MAX(startdate) FROM #Test t1
WHERE t1.startdate<t.startdate AND t1.available='Y') prev_available,
(SELECT MAX(startdate) FROM #Test t1
WHERE t1.startdate<t.startdate AND t1.available='N')
prev_not_available,
(SELECT MAX(enddate) FROM #Test t1
WHERE t1.startdate>t.startdate and t1.available='Y'
AND NOT EXISTS(SELECT 1 FROM #test t2
WHERE t2.Available='N' and t2.Startdate between t.Startdate and
t1.Startdate)
) last_available
FROM #Test t
WHERE Available='Y'
) t
WHERE prev_available<prev_not_available
OR prev_available IS NULL
ORDER BY Startdate
/*
Desired result:
StartDate EndDate
'2006-01-31 13:00' '2006-01-31 14:00'
'2006-01-31 15:00' '2006-01-31 16:00'
'2006-02-01 15:00' '2006-02-01 15:30'
*/
DROP TABLE #Test
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1139325716.672360.263040@.g47g2000cwa.googlegroups.com...
> yes the spell checker replaced Avalible with Available. I did not argue
> with it ;)
>|||because I did not think of ObjectId at all. It's easy to take care of
however:
SELECT ObjectID,Startdate, COALESCE(last_available, Enddate) Enddate
FROM
(
SELECT t.*,
(SELECT MAX(startdate) FROM #Test t1
WHERE t.ObjectID=t1.ObjectID and t1.startdate<t.startdate AND
t1.available='Y') prev_available,
(SELECT MAX(startdate) FROM #Test t1
WHERE t.ObjectID=t1.ObjectID and t1.startdate<t.startdate AND
t1.available='N')
prev_not_available,
(SELECT MAX(enddate) FROM #Test t1
WHERE t.ObjectID=t1.ObjectID and t1.startdate>t.startdate and
t1.available='Y'
AND NOT EXISTS(SELECT 1 FROM #test t2
WHERE t2.ObjectID=t1.ObjectID and t2.Available='N' and
t2.Startdate between t.Startdate and
t1.Startdate)
) last_available
FROM #Test t
WHERE Available='Y'
) t
WHERE prev_available<prev_not_available
OR prev_available IS NULL
ORDER BY Startdate
ObjectID Startdate
Enddate
-- ---
---
1 2006-01-31 13:00:00.000
2006-01-31 14:00:00.000
2 2006-01-31 13:30:00.000
2006-01-31 16:00:00.000
1 2006-01-31 15:00:00.000
2006-01-31 16:00:00.000
1 2006-02-01 15:00:00.000
2006-02-01 15:30:00.000
1 2006-02-02 15:00:00.000
2006-02-02 16:30:00.000
(5 row(s) affected)