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
 Date sorter/grouper problem, HELP PLEASE!

Author  Topic 

Bill_C
Constraint Violating Yak Guru

299 Posts
Posted - 2008-12-02 : 02:19:28
Can anyone help with the logic (sql) for this please?

Task:-

1. A period of dates (Sdate - Edate ) is entered into the system and have to be grouped together by personID. So what we have is basically 4 fields PersonID, Sdate (the start for a session) and Edate (the end of that particular session) and another id field T_ID which is used to identify the person that runs that session, as this field can be used to add up all sessions for that person.

2. Problem:- These dates can sometimes overlap, as the person may be involved in more than 1 activity, so a Sdate may be 24/07/2008 with the Edate for that session as 27/07/2008, but within the system we find another entry for that same personID as Sdate = 26/07/2008, with Edate as 30/07/2008.

3. Now the total session time for each person has to be calculated whether the sessions overlap or not (a new session may just start to run after the previous one finishes, therefore we have 2 start and end dates making one continuous session)
All this has to be done in number of days per session, with a total for consecutive sessions (where they exist)

(Hope I'm making sense here!).

So what we have (diagramatically) is possibly:-

row1.
--PersonID 1---T_ID 1-
Sdate Edate
(12/05/2005) (16/05/2005)
row2.
--PersonID 1--T_ID 2-
Sdate Edate
(17/05/2005) (22/05/2005)
row3.
--PersonID 2--T_ID 1-
Sdate Edate
(18/05/2005) (24/05/2005)
row4.
--PersonID 2--T_ID 3-
Sdate Edate
(23/05/2005) (28/05/2005)

(I have put the dates in for clarity)

So as you can see, in record 2 the start date for the second session starts immediately after the end of session 1, so these dates for this personID are to be stated as continuous, so we can use the Sdate of the first part and the Edate of the second part to give us total number of days,
but in records 3 & 4 there is an overlap, we still have to add the total number of days for these two periods (sessions) as they are for the same personID, so the must be linked and total days added up.

Any help anyone?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-12-02 : 02:30:02
so what should be your expected output for above dataset?
Go to Top of Page

Bill_C
Constraint Violating Yak Guru

299 Posts
Posted - 2008-12-02 : 02:45:39
Days for each period and running session, much like you would get with using 'Datediff("d", Sdate,Edate)', so if there are two or more running (or overlapping) periods then the number of days for each period as one count, plus the total number of days for the whole lot for each person (which will differ if there are overlapping periods, so it just won't be a case of Sdate (of first period) to Edate (of last Period))
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-12-02 : 03:11:46
sorry didnt understand that, can you show output format as in format below

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

Bill_C
Constraint Violating Yak Guru

299 Posts
Posted - 2008-12-02 : 04:12:06
What I need to do is
(It may have to be done in a loop or something, but)
A session is a number of periods of time, it could be 1 period with a start date and end date, or it could be a number of periods which run into one whole session.

1. Get out all the periods for a person found in the table.
2. Find out which of those periods are consecutive or overlapping. (e.g. where Sdate of period2 <= Edate of period1)
so in this case all periods found would have to be sorted on date 'ASC'
3. Work out the number of days (length) for each of those periods. (e.g. Datediff("d", Sdate,Edate)
4. Add up all the days for consecutive or overlapping periods to give me a total number of days for that whole session of periods.
5. Loop for all periods found?


Something along the lines of:-

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'periodlengths')
BEGIN
DROP TABLE periodlengths
END
GO

CREATE TABLE periodlengths
(
[recno] int,
[PersId] int,
[periodlength] int,
[periodno] int,

)

Declare @sessionlength int -- number of days elapsed in a period
Declare @session_no int --counter to get the total periods for each session



--get person
Select PersonID from table1 a
--get all sessions for each person
Select PersonID,Sdate,Edate,T_ID from table1 b
Where a.PersonID = b.PersonID
Order by Sdate
@session_no = 0
Select PersonID,Sdate,Edate,T_ID from table1 c
Where c.PersonID = b.PersonID
and c.Sdate <= b.Edate
-- now work out length of each session
@sessionlength = Datediff("d",b.Sdate,c.Edate)
-- next save the value of @sessionlength into periodlengths table
@session_no = @session_no + 1
insert into periodlengths
c.PersonID, @sessionlength, @session_no

-- now I would have to loop somehow to get the next period for this person?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-12-02 : 04:45:26
did you check the link i posted?Not much use giving query. post some data as per instructions in link if you need help.
Go to Top of Page

Bill_C
Constraint Violating Yak Guru

299 Posts
Posted - 2008-12-02 : 09:05:53
Here is some more code, hope this helps:-



CREATE TABLE [Tbl1](
	[Sdate] [varchar](20) NULL,
	[Edate] [Varchar](20) NULL,
	[PersonID] [int] NULL,
	[T_ID] [int] NULL
)

GO
--delete tbl1 from tbl1

INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ('17/06/2005','24/06/2005',123,234)
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ('17/06/2005','24/06/2005',122,232)
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '11/06/2005','27/06/2005',125,235 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '09/06/2005','24/06/2005',127,234 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '11/06/2005','24/06/2005',128,238 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '17/06/2006','27/06/2006',129,234 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '22/06/2006','24/06/2006',143,237 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '26/06/2005','29/06/2005',163,234 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '28/06/2005','03/07/2005',163,235 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '23/06/2005','28/06/2005',143,232 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '17/06/2005','24/06/2005',124,234 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '23/06/2005','30/06/2005',125,231 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '24/06/2005','29/06/2005',123,238) 

go

Declare @sessionlength int -- number of days elapsed in a period
Declare @session_no int --counter to get the total periods for each session
Declare @pid int


--get person
Select PersonID from Tbl1 

	--get all sessions for each person
	Select PersonID,Sdate,Edate,T_ID from Tbl1 b
		Where PersonID = b.PersonID
		Order by Sdate
		--set @pid = PersonID
			--@session_no = 0
			Select PersonID,Sdate,Edate,T_ID from Tbl1 c
				Where c.PersonID = PersonID
				and c.Sdate <= Edate
					-- now work out length of each session
		set @sessionlength = Datediff("d",b.Sdate,c.Edate)

		-- next save the value of @sessionlength into periodlengths table
		--@session_no = @session_no + 1
		--insert into periodlengths
	print @pid + ', ' + @sessionlength + ', ' + @session_no
Go to Top of Page

Bill_C
Constraint Violating Yak Guru

299 Posts
Posted - 2008-12-02 : 09:42:59
Thinking more on it, would a CURSOR be a better method of extracting the data?
Seeing as i need to loop through to get all periods for each personid to build up a total session time?
Go to Top of Page

Bill_C
Constraint Violating Yak Guru

299 Posts
Posted - 2008-12-03 : 03:13:07
Update:

What I have so far by using a cursor is:-



CREATE TABLE [Tbl1](
	[Sdate] [varchar](20) NULL,
	[Edate] [Varchar](20) NULL,
	[PersonID] [int] NULL,
	[T_ID] [int] NULL
)

GO

INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ('17/06/2005','24/06/2005',123,234)
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ('17/06/2005','24/06/2005',122,232)
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '11/06/2005','27/06/2005',125,235 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '09/06/2005','24/06/2005',127,234 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '11/06/2005','24/06/2005',128,238 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '17/06/2006','27/06/2006',129,234 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '22/06/2006','24/06/2006',143,237 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '26/06/2005','29/06/2005',163,234 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '28/06/2005','03/07/2005',163,235 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '23/06/2005','28/06/2005',143,232 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '17/06/2005','24/06/2005',124,234 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '23/06/2005','30/06/2005',125,231 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '24/06/2005','29/06/2005',123,238) 
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '28/06/2005','03/07/2005',123,233) 
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '03/06/2005','10/06/2005',125,231 )
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '28/08/2005','03/09/2005',123,233) 
INSERT INTO [Tbl1]([Sdate],[Edate],[PersonID],[T_ID])
values ( '03/08/2005','10/08/2005',125,231 )

go


Declare @sessionlength int -- number of days elapsed in a period
Declare @sessionlengthtot int -- number of days elapsed in a period
Declare @session_no int --counter to get the total periods for each session

SET NOCOUNT ON

DECLARE @person_id int, @sdate_ nvarchar(20),@edate_ nvarchar(20),
@message varchar(100),@tid int, @product nvarchar(50),
@person_id2 int, @sdate_2 nvarchar(20),@edate_2 nvarchar(20),@tid2 int,
@sdate_3 datetime,@edate_3 datetime 

	PRINT '-------- Sessions Report --------'

DECLARE session_cursor CURSOR FOR 
	SELECT DISTINCT PersonID,Sdate,Edate,T_ID from Tbl1 
	  ORDER BY PersonID

		OPEN session_cursor

		 FETCH NEXT FROM session_cursor INTO @person_id, @sdate_,@edate_ ,@tid 
			set @sessionlengthtot = 0
		  WHILE @@FETCH_STATUS = 0
			BEGIN
				PRINT ' '
				SELECT @message = '----- periods and sessions for Person: ' + cast(@person_id as varchar(20))  
				PRINT @message

-- Declare an inner cursor based   
-- on person_id from the outer cursor.

		DECLARE period_cursor CURSOR FOR 
			SELECT pv.PersonID,pv.Sdate,pv.Edate,pv.T_ID FROM Tbl1 pv 
			WHERE pv.Sdate <= @edate_ AND pv.PersonID = @person_id     -- Variable values from the outer cursor
		     ORDER BY CONVERT(DATETIME,pv.Sdate, 103),pv.PersonID
			OPEN period_cursor
				FETCH NEXT FROM period_cursor INTO @person_id2, @sdate_2,@edate_2 ,@tid2

					set @sdate_3 = CONVERT(DATETIME,@sdate_2, 103)
					set @edate_3 = CONVERT(DATETIME,@edate_2, 103)
			-- todo - get the total sessionlength for all periods

					set @sessionlength = datediff("d",@sdate_3,@edate_3)


				IF @@FETCH_STATUS <> 0 
					PRINT '         <<None>>'     

				WHILE @@FETCH_STATUS = 0
					BEGIN

			SELECT @message = '         InDate: ' + @sdate_2 + ',  OutDate: ' + @edate_2 + ',  T_ID = ' + cast(@tid2 as varchar(20)) + ',  days = '	+ cast(@sessionlength as varchar(20))
					PRINT @message
					FETCH NEXT FROM period_cursor INTO @person_id2, @sdate_2,@edate_2 ,@tid2
					
					set @sdate_3 = CONVERT(DATETIME,@sdate_2, 103)
					set @edate_3 = CONVERT(DATETIME,@edate_2, 103)
					set @sessionlength = datediff("d",@sdate_3,@edate_3)

					END

					CLOSE period_cursor
					DEALLOCATE period_cursor

	-- Get the next PersonID.
		FETCH NEXT FROM session_cursor INTO @person_id, @sdate_,@edate_ ,@tid
			set @sessionlengthtot = 0
		END 
CLOSE session_cursor
DEALLOCATE session_cursor




Now what I need to do is add up a running total of periods based upon criteria 'WHERE pv.Sdate <= @edate_ AND pv.PersonID = @person_id'
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-12-03 : 03:17:41
See http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site