Hi , I have a database that records the users entrance to a building.The dates are recorded also .I have written some codes that to detect the period of dates that the person has entered.Lets say that a person named as jhon has entered the building on the days 02/08/2006 and 05/08/2006 and 11/08/2006 .Those dates are formated as dd/mm/yyyy . So that john has entered building for 3 times and the periods for the dates are one after another entrance is 3 days + 6 days =9 days .If you divide 9 by 3 we get the arithmetic average number 3 .So that we can say as john visits this building every 3 days and we can guess the next day that he may come.
I have coded this and works great .I will paste the codes to the end of my message.But a master programmer friend of mine has said that I should have get this result by without writing code , by just using sql database .But some kind of stored procedure I mustn't use.So that I thought it can be done by views in sqldb .By using sql server enterprise manager I tried to use views but I could not succees.
Can anyone guess this ?
Here are my vb codes ...
database data types :
entry_id : int , identity
user_id : int
movie : nvarchar (50)
dateenter : datetime
stored procedure that selects the data from db:
CREATE PROCEDURE veri_al
(
@.user_id int
)
AS
SELECT entry_id ,user_id, movie, dateenter from uye_aktiviteleri
where (user_id=@.user_id) ORDER BY entry_id ASC
GO
Code:
Dim conn As New SqlClient.SqlConnection
conn.ConnectionString = "data source=localhost;initial catalog=AFM;uid=nusret;pwd=araz"
Dim command As New SqlClient.SqlCommand
command.CommandText = "[veri_al]"
command.CommandType = CommandType.StoredProcedure
command.Connection = conn
command.Parameters.Add("@.user_id", SqlDbType.Int, 4).Value = Val(TextBox1.Text)
If Not IsNumeric(TextBox1.Text) Then
Exit Sub
End If
Try
Dim adapter As New SqlClient.SqlDataAdapter
adapter.SelectCommand = command
Dim ds As New DataSet
adapter.Fill(ds, "uyeler")
DataGrid1.DataSource = ds.Tables("uyeler")
Dim recordcount As Integer 'Found the recordcount
recordcount = ds.Tables("uyeler").Rows.Count
Dim mydatarow_ilk As DataRow
Dim mydatarow_son As DataRow
'in stored procedure I used ORDER BY entry_id ASC so that first datarow
'will always be the first visit date and the last record of the
'datarow will be the last visit date
mydatarow_first= ds.Tables("uyeler").Rows(0)
mydatarow_last = ds.Tables("uyeler").Rows(recordcount - 1)
Dim first_date As String
Dim last_date As String
first_date = mydatarow_first("dateenter")
last_date =mydatarow_last("dateenter")
Dim average_ As Integer
'What is the aveare of the visits
ortalama = DateDiff("d", first_date , last_date ) / recordcount
Label2.Text = "Member had visited for " & recordcount & "times"
Label3.Text = "by average he/she comes here " & ortalama & " day to another."
Dim last_time_visit As Integer
last_time_visit = DateDiff("d", last-date , DateTime.Today())
Label4.Text = "Until the last visit it had been" & last_time_visit & " days"
Label5.Text = "Guess for the next visit is" & DateAdd("d", average_ , last_date)
' MsgBox("average visits" & ortalama & " days" )
Catch z As Exception
MsgBox("error : " & z.Message())
Finally
MsgBox("Successfully calculated")
End Try
End Sub
SELECT d.user_id,COUNT(d.dateenter)as totalEnterTimes,AVG(CASEWHEN c.DiffsISNULLTHEN 0ELSE c.DiffsEND)as avgIntervalsFROM entrydateas dLEFTJOIN(SELECT a.user_id, a.dateenteras HighDate, b.dateenteras lowDate,
DATEDIFF(day, a.dateenter, b.dateenter)AS DiffsFROM(SELECTuser_id, dateenter,(selectcount(*)From entrydatewhereuser_id= T.user_idand dateenter<= T.dateenter)+ 1as Rank1
from entrydateas T) aINNERJOIN(SELECTuser_id, dateenter,(selectcount(*)From entrydatewhereuser_id= T1.user_idand dateenter<=T1.dateenter)as Rank2
from entrydateas T1) bON b.user_id=a.user_idand a.Rank1=b.Rank2) cON c.user_id= d.user_idAND c.lowDate=d.dateenter
GROUPBY d.user_id
--Let me know if you have question.
