January 14, 2011 at 10:28 am
For the query below I would like to retrieve the records where there is at least 1 hour or greater between the ReceivedDate and Created fields for any Device. Can someone assist with this query?
select HandheldDeviceActivityID,Device,UploadDevice, DownloadAppVersion,UploadAppVersion,REtry,Received,
UploadFileGuid,Processed,Created,ReceivedDate from HandHeldDeviceActivity
where companyid = 1 and created > '2010-12-1' order by Device, Created
Thanks!!! 😀
January 14, 2011 at 10:50 am
You can create another column as diff between the 2 column values.
In the where clause, you can spcify another condition, name of the new column > the value.
Regards,
Sushant
DBA
West Indies
Regards
Sushant Kumar
MCTS,MCP
January 14, 2011 at 11:26 am
Something like this?
select
HandheldDeviceActivityID,
Device,
UploadDevice,
DownloadAppVersion,
UploadAppVersion,
REtry,
Received,
UploadFileGuid,
Processed,
Created,
ReceivedDate
from
HandHeldDeviceActivity
where
companyid = 1
and created > '2010-12-1'
--Absolute of diff in minutes between these two time fields.
AND ABS( datediff( n, Created, ReceivedDate)) > 60
order by
Device, Created
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 14, 2011 at 11:35 am
All who responded to this thread,
A developer here gave me some insight to the query i needed to create. The comparison needed to be between the min record and the next available record and compare the two where the ReceivedDate was at least 1 hour or greater than the Created date of the next record. The query below satifies the criteria I needed. You all are awesome for responding with your suggestions to my question. Until next time my friends!!! 😀
select hh.HandheldDeviceActivityID, hh.Device, hh.UploadDevice, hh.DownloadAppVersion, hh.UploadAppVersion, hh.REtry,
hh.Received, hh.UploadFileGuid, hh.Processed, hh.Created, hh.ReceivedDate
from HandHeldDeviceActivity hh, HandHeldDeviceActivity hh2
WHERE hh.Device = hh2.Device
AND hh.HandHeldDeviceActivityId < hh2.HandheldDeviceActivityID
AND hh2.HandHeldDeviceActivityID = (SELECT MIN(HandHeldDeviceActivityID) FROM HandheldDeviceActivity hh3
WHERE hh3.HandHeldDeviceActivityID > hh.HandHeldDeviceActivityID AND hh3.Device = hh.Device)
AND DATEDIFF(hour, hh.ReceivedDate, hh2.Created) >= 1
and hh.companyid = 1 and hh.created > '2011-01-01'
order by hh.Device, hh.Created
January 14, 2011 at 12:59 pm
A developer here gave me some insight to the query i needed to create. The comparison needed to be between the min record and the next available record and compare the two where the ReceivedDate was at least 1 hour or greater than the Created date of the next record. The query below satifies the criteria I needed. You all are awesome for responding with your suggestions to my question. Until next time my friends!!!
select hh.HandheldDeviceActivityID, hh.Device, hh.UploadDevice, hh.DownloadAppVersion, hh.UploadAppVersion, hh.REtry,
hh.Received, hh.UploadFileGuid, hh.Processed, hh.Created, hh.ReceivedDate
from HandHeldDeviceActivity hh, HandHeldDeviceActivity hh2
WHERE hh.Device = hh2.Device
AND hh.HandHeldDeviceActivityId < hh2.HandheldDeviceActivityID
AND hh2.HandHeldDeviceActivityID = (SELECT MIN(HandHeldDeviceActivityID) FROM HandheldDeviceActivity hh3
WHERE hh3.HandHeldDeviceActivityID > hh.HandHeldDeviceActivityID AND hh3.Device = hh.Device)
AND DATEDIFF(hour, hh.ReceivedDate, hh2.Created) >= 1
and hh.companyid = 1 and hh.created > '2011-01-01'
order by hh.Device, hh.Created
Are you sure that this does what you want?
DATEDIFF(hour, hh.ReceivedDate, hh2.Created) >= 1
This criterion will be true even if the difference between the two times is as small as a few milliseconds, so long as the two dates straddle a change in hour. I think you really want one of these two criteria instead:
hh2.Created >= DATEADD(hour, 1, hh.ReceivedDate)
or
hh.ReceivedDate <= DATEADD(hour, -1, hh2.Created)
Also, have you considered the alternative of using ROW_NUMBER() to join sequential rows ordered by HandHeldDeviceActivityID? Note that I haven't tested the following query against any data.
;WITH cteSeq AS (
SELECT
HandheldDeviceActivityID,
Device,
UploadDevice,
DownloadAppVersion,
UploadAppVersion,
Retry,
Received,
UploadFileGuid,
Processed,
Created,
ReceivedDate,
ROW_NUMBER() OVER (PARTITION BY Device ORDER BY HandheldDeviceActivityID) AS rowId
FROM HandHeldDeviceActivity
WHERE (CompanyID = 1)
AND (Created > '2011-01-01T00:00:00')
)
SELECT
hh1.HandheldDeviceActivityID,
hh1.Device,
hh1.UploadDevice,
hh1.DownloadAppVersion,
hh1.UploadAppVersion,
hh1.Retry,
hh1.Received,
hh1.UploadFileGuid,
hh1.Processed,
hh1.Created,
hh1.ReceivedDate
FROM cteSeq hh1
JOIN cteSeq hh2 ON (hh1.Device = hh2.Device AND hh1.rowId = hh2.rowId - 1)
WHERE (hh2.Created >= DATEADD(hour, 1, hh1.ReceivedDate))
ORDER BY hh1.Device, hh1.Created
Edit: attempted to fix formatting of '>' character
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply