Get the first set of (consecutive) day(s)

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

Get the first set of (consecutive) day(s)

Author

Topic

whoever
Starting Member

5 Posts

Posted - 2009-01-21 : 17:26:25

A very simple table with a list of ID, Date and Time(so there could be multiple entries for the same ID on the same day).

The task is, given an arbitrary starting day, find all IDs that have Date Entry(ies) on that day, and any consecutive days, if any (only the set of record(s), no need for any additional entries, consecutive or otherwise). In other words, the result is a list of IDs, some have one or more entries on the starting day, some may have additional entries on the following days, as long as the dates are consecutive. If you want to make it more challenging, if there are no entries on Sat and Sun, then Fri and Monday is considered consecutive.

Hope I describe the question clearly. Most of the answers I found on the net are doing counting via having/group, nothing really fit this particular situation.

I hope this could be done without cursor. Any help is really appreciated. Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-21 : 17:27:32

See http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.63"
N 56°04'39.26"

whoever
Starting Member

5 Posts

Posted - 2009-01-21 : 19:18:10

Sorry, new here, didn't pay attention. Here's the updated version, hope it comply with the requirement

CREATE TABLE Schedules (
UserID VARCHAR(50),
ScheduleDay DATETIME,
ScheduleTime DATETIME
)

INSERT INTO Schedules (UserID, ScheduleDay, ScheduleTime)
SELECT 'user1', '2008-1-7', '08:30' UNION ALL
SELECT 'user1', '2008-1-7', '14:20' UNION ALL
SELECT 'user1', '2008-1-8', '8:30' UNION ALL
SELECT 'user1', '2008-1-9', '8:30' UNION ALL
SELECT 'user1', '2008-1-9', '10:30' UNION ALL
SELECT 'user1', '2008-1-12', '8:30' UNION ALL
SELECT 'user1', '2008-1-13', '8:30' UNION ALL
SELECT 'user2', '2008-1-7', '8:30' UNION ALL
SELECT 'user2', '2008-1-9', '8:30' UNION ALL
SELECT 'user3', '2008-1-8', '8:30'

So, without the weekend thing, with starting day on 1/7, it should return
user1: 1/7, 1/7, 1/8, 1/9, 1/9
user2: 1/7
It's all users who have an schedule item on that day and any consecutive day thereon.

Consider the weekend as consecutive, it should return
user1: 1/7, 1/7, 1/8, 1/9, 1/9, 1/12, 1/13
user2: 1/7

Time is really irrelevant here, just to illustrate you can have multiple items on the same day.

Any hints? Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-22 : 01:24:11

If you are using SQL Server 2005, see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.63"
N 56°04'39.26"

whoever
Starting Member

5 Posts

Posted - 2009-01-22 : 10:32:10

My fault, shouldn't have taken a shortcut. The result should be:

user1 1/7
user1 1/7
user1 1/8
user1 1/9
user2 1/7

The key in this question is given an starting day, how to find all the records and their immediate consecutive days, not concatenate. All the examples I can find are using grouping and counting to find out how many consecutive days amongst all records, not returning the actual rows.

Sorry creating so much hassle, really appreciate your help. I know this can be done using cursor (or even two nested cursor depends on data), but wondering if set based solution might be better.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 10:45:19

wont this be enough?


SELECT UserID,ScheduleDay
FROM Table
ORDER BY ScheduleDay, ScheduleTime

whoever
Starting Member

5 Posts

Posted - 2009-01-22 : 16:29:39

No, I only need users with entry(ies) on 1/7 and the **immediate consecutive days**

So for user1, only 1/7, 1/8, 1/9 should return (12,13 if you consider weekend consecutive). User1 could have records on 1/20, 1/21, 1/22, etc. but I don't need those. Same reason user2 only returns 1/7 but not 1/9 because it's not consecutive to the starting day.

All the examples I can find are running stats and counts, but I need the actual rows because I need to make further actions, like mark those days done ...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-22 : 17:50:03

well this is kind of ugly but seems to work:


set nocount on
declare @Schedules table(
UserID VARCHAR(50),
ScheduleDay DATETIME,
ScheduleTime DATETIME
)

declare @st datetime
set @st = '2008-01-07'

INSERT INTO @Schedules (UserID, ScheduleDay, ScheduleTime)
SELECT 'user1', '2008-1-7', '08:30' UNION ALL
SELECT 'user1', '2008-1-7', '14:20' UNION ALL
SELECT 'user1', '2008-1-8', '8:30' UNION ALL
SELECT 'user1', '2008-1-9', '8:30' UNION ALL
SELECT 'user1', '2008-1-9', '10:30' UNION ALL
SELECT 'user1', '2008-1-12', '8:30' UNION ALL
SELECT 'user1', '2008-1-13', '8:30' UNION ALL
SELECT 'user2', '2008-1-7', '8:30' UNION ALL
SELECT 'user2', '2008-1-9', '8:30' UNION ALL
SELECT 'user3', '2008-1-8', '8:30'

;with cte
as
(
select UserID
       ,ScheduleDay
       ,ScheduleTime
       ,case when pd.prevday_exists=1 OR datediff(day,ScheduleDay,@st)=0 then 1 else 0 end Keep
from   @schedules s
outer apply (
       select max(1) [Prevday_exists]
       from   @schedules
       where  userid = s.userid
       and    scheduleDay = dateadd(day, -1, s.scheduleDay)
       ) pd
where  ScheduleDay >= @st
)
select a.userid
       ,a.scheduleDay
from cte a
outer apply (
       select top 1 
              scheduleDay
       from   cte
       where  userid = a.userid
       and    Keep = 0
       order by scheduleDay
              ,scheduleTime
       ) x
where  a.scheduleDay < x.scheduleDay
order by userid, scheduleDay, ScheduleTime

output:
userid                                             scheduleDay
-------------------------------------------------- -----------------------
user1                                              2008-01-07 00:00:00.000
user1                                              2008-01-07 00:00:00.000
user1                                              2008-01-08 00:00:00.000
user1                                              2008-01-09 00:00:00.000
user1                                              2008-01-09 00:00:00.000
user2                                              2008-01-07 00:00:00.000

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-23 : 01:40:00

Using same sample data as TG

;WITH Yak (UserID, ScheduleDay, ScheduleTime)
AS (
	SELECT	UserID,
		ScheduleDay,
		ScheduleTime
	FROM	@Schedules
	WHERE	ScheduleDay = @st

	UNION ALL

	SELECT		s.UserID,
			s.ScheduleDay,
			s.ScheduleTime
	FROM		@Schedules AS s
	INNER JOIN	Yak AS y ON y.UserID = s.UserID
	WHERE		s.ScheduleDay = DATEADD(DAY, 1, y.ScheduleDay)
)

SELECT		UserID,
		ScheduleDay
FROM		Yak
GROUP BY	UserID,
		ScheduleDay,
		ScheduleTime

E 12°55'05.63"
N 56°04'39.26"

whoever
Starting Member

5 Posts

Posted - 2009-01-26 : 14:32:36

You guys are amazing! Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources