Date gaps - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Date gaps

Author

Topic

morphviper
Yak Posting Veteran

60 Posts

Posted - 2007-02-27 : 16:21:58

I need to look in an application database and see if and when any person did not log into the computer more than two days in a row in the last two weeks.

So this is looking at a datetime column and I only need users that have spent two or more days not logged in for the past 14 days.

Here is kind of what I did.

SELECT a.re_recnum,a.qu_ref,a.ob_date AS GapSeq
FROM #temp_table a
WHERE NOT EXISTS( SELECT * FROM #temp_table b
WHERE b.ob_date = DATEADD(day, DATEDIFF(day, 1, a.ob_date), 0)) and
a.ob_date > ( SELECT MIN( ob_date ) FROM #temp_table)
group by a.re_recnum,a.qu_ref,a.ob_date
order by re_recnum

but this returns the dates they logged in. not the count of days between.

Any help would be greatly appreciated.

Thank you.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-27 : 23:20:18

it would help if you provided the DDL for the tables involved...

www.elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 01:34:34

As seen here [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79221[/url]

quote:
Originally posted by Peso

Without some more sample data and your expected output based on the same sample data, it might be hard to find a acceptable solution.

Peter Larsson
Helsingborg, Sweden

morphviper
Yak Posting Veteran

60 Posts

Posted - 2007-02-28 : 09:03:01

Sorry, I didn't put this out before,
I thought maybe someone had seen something like this and have a quick answer and I could modify it to my needs.

But here it is

------
Create Table #My_table
(Re_recnum Int NULL,
ob_date Smalldatetime NULL,
L_Name Varchar(20) NULL,
F_Name Varchar(20) NULL,
Qu_Ref VarChar(20) NULL,
Ch_Graph INT NULL)

Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/16/2007 14:59','Johnson ','Andrew ','BB-BM1 ',1)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/19/2007 14:59','Johnson ','Andrew ','BB-BM1 ',1)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/27/2007 9:25','Johnson ','Andrew ','BB-BM1 ',1)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/22/2007 14:59','Johnson ','Andrew ','BB-BM1 ',1)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/24/2007 22:59','Johnson ','Andrew ','BB-BM1 ',1)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/24/2007 22:59','Johnson ','Andrew ','BB-BMF1 ',2)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/22/2007 14:59','Johnson ','Andrew ','BB-BMF1 ',1)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/27/2007 9:25','Johnson ','Andrew ','BB-BMF1 ',1)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/19/2007 14:59','Johnson ','Andrew ','BB-BMF1 ',2)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/16/2007 14:59','Johnson ','Andrew ','BB-BMF1 ',4)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/16/2007 14:59','Johnson ','Andrew ','BB-BMF2 ',3)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/19/2007 14:59','Johnson ','Andrew ','BB-BMF2 ',1)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/27/2007 9:25','Johnson ','Andrew ','BB-BMF2 ',3)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/24/2007 22:59','Johnson ','Andrew ','BB-BMF2 ',3)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/22/2007 14:59','Johnson ','Andrew ','BB-BMF2 ',3)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/22/2007 14:59','Johnson ','Andrew ','BB-BMF3 ',0)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/24/2007 22:59','Johnson ','Andrew ','BB-BMF3 ',0)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/27/2007 9:25','Johnson ','Andrew ','BB-BMF3 ',0)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/19/2007 14:59','Johnson ','Andrew ','BB-BMF3 ',0)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/16/2007 14:59','Johnson ','Andrew ','BB-BMF3 ',0)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/16/2007 14:59','Johnson ','Andrew ','BB-BMF4 ',0)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/19/2007 14:59','Johnson ','Andrew ','BB-BMF4 ',1)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/27/2007 9:25','Johnson ','Andrew ','BB-BMF4 ',1)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/24/2007 22:59','Johnson ','Andrew ','BB-BMF4 ',1)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/22/2007 14:59','Johnson ','Andrew ','BB-BMF4 ',0)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/22/2007 14:59','Johnson ','Andrew ','H1a ',0)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/27/2007 9:25','Johnson ','Andrew ','H1a ',0)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/24/2007 22:59','Johnson ','Andrew ','H1a ',1)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/19/2007 14:59','Johnson ','Andrew ','H1a ',1)
Insert into #My_Table(Re_Recnum,ob_date,L_Name,F_Name,Qu_ref,Ch_graph)values(170,'2/16/2007 14:59','Johnson ','Andrew ','H1a ',1)

What I would like the output to be is this.
Login ID, last Name, Maximum Gap between given days.
Re_recnum,L_Name,Max_Gap
170,Johnson,2

I am looking for the largest gap in days for this login no a sum of gaps. What is the largest. I have some gaps that are 1 day and some are two days and possibly three.
I only want to see the largest gap. So if it is three, then I only want to see three.
The Qu_ref is type of login and I am only concerned with Login BB-BMF1 with a Ch_graph >0 and <5.
The Kicker is that I need to make sure that BB-BM1 Ch_graph = 0 doesn't fill those gaps because this is remote login.
Unless BB-BM1 is greater than 2 days in a row. So I guess you could count BB-BM1 with a Ch_graph = 0 as a NULL or GAP.

I hope this all makes sense.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 09:32:32

Not really.

-- Prepare sample data
DECLARE	@Test TABLE
	(
		Re_recnum INT,
		ob_date SMALLDATETIME,
		L_Name VARCHAR(20),
		F_Name VARCHAR(20),
		Qu_Ref VARCHAR(20),
		Ch_Graph INT
	)

INSERT	@Test
SELECT	170, '2/16/2007 14:59', 'Johnson', 'Andrew', 'BB-BM1', 1 UNION ALL
SELECT	170, '2/19/2007 14:59', 'Johnson', 'Andrew', 'BB-BM1', 1 UNION ALL
SELECT	170, '2/27/2007 09:25', 'Johnson', 'Andrew', 'BB-BM1', 1 UNION ALL
SELECT	170, '2/22/2007 14:59', 'Johnson', 'Andrew', 'BB-BM1', 1 UNION ALL
SELECT	170, '2/24/2007 22:59', 'Johnson', 'Andrew', 'BB-BM1', 1 UNION ALL
SELECT	170, '2/24/2007 22:59', 'Johnson', 'Andrew', 'BB-BMF1', 2 UNION ALL
SELECT	170, '2/22/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF1', 1 UNION ALL
SELECT	170, '2/27/2007 09:25', 'Johnson', 'Andrew', 'BB-BMF1', 1 UNION ALL
SELECT	170, '2/19/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF1', 2 UNION ALL
SELECT	170, '2/16/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF1', 4 UNION ALL
SELECT	170, '2/16/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF2', 3 UNION ALL
SELECT	170, '2/19/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF2', 1 UNION ALL
SELECT	170, '2/27/2007 09:25', 'Johnson', 'Andrew', 'BB-BMF2', 3 UNION ALL
SELECT	170, '2/24/2007 22:59', 'Johnson', 'Andrew', 'BB-BMF2', 3 UNION ALL
SELECT	170, '2/22/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF2', 3 UNION ALL
SELECT	170, '2/22/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF3', 0 UNION ALL
SELECT	170, '2/24/2007 22:59', 'Johnson', 'Andrew', 'BB-BMF3', 0 UNION ALL
SELECT	170, '2/27/2007 09:25', 'Johnson', 'Andrew', 'BB-BMF3', 0 UNION ALL
SELECT	170, '2/19/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF3', 0 UNION ALL
SELECT	170, '2/16/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF3', 0 UNION ALL
SELECT	170, '2/16/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF4', 0 UNION ALL
SELECT	170, '2/19/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF4', 1 UNION ALL
SELECT	170, '2/27/2007 09:25', 'Johnson', 'Andrew', 'BB-BMF4', 1 UNION ALL
SELECT	170, '2/24/2007 22:59', 'Johnson', 'Andrew', 'BB-BMF4', 1 UNION ALL
SELECT	170, '2/22/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF4', 0 UNION ALL
SELECT	170, '2/22/2007 14:59', 'Johnson', 'Andrew', 'H1a', 0 UNION ALL
SELECT	170, '2/27/2007 09:25', 'Johnson', 'Andrew', 'H1a', 0 UNION ALL
SELECT	170, '2/24/2007 22:59', 'Johnson', 'Andrew', 'H1a', 1 UNION ALL
SELECT	170, '2/19/2007 14:59', 'Johnson', 'Andrew', 'H1a', 1

-- Show the expected output
SELECT		t1.re_recnum AS LoginID,
		t1.l_name AS LastName,
		t1.qu_ref,
		MIN(DATEDIFF(day, t1.ob_date, t2.ob_date)) AS Max_Gap
FROM		@Test AS t1
INNER JOIN	@Test AS t2 ON t2.re_recnum = t1.re_recnum AND t2.qu_ref = t1.qu_ref AND t2.ob_date > t1.ob_date
WHERE		t1.ob_date >= DATEADD(day, DATEDIFF(day, 13, CURRENT_TIMESTAMP), 0)
		AND t1.ob_date < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1)
		AND t2.ob_date >= DATEADD(day, DATEDIFF(day, 13, CURRENT_TIMESTAMP), 0)
		AND t2.ob_date < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1)
GROUP BY	t1.re_recnum,
		t1.l_name,
		t1.qu_ref
ORDER BY	t1.re_recnum

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 09:39:04

This is the output for the suggestion above

LoginID	LastName	qu_ref	Max_Gap
-------	---------------	-------	-------
170	Johnson		BB-BM1	2
170	Johnson		BB-BMF1	2
170	Johnson		BB-BMF2	2
170	Johnson		BB-BMF3	2
170	Johnson		BB-BMF4	2
170	Johnson		H1a	2

Peter Larsson
Helsingborg, Sweden

morphviper
Yak Posting Veteran

60 Posts

Posted - 2007-02-28 : 12:10:12

This works for small date gaps, but where I am having issues, is say if johnson hasn't logged in for 5 days and then logged in..

here is new data. I tried modifying your code but with no luck...

here is a new set of data that for BB-BMF1 type of log in there is a gap greater than 3.
and if there is a gap between the last login and now...

(this is a 14 day look back)
INSERT @Test
SELECT 170, '2/16/2007 14:59', 'Johnson', 'Andrew', 'BB-BM1', 1 UNION ALL
SELECT 170, '2/19/2007 14:59', 'Johnson', 'Andrew', 'BB-BM1', 1 UNION ALL
SELECT 170, '2/27/2007 09:25', 'Johnson', 'Andrew', 'BB-BM1', 1 UNION ALL
SELECT 170, '2/22/2007 14:59', 'Johnson', 'Andrew', 'BB-BM1', 1 UNION ALL
SELECT 170, '2/24/2007 22:59', 'Johnson', 'Andrew', 'BB-BM1', 1 UNION ALL
SELECT 170, '2/24/2007 22:59', 'Johnson', 'Andrew', 'BB-BMF1', 2 UNION ALL
SELECT 170, '2/22/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF1', 1 UNION ALL
SELECT 170, '2/27/2007 09:25', 'Johnson', 'Andrew', 'BB-BMF1', 1 UNION ALL
SELECT 170, '2/16/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF1', 4 UNION ALL
SELECT 170, '2/16/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF2', 3 UNION ALL
SELECT 170, '2/19/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF2', 1 UNION ALL
SELECT 170, '2/27/2007 09:25', 'Johnson', 'Andrew', 'BB-BMF2', 3 UNION ALL
SELECT 170, '2/24/2007 22:59', 'Johnson', 'Andrew', 'BB-BMF2', 3 UNION ALL
SELECT 170, '2/22/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF2', 3 UNION ALL
SELECT 170, '2/22/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF3', 0 UNION ALL
SELECT 170, '2/24/2007 22:59', 'Johnson', 'Andrew', 'BB-BMF3', 0 UNION ALL
SELECT 170, '2/27/2007 09:25', 'Johnson', 'Andrew', 'BB-BMF3', 0 UNION ALL
SELECT 170, '2/19/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF3', 0 UNION ALL
SELECT 170, '2/16/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF3', 0 UNION ALL
SELECT 170, '2/16/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF4', 0 UNION ALL
SELECT 170, '2/19/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF4', 1 UNION ALL
SELECT 170, '2/27/2007 09:25', 'Johnson', 'Andrew', 'BB-BMF4', 1 UNION ALL
SELECT 170, '2/24/2007 22:59', 'Johnson', 'Andrew', 'BB-BMF4', 1 UNION ALL
SELECT 170, '2/22/2007 14:59', 'Johnson', 'Andrew', 'BB-BMF4', 0 UNION ALL
SELECT 170, '2/22/2007 14:59', 'Johnson', 'Andrew', 'H1a', 0 UNION ALL
SELECT 170, '2/27/2007 09:25', 'Johnson', 'Andrew', 'H1a', 0 UNION ALL
SELECT 170, '2/24/2007 22:59', 'Johnson', 'Andrew', 'H1a', 1 UNION ALL
SELECT 170, '2/19/2007 14:59', 'Johnson', 'Andrew', 'H1a', 1

and your out put is exactly correct

morphviper
Yak Posting Veteran

60 Posts

Posted - 2007-02-28 : 16:51:10

I figure it out...

Thank you for all of your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources