websss
asked on
Delete data from a table
Hi, I have 2 tables which are essentially:
1 DeviceMaster
2 DeviceLog
I need to delete all but the latest record from the LOGs if the DEVICE hasn't reported any log data in the last 3 months
This bit of code returns the asset ID (IMEI number) for anything that hasn't reported in the last 3 months:
What I need to do is delete all this data from tblCommonTrackingData (apart from the last recorded ID for that asset (tblCommonTrackingData .ID)
OR event better, just use the 1 table tblCommonTrackingData and delete all but latest record if that device hasn't reported in the last 3 months (not sure how this would work though)
1 DeviceMaster
2 DeviceLog
I need to delete all but the latest record from the LOGs if the DEVICE hasn't reported any log data in the last 3 months
This bit of code returns the asset ID (IMEI number) for anything that hasn't reported in the last 3 months:
select ImeiNumber, [Name]
from [wlt_tblDevices] d
left join [dbo].[wlt_tblAssets] a on d.[ifk_AssignedAssetId] = a.Id
where not exists (select 1
from [tblCommonTrackingData] c
where d.ImeiNumber = c.vpkDeviceId
and c.dGPSDateTime between '2016/07/01' and '2016/10/01')
What I need to do is delete all this data from tblCommonTrackingData (apart from the last recorded ID for that asset (tblCommonTrackingData .ID)
OR event better, just use the 1 table tblCommonTrackingData and delete all but latest record if that device hasn't reported in the last 3 months (not sure how this would work though)
1 DeviceMaster = wlt_tblDevices
2 DeviceLog = tblCommonTrackingData
and you want to delete from 2 (the logs)
I would't use the query above (sorry Vitor, but I think that query deletes from 1 [the device master])
2 DeviceLog = tblCommonTrackingData
and you want to delete from 2 (the logs)
I would't use the query above (sorry Vitor, but I think that query deletes from 1 [the device master])
I think you want to delete from tblCommonTrackingData
Try this as a select first:
Then, if that is OK
Try this as a select first:
WITH CTE
AS (
SELECT
*
, ROW_NUMBER() OVER (PARTITION BY vpkDeviceId ORDER BY dGPSDateTime DESC) AS rn
, MAX(dGPSDateTime) OVER (PARTITION BY vpkDeviceId) AS max_dt
FROM tblCommonTrackingData c
)
SELECT *
FROM CTE
WHERE max_dt < '20160701'
OR (max_dt >= '20160701' AND rn > 1)
;
That should display the rows you would delete.Then, if that is OK
WITH CTE
AS (
SELECT
*
, ROW_NUMBER() OVER (PARTITION BY vpkDeviceId ORDER BY dGPSDateTime DESC) AS rn
, MAX(dGPSDateTime) OVER (PARTITION BY vpkDeviceId) AS max_dt
FROM tblCommonTrackingData
)
DELETE FROM CTE
WHERE max_dt < '20160701'
OR (max_dt >= '20160701' AND rn > 1)
;
as always, have some way to restore if you have made an error (or I have)
ASKER
Thanks Paul
Your query looks more like it as Vitors did delete from wrong table
However, when i'm looking at the SELECT, i'm seeing dates such as 2016-09-28 23:33:45.000
I should only be seeing dates below '20160701' right?
Your query looks more like it as Vitors did delete from wrong table
However, when i'm looking at the SELECT, i'm seeing dates such as 2016-09-28 23:33:45.000
I should only be seeing dates below '20160701' right?
You're right Paul. That's why knee jerk reaction is not good :)
ASKER
also, i think rn is just the ID of the record, so i'm not sure its will keep the 1 record (last record?)
Do you need more help on this?
ASKER
yes, I think this query is closer to what i need:
However, I dont believe it will keep at least 1 record in this table.
I think it will just SELECT (will alter to DELETE) ALL DATA for units that havent LOGGED in the last 3 months?
WITH CTE
AS (
SELECT
*
, ROW_NUMBER() OVER (PARTITION BY vpkDeviceId ORDER BY dGPSDateTime DESC) AS rn
, MAX(dGPSDateTime) OVER (PARTITION BY vpkDeviceId) AS max_dt
FROM tblCommonTrackingData c
)
SELECT *
FROM CTE
WHERE (max_dt <= '20160701' AND rn > 1)
;
However, I dont believe it will keep at least 1 record in this table.
I think it will just SELECT (will alter to DELETE) ALL DATA for units that havent LOGGED in the last 3 months?
Try this ..
A minor modification....
A minor modification....
WITH CTE AS
(
SELECT * FROM
(
SELECT
*
, ROW_NUMBER() OVER (PARTITION BY vpkDeviceId ORDER BY dGPSDateTime DESC) AS rn
, MAX(dGPSDateTime) OVER (PARTITION BY vpkDeviceId) AS max_dt
FROM tblCommonTrackingData c
)p
WHERE (p.max_dt <= '20161003')
)
DELETE FROM CTE
WHERE rn > 1
ASKER
Thanks
Actually, the more i look at it the more I think this could be wrong
I'm not sure i explained myself well enough
how about this
Delete all data from tblCommonTrackingData
If the tblCommonTrackingData.vpKD eviceID doesn't have any data in the last 3 months?
i.e.
Delete from tblCommonTrackingData
where not exists (select *
from [tblCommonTrackingData] c
where c.dGPSDateTime between '2016/07/01' and '2016/10/01')
--however, leave at least 1 record
Actually, the more i look at it the more I think this could be wrong
I'm not sure i explained myself well enough
how about this
Delete all data from tblCommonTrackingData
If the tblCommonTrackingData.vpKD
i.e.
Delete from tblCommonTrackingData
where not exists (select *
from [tblCommonTrackingData] c
where c.dGPSDateTime between '2016/07/01' and '2016/10/01')
--however, leave at least 1 record
If you want to leave one records then count must be required.
If above query is working you can use.
If above query is working you can use.
ASKER
Thanks,
However RN refers to row number and not count?
Or am i mistaken?
also your query doesn't seem to mention the 3 months ago thing
However RN refers to row number and not count?
Or am i mistaken?
also your query doesn't seem to mention the 3 months ago thing
Yes you are right it is row number , In that case we can leave the first row. :)
Try this version. It should work if dGPSDateTime stores date and time and not only date:
delete from [tblCommonTrackingData]
where dGPSDateTime between '2016/07/01' and '2016/10/01'
and dGPSDateTime < (SELECT MAX(dGPSDateTime) FROM [tblCommonTrackingData])
@Author - Now check the logic given by Vitor, In this case also we are deleting all values between the date range and Just leaving the maximum date from the dataset.
I hope it helps.
I hope it helps.
ASKER
still not right
I only want to delete DEVICEID data if the device hasn't reported in last 3 months
I only want to delete DEVICEID data if the device hasn't reported in last 3 months
It misses the joins:
delete from [tblCommonTrackingData]
inner join from [wlt_tblDevices] d
left join [dbo].[wlt_tblAssets] a on d.[ifk_AssignedAssetId] = a.Id
on d.ImeiNumber = [tblCommonTrackingData].vpkDeviceId
where [tblCommonTrackingData].dGPSDateTime between '2016/07/01' and '2016/10/01'
and [tblCommonTrackingData].dGPSDateTime < (SELECT MAX(dGPSDateTime)
FROM [tblCommonTrackingData] t2
WHERE [tblCommonTrackingData].vpkDeviceId = t2.vpkDeviceId)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oh... I may still be wrong... sorry
ASKER
Thanks all
Vitor, your script is returning data between '2016/07/01' and '2016/10/01'
which is wrong
We can ignore the tblAssets Table for now, please just focus on [wlt_tblDevices] & [tblCommonTrackingData]
What i need is something like this
First get all the Devices are in [wlt_tblDevices].ImeiNumbe r
1. Return all DeviceIDs
([wlt_tblDevices].ImeiNumbe r = tblCommonTrackingData.vpkD eviceId)
that have NOT reported to tblCommonTrackingData between '2016/07/01' and '2016/10/01'
2. for each of these ID's (tblCommonTrackingData.vpkD eviceId)
delete all data in tblCommonTrackingData
Vitor, your script is returning data between '2016/07/01' and '2016/10/01'
which is wrong
We can ignore the tblAssets Table for now, please just focus on [wlt_tblDevices] & [tblCommonTrackingData]
What i need is something like this
First get all the Devices are in [wlt_tblDevices].ImeiNumbe
1. Return all DeviceIDs
([wlt_tblDevices].ImeiNumbe
that have NOT reported to tblCommonTrackingData between '2016/07/01' and '2016/10/01'
2. for each of these ID's (tblCommonTrackingData.vpkD
delete all data in tblCommonTrackingData
It's hard to work with no sample data so we're trying to guess what your data should look alike.
If I understand well your requirements then check if this solves your problem:
If I understand well your requirements then check if this solves your problem:
delete from [tblCommonTrackingData]
inner join from [wlt_tblDevices] d
left join [dbo].[wlt_tblAssets] a on d.[ifk_AssignedAssetId] = a.Id
on d.ImeiNumber = [tblCommonTrackingData].vpkDeviceId
where [tblCommonTrackingData].dGPSDateTime < (SELECT MAX(dGPSDateTime)
FROM [tblCommonTrackingData] t2
WHERE [tblCommonTrackingData].vpkDeviceId = t2.vpkDeviceId
AND t2.dGPSDateTime <= '2016/07/01')
ASKER
Hi Vitor
Nearly there
i'm geting
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'inner'.
Nearly there
i'm geting
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'inner'.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
websss, a feedback will be appreciated.
Cheers
Cheers
Open in new window