Link to home
Start Free TrialLog in
Avatar of websss
websssFlag for Kenya

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:
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')

Open in new window


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)
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Try this in a TEST environment first to check if is deleting all the expected records:
delete
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')

Open in new window

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])
I think you want to delete from tblCommonTrackingData
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)
;

Open in new window

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)
;

Open in new window

as always, have some way to restore if you have made an error (or I have)
Avatar of websss

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?
You're right Paul. That's why knee jerk reaction is not good :)
Avatar of websss

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?
Avatar of websss

ASKER

yes, I think this query is closer to what i need:

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)
;

Open in new window


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....

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

Open in new window

Avatar of websss

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.vpKDeviceID 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
If you want to leave one records then count must be required.

If above query is working you can use.
Avatar of websss

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
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])

Open in new window

@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.
Avatar of websss

ASKER

still  not right

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)

Open in new window

SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Oh... I  may still be wrong... sorry
Avatar of websss

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].ImeiNumber

1. Return all DeviceIDs
([wlt_tblDevices].ImeiNumber = tblCommonTrackingData.vpkDeviceId)
that have NOT reported to tblCommonTrackingData between '2016/07/01' and '2016/10/01'

2. for each of these ID's (tblCommonTrackingData.vpkDeviceId)
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:
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')

Open in new window

Avatar of websss

ASKER

Hi Vitor
Nearly there

i'm geting
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'inner'.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
websss, a feedback will be appreciated.
Cheers