CODEWITHSUNDEEP
sql-serversql-server-2008sql-delete
user5921883
user5921883

Reputation:

Delete entries of a specific time range

I want to delete from an existing table the entries that respond to a specific time range if they exist in this table, then calculate them and insert them to it. If they do not exist create them and just insert to the table. How is that possible to be done? Could anyone give an example of this? The table structure should be the following:

create table test_table 
(
   [Date] float
  ,[UserName] nvarchar(max)
  ,[SessionType] int
  ,[Duration] float
  ,[MessageCount] int
)

Upvotes: 0

Views: 1276

Answers (3)

user5921883
user5921883

Reputation:

I am updating my solution to this thank you all for help.

create mytable table 
(
   [Date] float
  ,[UserName] nvarchar(max)
  ,[SessionType] int
  ,[Duration] float
  ,[MessageCount] int
)
IF EXISTS (SELECT * FROM mytable WHERE [Date] >= 43082 AND [Date] < 43111)
BEGIN
DELETE FROM mytable WHERE [Date] >= 43082 AND [Date]< 43111
;WITH Data AS (
/*do the calculation of data*/
)
INSERT INTO mytable select * from Data
END
ELSE
BEGIN
;WITH Data AS (
/*do the calculation of data*/
)
INSERT INTO mytable select * from Data
END

Upvotes: 0

Nitesh Kumar
Nitesh Kumar

Reputation: 1774

You can do in steps like this

First store the set of records in time range in a temp table

SELECT * INTO tempTable FROM YourTable WHERE CONVERT(FLOAT, [Date]) BETWEEN 43100.3603763503 AND 43110.3603763503

Then delete the records from the table

DELETE FROM YourTable WHERE CONVERT(FLOAT, [Date]) BETWEEN 43100.3603763503 AND 43110.3603763503

Then do the calculations as per your requirement with the data available in tempTable and insert the data into your table

INSERT INTO YourTable
SELECT * FROM tempTable

Then drop the tempTable

DROP TABLE tempTable

Upvotes: 1

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

if you have a Column that Stores the DATE and TIME then you can just delete the records based on that.

Suppose I have a Column Called CreateDate on My table. Then I can Delete all records Created between 10.00 am and 11.00 Today by Just Giving

DELETE FROM MyTable WHERE 
CreateDate BETWEEN '2018-01-12 10:00:00.000' AND '2018-01-12 11:00:00.000'

Now Insert the values again using the Normal INSERT statement

Upvotes: 1

Related Questions