Using loop at where condition in SQL View

See more:

ASM

I have to use view instead of the following query. But temp table DTLineInfo is used in the query at where condition. I can create simple query. But I can not use loop like DTLineInfo.
Please look at where condition of the queries strSQL=""; I am eagerly wait for the answer. please help me

private void LoadManpower()
    {
        DataTable DTLineInfo = new DataTable();
        DataTable DTLineWiseOT = new DataTable();
        DataTable DTLineWiseOT1H = new DataTable();

        string strLineName = string.Empty;
        string strLocation = string.Empty;

        //CheckBox cb = (CheckBox)grdviewDailyManpowerProduction.HeaderRow.FindControl("chkSelect");

        for (int i = 0; i < grdviewDailyProduction.Rows.Count; i++)
        {
            CheckBox chkSelect = (CheckBox)grdviewDailyProduction.Rows[i].Cells[0].FindControl("chkSelect");
            if (chkSelect != null)
            {
                if (chkSelect.Checked)
                {
                    strLineName = grdviewDailyProduction.Rows[i].Cells[2].Text.Trim();
                    strLocation = grdviewDailyProduction.Rows[i].Cells[6].Text.Trim();
                }
            }
        }
        strSQL = "select distinct LineCode, LineName, Designation, DesigCode, Location, DayFlag from tblManpowerCollection where LineName='" + strLineName + "' and Location='" + strLocation + "' and ATTdate='" + dptProductionDate.Text.Trim() + "'";

        ConManager.DBConnection("TERMSPDN");
        ConManager.OpenDataTableThroughAdapter(strSQL, out DTLineInfo, true);
        ConManager.CloseConnection();
        DTLineInfo.TableName = "DTLineInfo";

        if (DTLineInfo.Rows.Count > 0)
        {
            for (int j = 0; j < DTLineInfo.Rows.Count; j++)
            {
                string strSQL1 = "";
                if (DTLineInfo.Rows[j]["DayFlag"].ToString().Trim() == "HD")
                {
                    strSQL1 = "select distinct" + _
                       " (select count(ATTdate) from tblManpowerCollection where ATTDate='" + dptProductionDate.Text.Trim() + "' and lineName='" + DTLineInfo.Rows[j]["LineName"].ToString() + "'and Location='" + DTLineInfo.Rows[j]["Location"].ToString() + "' ) as '1H'," + _
                       " (convert(Int,(select count(ATTdate) from tblManpowerCollection where ATTDate='" + dptProductionDate.Text.Trim() + "' and TotalOTHour>=9 and lineName='" + DTLineInfo.Rows[j]["LineName"].ToString() + "'and Location='" + DTLineInfo.Rows[j]["Location"].ToString() + "' ))) as '2H'," + _
                       " (convert(Int,(select count(ATTdate) from dbo.tblManpowerCollection where TotalOTHour >=10 and ATTdate='" + dptProductionDate.Text.Trim() + "' and lineName='" + DTLineInfo.Rows[j]["LineName"].ToString() + "'and Location='" + DTLineInfo.Rows[j]["Location"].ToString() + "'))) as '3H'," + _
                       " (convert(Int,(select count(ATTdate) from dbo.tblManpowerCollection where TotalOTHour>=11 and ATTdate='" + dptProductionDate.Text.Trim() + "' and lineName='" + DTLineInfo.Rows[j]["LineName"].ToString() + "'and Location='" + DTLineInfo.Rows[j]["Location"].ToString() + "' ))) as '4H'," + _
                       " (Convert(Int,(select count(ATTdate) from dbo.tblManpowerCollection where TotalOTHour =12 and ATTdate='" + dptProductionDate.Text.Trim() + "'and lineName='" + DTLineInfo.Rows[j]["LineName"].ToString() + "'and Location='" + DTLineInfo.Rows[j]["Location"].ToString() + "' ))) as '5H'," + _
                       " (convert(INT,(select count(ATTdate) from dbo.tblManpowerCollection where TotalOTHour >12 and ATTdate='" + dptProductionDate.Text.Trim() + "' and lineName='" + DTLineInfo.Rows[j]["LineName"].ToString() + "'and Location='" + DTLineInfo.Rows[j]["Location"].ToString() + "' ))) as '6H'," + _
                       " ATTdate from  tblManpowerCollection where  ATTdate= '" + dptProductionDate.Text.Trim() + "'";
                }
            }
        }
    }