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.
| 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.
|
|
|
|
|
|
|
|
|
Find two dates in same column within 48 hours