Save average of value from first table into second table and delete the data in first table after the saving

Question

1.00/5 (1 vote)

See more:

hi...
I have two tables and I want save value from senser when the sensor value>0 in first table and when senser value=0 I want calculate the average sensor value in first table and save the resulat in second table and delete the data in first table in vb.net

What I have tried:

hi...
I have two tables and I want save  value from senser when the sensor value>0 in first table and when senser value=0 I want calculate the average sensor value in first table and save the resulat in second table and delete the data in first table in vb.net

Posted 5-Dec-18 8:00am

Member 14044492

Updated 7-Dec-18 23:40pm

Add a Solution

Comments

MadMyche 5-Dec-18 14:11pm

SQL Server?

Member 14044492 5-Dec-18 14:51pm

yes with sql server

Member 14044492 5-Dec-18 14:52pm

this my code

Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick

If s1.IsOpen = False Then

s1.PortName = "COM4"
s1.BaudRate = 9600
s1.Open()

Dim reading As String
reading = s1.ReadLine()

'lb_case.ForeColor = Color.Green
'lb_case.Text = "connected"

txt_read.Text = Convert.ToString(reading)
If txt_read.Text <> "" Then
Select Case Val(txt_read.Text)

Case 0.0
''''فحص جدول القراءات

Comm = New SqlClient.SqlCommand("SELECT * FROM first_tab", con)
SQLAdap.SelectCommand = Comm
comBild = New SqlClient.SqlCommandBuilder(SQLAdap)

SQLAdap.Fill(ds, "ds_used1")

If ds.Tables("ds_used1").Rows.Count > 0 Then
'حساب معدل الوقود الصاعد
Comm = New SqlClient.SqlCommand("select AVG (reading) from first_tab ", con)

SQLAdap.SelectCommand = Comm
comBild = New SqlClient.SqlCommandBuilder(SQLAdap)
SQLAdap.Fill(ds, "ds_avup")

Dim av_fuel_up As Double
av_fuel_up = ds.Tables("ds_avup").Rows(0).Item(0)

'حساب تاريخ التشغيل
Comm = New SqlClient.SqlCommand("select read_date from first_tab where id=1", con)

SQLAdap.SelectCommand = Comm
comBild = New SqlClient.SqlCommandBuilder(SQLAdap)
SQLAdap.Fill(ds, "ds_read_date")

Dim work_date As Date
work_date = ds.Tables("ds_read_date").Rows(0).Item(0)

'حساب مدة التشغيل
Comm = New SqlClient.SqlCommand("select DATEDIFF(s,(select read_date from first_tab where id=1),(select read_date from first_tab where id=(select MAX(id)from first_tab)))", con)

SQLAdap.SelectCommand = Comm
comBild = New SqlClient.SqlCommandBuilder(SQLAdap)
SQLAdap.Fill(ds, "ds_working_period")

Dim working_period As Integer
working_period = ds.Tables("ds_working_period").Rows(0).Item(0)

'اضافة البيانات الى جدول التشغيل
Comm = New SqlClient.SqlCommand("SELECT * FROM second_table", con)
SQLAdap.SelectCommand = Comm
comBild = New SqlClient.SqlCommandBuilder(SQLAdap)
SQLAdap.Fill(ds, "ds_working")
Dim dr2 As DataRow

dr2 = ds.Tables("ds_working").NewRow

dr2.Item("working_date") = work_date
dr2.Item("read_av") = av_fuel_up
dr2.Item("period") = working_period

ds.Tables("ds_working").Rows.Add(dr2)

comBild.GetUpdateCommand()
SQLAdap.Update(ds, "ds_working")
con.Close()
'تصفير جدول الاستخدام

Dim cmd As New SqlClient.SqlCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "truncate table first_tab"
cmd.Connection = con

con.Open()
cmd.ExecuteNonQuery()

con.Close()
Else

MadMyche 5-Dec-18 17:22pm

It would be best to use the "Improve Question" widget and paste this in yur ofirinal post.

Member 14044492 7-Dec-18 14:01pm

thank you very much ...
can you write vb.net cod in timer event to work your sql code which send to me

MadMyche 7-Dec-18 14:12pm

I would rather not for a few reasons:
1. My opinion is that this is a database task, and that your application should call the procedure- which contains multiple SQL statements
2. The code will need to be modified to your database schema, which I do not know.
3. I feel you would be better off if you learned how to do Stored Procedures yourself. It may take more time to write them and incorporate them, but when written well they almost always outperform any application-side routine or ORM.

Member 14044492 8-Dec-18 4:01am

thnk you very much....
I have successfully solved the problem that I have encountered and I thank you for helping me solve it

2 solutions

Solution 2

CREATE TABLE dbo.Table1 (
	ndx         INT IDENTITY(1,1) NOT NULL,
	SensorID    INT,
	SensorTime  DATETIME,
	SensorValue Decimal,
	CONSTRAINT PK_Table1 PRIMARY KEY ([SensorID], [ndx]) ON [PRIMARY]
) ON [PRIMARY]
GO




CREATE TABLE dbo.Table2 (
	ndx             INT IDENTITY(1,1) NOT NULL,
	SensorID        INT,
	SensorTimeStart DATETIME,
	SensorTimeEnd   DATETIME,
        period          Int,
	SensorValue     Decimal,
	CONSTRAINT PK_Table2 PRIMARY KEY ([SensorID], [ndx]) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE dbo.Sensor_LogValue (
@SensorID     INT,
	@SensorValue  Decimal 
) AS 
BEGIN
	IF (@SensorValue > 0) BEGIN
		INSERT Table1 ( SensorID,  SensorValue, SensorTime)
		VALUES (       @SensorID, @SensorValue, GetDate() )
	END; ELSE IF (@SensorValue = 0) BEGIN
		
		INSERT Table2 (	SensorID,	SensorTimeStart,	SensorTimeEnd , period,		SensorValue)
		SELECT			SensorID,	Min(SensorTime),	Max(SensorTime),DATEDIFF(s,Min(SensorTime),Max(SensorTime)),	Avg(SensorValue)
		FROM		Table1 
		WHERE	SensorID = @SensorID group by SensorID

		truncate table Table1

END; 
END

Posted 7-Dec-18 23:41pm

Member 14044492

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MadMyche · Accepted Answer · 2018-12-05T08:51:00

If you are doing this via an SQL Server; the best option would probably be to utilize a Stored Procedure to do the logic and subsequent actions.

Without more specific information from you, I came up with a concept which will meet your needs.
It has a few additional items:
- Ability to use multiple sensors
- Tracks the individual time while using Table1
- Aggregates the sensor times so that you will have the range (time span) in table2.

Never a finished project... you do need to come up with an action plan for sub-zero readings. You could add a "Reading Count" column to the second table as well

SQL

CREATE TABLE dbo.Table1 (
	ndx         INT IDENTITY(1,1) NOT NULL,
	SensorID    INT,
	SensorTime  DATETIME,
	SensorValue INT,
	CONSTRAINT PK_Table1 PRIMARY KEY ([SensorID], [ndx]) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE TABLE dbo.Table2 (
	ndx             INT IDENTITY(1,1) NOT NULL,
	SensorID        INT,
	SensorTimeStart DATETIME,
	SensorTimeEnd   DATETIME,
	SensorValue      INT,
	CONSTRAINT PK_Table2 PRIMARY KEY ([SensorID], [ndx]) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE dbo.Sensor_LogValue (
	@SensorID     INT,
	@SensorValue  INT 
) AS 
BEGIN
	IF (@SensorValue > 0) BEGIN
		INSERT Table1 ( SensorID,  SensorValue, SensorTime)
		VALUES (       @SensorID, @SensorValue, GetDate() )
	END; ELSE IF (@SensorValue = 0) BEGIN
		
		INSERT Table2 (	SensorID,	SensorTimeStart,	SensorTimeEnd,		SensorValue)
		SELECT			SensorID,	Min(SensorTime),	Max(SensorTime),	Avg(SensorValue)
		FROM		Table1 
		WHERE	SensorID = @SensorID

		DELETE Table1	WHERE SensorID = @SensorID

	END; ELSE BEGIN
		-- ToDo: What happens if negative value passed in
		Print 'Some error occurred......'
	END
END
GO

Save average of value from first table into second table and delete the data in first table after the saving

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Save average of value from first table into second table and delete the data in first table after the saving

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us