Help in looping check

Question

User1248258412 posted

I have a table with user_name and section_ent store in the table, 1 user can have many section_ent. Currently I able to select all section_ent by user and order by the datetime.

Can anyone guide me how can I do compare on each 2 row if the value is equal to certain value I needed (eg: section_ent row1 = secA; section_ent row2 = secA) then return count +1; else continue compare row2 with row3, row3 with row4, row4 with row5,.... and so on until all is check?

Sorry that I ready no idea how to do this, currently I able to select all the section_ent from database base on user and order by datetime already, I need suggestion to guide me on how can I do the checking on program. Appreciate for any help given.

Answer 1

User-271186128 posted

Hi aoshi_kh,

As for this issue, I suggest you could populate the result to a DataTable or DataSet by using the DataAdapter. Then you could using for loop to check the values. Like this:

            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[3] { new DataColumn("StuId"), new DataColumn("StuName"), new DataColumn("Course") });
            dt.Rows.Add(1001, "AAA", "English");
            dt.Rows.Add(1002, "AAA", "Math");
            dt.Rows.Add(1003, "CCC", "Math");
            dt.Rows.Add(1004, "CCC", "English");
            dt.Rows.Add(1005, "EEE", "English");

            int count=0;
            string name ="AAA";
            for (int i = 0; i < dt.Rows.Count - 1; i++)
            {
                if ((dt.Rows[i]["StuName"].ToString().Equals(name)) || dt.Rows[i + 1]["StuName"].ToString().Equals(name))
                {
                    count++;
                    break;
                }
                
            }
            Response.Write(count.ToString());

As for populating a DataSet from a DataAdapter, please refer to this link:

https://msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.110).aspx

Best Regards,
Dillion

Answer 2

User-625491883 posted

Hi,

please just use T-SQL to solve your issue. below is my testing data.

section_ent user_name datatime_column
secA aoshi_kh 2015-09-01 00:00:00.000
secA aoshi_kh 2015-09-02 00:00:00.000
secB aoshi_kh 2015-09-03 00:00:00.000
secB aoshi_kh 2015-09-04 00:00:00.000
secB aoshi_kh 2015-09-05 00:00:00.000
secC aoshi_kh 2015-09-06 00:00:00.000
secD aoshi_kh 2015-09-07 00:00:00.000
secE aoshi_kh 2015-09-08 00:00:00.000
secE aoshi_kh 2015-09-09 00:00:00.000
secE aoshi_kh 2015-09-10 00:00:00.000

source code is

select top 1 rowindexB as youroutput from
(select section_ent,user_name,ROW_NUMBER() OVER(ORDER BY datatime_column) as rowindexA from TestTable) A
inner join
(select section_ent,user_name,ROW_NUMBER() OVER(ORDER BY datatime_column) as rowindexB from TestTable) B
on A.rowindexA+1=B.rowindexB AND A.section_ent=B.section_ent
where A.section_ent =  'secB'-->can change any input

your SQL Server version have to be later than SQL Server 2005