Find two dates in same column within 48 hours

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 2000 Forums

Transact-SQL (2000)

Find two dates in same column within 48 hours

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2010-09-01 : 05:37:12

Hi folks and thanks in advance for any help.

This is boggling me a bit. I have a table that is to do with fault callouts for a company. If there is a fault in a shop store, an Engineer is called out and this is recorded in the table.

I've been asked to identify when a callout for the same store has been made with 48 hours of the last one. This is to show if an engineer has not corrected the problem and has had to come back out to fix it. This will evaluate Engineers performance.

Here is sample data that contains the storeID, the callout dates and always different faultIDs


FAultID StoreID  Callout Date
627917	014222	2010-04-26 00:00:00.000
641874	014222	2010-06-28 00:00:00.000
1000278	014222	2010-06-28 16:35:00.000
1000301	014222	2010-07-01 17:08:00.000
1000395	014222	2010-07-05 12:00:00.000
1000322	014222	2010-07-06 07:00:00.000
1000398	014222	2010-07-06 08:44:00.000

As you can see, there are three sets of dates that are within 48 hours of each other:

FaultIDs 641874 and 1000278 are 16.35 hours from each other.
faultIDs 1000395, 1000322 are around 19 hours from each other.
Also, 1000322 and 1000398 are aound 1.44 hours from each other.

These five records would be shown with the callout notes to determine if an engineer has not corrected the fault properly and has had to come back out.

It should look like this



FAultID StoreID  Callout Date
641874	014222	2010-06-28 00:00:00.000
1000278	014222	2010-06-28 16:35:00.000
1000395	014222	2010-07-05 12:00:00.000
1000322	014222	2010-07-06 07:00:00.000
1000398	014222	2010-07-06 08:44:00.000

FaultID 627917 and 1000301 are not included in this report because they're outwith 48 hours of any other dates in the column for that particular store number.

I'm very unsure how to do grab only records that fit the above criteria. Is there anyone that can help me?

Many thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-01 : 08:56:10

And you are using SQL Server 2000?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-01 : 09:16:19

[code]DECLARE @Sample TABLE
(
FaultID INT NOT NULL,
StoreID INT NOT NULL,
CallOut SMALLDATETIME NOT NULL
)

INSERT @Sample
SELECT 627917, 14222, '2010-04-26 00:00' UNION ALL
SELECT 641874, 14222, '2010-06-28 00:00' UNION ALL
SELECT 1000278, 14222, '2010-06-28 16:35' UNION ALL
SELECT 1000301, 14222, '2010-07-01 17:08' UNION ALL
SELECT 1000395, 14222, '2010-07-05 12:00' UNION ALL
SELECT 1000322, 14222, '2010-07-06 07:00' UNION ALL
SELECT 1000398, 14222, '2010-07-06 08:44'

-- Solution here
SELECT DISTINCT x.FaultID,
x.StoreID,
x.CallOut
FROM (
SELECT FaultID,
StoreID,
CallOut,
DATEADD(HOUR, -48, CallOut) AS FromTime,
DATEADD(HOUR, 48, CallOut) AS ToTime
FROM @Sample
) AS s
INNER JOIN @Sample AS x ON x.StoreID = s.StoreID
WHERE x.CallOut BETWEEN s.FromTime AND s.ToTime
AND x.FaultID <> s.FaultID[/code]

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

cidr
Posting Yak Master

207 Posts

Posted - 2010-09-01 : 11:51:24

Hi Peso,

Thanks very much for your help. I found another way to do it on another forum, however, I think yours works slightly faster.

Thanks:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-01 : 14:47:13

Please post the other solution for comparison.

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

cidr
Posting Yak Master

207 Posts

Posted - 2010-09-02 : 04:27:08

[code]

CREATE TABLE #temp
(
faultID INT,
storeID VARCHAR(6),
calloutDate DATETIME
)

INSERT INTO #temp
SELECT 627917, '014222', '04/26/10'
UNION ALL
SELECT 641874, '014222','06/28/10'
UNION ALL
SELECT 1000278, '014222', '06/28/10 16:35'
UNION ALL
SELECT 1000307, '014222','07/01/10 17:08'
UNION ALL
SELECT 1000395, '014222','07/05/10 12:00'
UNION ALL
SELECT 1000322, '014222','07/06/10 7:00'
UNION ALL
SELECT 1000398, '014222','07/06/10 8:44'

SELECT * FROM #temp T
WHERE EXISTS (SELECT * FROM #temp T2
WHERE t.storeID = t2.storeID
AND t2.calloutDate > t.calloutDate
AND t2.calloutDate < DATEADD(hh,48,t.calloutDate))
union
SELECT * FROM #temp T
WHERE EXISTS (SELECT * FROM #temp T2
WHERE t.storeID = t2.storeID
AND t2.calloutDate < t.calloutDate
AND t2.calloutDate > DATEADD(hh,-48,t.calloutDate))
ORDER BY T.calloutDate

DROP TABLE #temp

[/code]

The query above touched 98 rows to get the answer, yours touched about 56. Apparently the one above (and yours I suspect since it uses inequality operater) is called a triangular join and is quite bad for the server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources