Using parameter from one query in another query

Question

I have a sql table of dbo.individuals with 15 parametres. The table shall be bound from behind to a gridview, but before binding, 6 of the parametres shall be updated on basis of computed parametres from another table dbo.activities, which holds 10 parametres. Each individual has none or a variable amount of logged activities. The parametres for use in query 2 is a varchar(50) datatype.

I have tried two methods: 1: To JOIN the two tables in one query, but with that method I did not succeed to get the envisaged table of individuals established. 2: I have tried to load a dataset of individuals with query 1, used reader to loop through these and used the varchar (50) id in query 2 to load a dataset of activities that are looped through by use of another reader and analysed before the table is established. The final table shall be a mix of parametres from query 1 and query 2. However, I cannot manage the query 2 to function.

Private Sub BindData()

    ' define table
    Dim dta As New DataTable()
    dta.Columns.AddRange(New DataColumn() {
        New DataColumn("RecordID", GetType(Integer)),
        New DataColumn("tagID", GetType(String)),
        New DataColumn("animalID", GetType(String)),
        New DataColumn("calvingdate", GetType(String)),
        New DataColumn("24hoursallowancesup1", GetType(String)),
        New DataColumn("24hoursallowancesup2", GetType(String)),
        New DataColumn("accumulatedallowancesup1", GetType(Integer)),
        New DataColumn("accumulatedallowancesup2", GetType(Integer)),
        New DataColumn("visitswithin24hours1", GetType(Integer)),
        New DataColumn("visitswithin24hours2", GetType(Integer)),
        New DataColumn("totalvisits1", GetType(Integer)),
        New DataColumn("totalvisits2", GetType(Integer))
                        })
    Dim konr As String = " "

    Try
        Dim PrimaryConnection As String = ConfigurationManager.ConnectionStrings("PrimaryConnection").ToString()
        Dim sqlhk As String = "Select * FROM Individuals ORDER BY Individuals.tagID ASC"
        Dim cnhk As New SqlConnection(PrimaryConnection),
            cmdhk As New SqlCommand(sqlhk, cnhk)

        cnhk.Open()

        Dim readerhk As SqlDataReader = cmdhk.ExecuteReader

        While readerhk.Read()

            konr = readerhk("tagID")

            'define and reset for each loop
            Dim acc1 As Integer = 0
            Dim acc2 As Integer = 0
            Dim accperiod As String = 0
            Dim accumulatedsup1 As Integer = 0
            Dim accumulatedsup2 As Integer = 0
            Dim cowactive1 As String = "Y"
            Dim cowactive2 As String = "Y"
            Dim dayvisits1 As Integer = 0
            Dim dayvisits2 As Integer = 0
            Dim totalvisits1 As Integer = 0
            Dim totalvisits2 As Integer = 0
            Dim eatensup1day As Integer = 0
            Dim eatensup2day As Integer = 0
            Dim eatentotal1 As Integer = 0
            Dim eatentotal2 As Integer = 0

            Try
                Dim caConnection As String = ConfigurationManager.ConnectionStrings("PrimaryConnection").ToString()
                Dim sqlca As String = "Select Activities.[ID], Activities.[VisitTime], Activities.[GramSup1], Activities.[GramSup2], Activities.[Comment] FROM Activities WHERE Activities.[EarTag] = '" & konr & "' ORDER BY Activities.[EarTag] ASC"
                Dim cnca As New SqlConnection(caConnection),
                cmdca As New SqlCommand(sqlca, cnca)

                'cmdca.Parameters.AddWithValue("@konr", readerhk("tagID"))

                cnca.Open()

                Dim readerca As SqlDataReader = cmdca.ExecuteReader

                While readerca.Read()

                    Dim vti As DateTime = DateTime.Parse(readerca("VisitTime"))
                    Dim fsk As TimeSpan = Now() - vti
                    Dim def As Integer = fsk.TotalHours
                    'would logically not become negative, but for certainty, we ensure the figure is positive
                    If def < 0 Then
                        def = -def
                    Else
                        def = def
                    End If

                    'visits last 24h
                    If def <= 24 And (readerca("GramSup1") + readerca("GramSup2")) > 0 Then
                        dayvisits1 += 1
                        dayvisits2 += 1
                        eatensup1day += readerca("GramSup1")
                        eatensup2day += readerca("GramSup2")
                    End If
                    'visits in accumulationperiod
                    If def <= (24 * Session("accperiod")) And (readerca("GramSup1") + readerca("GramSup2")) > 0 Then
                        totalvisits1 += 1
                        totalvisits2 += 1
                        eatentotal1 += readerca("GramSup1")
                        eatentotal2 += readerca("GramSup2")
                    End If

                End While

                Dim vtik As DateTime = DateTime.Parse(readerhk("calvingdate"))
                Dim fskk As TimeSpan = Now() - vtik
                Dim defk As Integer = fskk.TotalHours
                If defk < 0 Then
                    defk = -defk
                Else
                    defk = defk
                End If

                If defk >= (Session("accperiod") * 24) Then
                    accperiod = (Session("accperiod") * 24)
                Else
                    accperiod = defk
                End If

                Dim start1 As DateTime = vtik.AddDays(Session("startday1"))
                Dim end1 As DateTime = vtik.AddDays(Session("endday1"))
                Dim start2 As DateTime = vtik.AddDays(Session("startday2"))
                Dim end2 As DateTime = vtik.AddDays(Session("endday2"))

                If DateTime.Parse(Now()) >= DateTime.Parse(start1) And DateTime.Parse(Now()) <= DateTime.Parse(end1) Then
                    accumulatedsup1 = accperiod / 24 * readerhk("24hoursallowancesup1") - eatentotal1
                Else
                    cowactive1 = "N"
                End If
                If DateTime.Parse(Now()) >= DateTime.Parse(start2) And DateTime.Parse(Now()) <= DateTime.Parse(end2) Then
                    accumulatedsup2 = accperiod / 24 * readerhk("24hoursallowancesup2") - eatentotal2
                Else
                    cowactive2 = "N"
                End If

                dta.Rows.Add(readerhk("Id"), readerhk("tagID"), readerhk("animalID"), readerhk("calvingdate"), readerhk("24hoursallowancesup1"), readerhk("24hoursallowancesup2"), If(cowactive1 = "N", Nothing, accumulatedsup1), If(cowactive2 = "N", Nothing, accumulatedsup2), If(cowactive1 = "N", Nothing, dayvisits1), If(cowactive2 = "N", Nothing, dayvisits2), eatentotal1, If(cowactive2 = "N", Nothing, totalvisits2))

                readerca.Close()
                readerca = Nothing
                cnca.Close()
                cnca = Nothing

            Catch ex As Exception
                Response.Write("Cow log data error: " & ex.Message)
            End Try
        End While
                    Catch ex As Exception
        Response.Write("Cow listing error:" & ex.Message)
    End Try

    '-- Declaring of a DataView to be used as DataSource for a second grid
    Dim dv As New DataView(dta) With {
        .Sort = "visitswithin24hours1 ASC, visitswithin24hours2 ASC"
    }

    ' BIND DATABASE WITH THE GRIDVIEW
    GridView1.DataSource = dv
    GridView1.DataBind()

End Sub

I want a table listing the id's of the individuals as column 1, four parameters more from the individuals table / query 1, and six parametres that are computed from activities table / query 2. I am amateur in this and the problem is that the six parametres that should be computed from activities table / query 2 all are zero (default vaues), and query 2 apparently not activated. I hope someone can help me out.

Sample data and data types for Table 1 / individuals:

• [Id] INT IDENTITY (1, 1) NOT NULL -123,
• [HerdCountryCows] INT NOT NULL - 278,
• [HerdNumberCows] INT NOT NULL - 29034,
• [SN_Cows] VARCHAR (16) NOT NULL - N67eF45G,
• [tagID] VARCHAR (50) NOT NULL - 208_0029034098564,
• [animalID] VARCHAR (50) NULL - Rose,
• [calvingdate] DATETIME2 (7) NOT NULL - 03012019 12:34:23,
• [24hoursallowancesup1] INT DEFAULT ((100)) NOT NULL - 100,
• [24hoursallowancesup2] INT DEFAULT ((100)) NOT NULL - 100,
• [accumulatedallowancesup1] INT DEFAULT ((0)) NOT NULL - 456,
• [accumulatedallowancesup2] INT DEFAULT ((0)) NOT NULL - 0,
• [visitswithin24hours1] INT DEFAULT ((0)) NOT NULL - 4,
• [visitswithin24hours2] INT DEFAULT ((0)) NOT NULL - 0,
• [totalvisits1] INT DEFAULT ((0)) NOT NULL - 12,
• [totalvisits2] INT DEFAULT ((0)) NOT NULL - 0,

Sample data and datatypes for table 2 / activities:

• [ID] INT IDENTITY (1, 1) NOT NULL - 4356,
• [EarTag] VARCHAR (50) NOT NULL - 208_0029034098564,
• [VisitTime] DATETIME2 (7) NOT NULL - 03012019 12:34:23,
• [GramSup1] INT NOT NULL - 23,
• [GramSup2] INT NOT NULL - 12,
• [Comment] VARCHAR (100) NULL - comment,
• [SlaveID] VARCHAR (50) NOT NULL - MSF0098,
• [Country] VARCHAR (50) NOT NULL - 278,
• [Herd] VARCHAR (50) NOT NULL - 29034,
• [SN_Log] VARCHAR (16) NULL - N67eF45G,
• [ActivityType] INT NULL - 7,

Answer 1

I think what you are looking for is "CROSS APPLY" the two tables:

somthing like:

Select *
FROM Individuals I CROSS APPLY
(
    Select min([VisitTime]) as first_visit, count(*) as Activities_cnt
    FROM Activities 
    WHERE Activities.[EarTag] = I.tagID
) as Activities_cum
ORDER BY Individuals.tagID ASC;

more infos: When should I use Cross Apply over Inner Join?