add auto dates from my table data

Question

Hello

This Is my table data

I want result to be like this in pivot format

For Process 1 Its a start date
For Process 2 Add 2 days from Process 1
For Process 3 Add 1 days from Process 2
For Process 4 Add 3 days from Process 3
how to get this result
Thanking You

Answer 1

create table test1 (RefNo char(2),StartDate date)

insert into test1 values('A1','2023-11-01'),

('C1','2023-11-04'),

('D1','2023-11-02')

select RefNo,StartDate as Process1

,Dateadd(Day,2,StartDate) as Process2

,Dateadd(Day,2+1,StartDate) as Process3

,Dateadd(Day,2+1+3,StartDate) as Process4

from test1

drop table test1

Answer 2

Hi @RAVI

If you want to show it on your project, you can try the following code(Operate DataTable):

public void getdata()
{
    SqlConnection conn = new SqlConnection("Data Source=LocalHost;Integrated Security=SSPI;Initial Catalog=Test;");
    conn.Open();
    string Sql = "select * from dbo.Case1";
    SqlCommand sqlCommand = new SqlCommand(Sql, conn);
    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
    DataTable dt = new DataTable();
    sqlDataAdapter.Fill(dt);
    dt.Columns.Add("PROCESS1(Start Date)", Type.GetType("System.String"));
    for (int i = 0; i < dt.Rows.Count; i++) 
    {
        DateTime A = (DateTime)dt.Rows[i][1];
        
        dt.Rows[i][2] = A.ToString("dd - MMM - yy");
    }
    dt.Columns.Add("PROCESS2(ADD 2 DAYS)", Type.GetType("System.String"));
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        DateTime A = (DateTime)dt.Rows[i][1];
        A=A.AddDays(2);

        dt.Rows[i][3] = A.ToString("dd - MMM - yy");
    }
    dt.Columns.Add("PROCESS2(ADD 1 DAYS)", Type.GetType("System.String"));
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        DateTime A = (DateTime)dt.Rows[i][1];
        A=A.AddDays(3);

        dt.Rows[i][4] = A.ToString("dd - MMM - yy");
    }
    dt.Columns.Add("PROCESS2(ADD 3 DAYS)", Type.GetType("System.String"));
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        DateTime A = (DateTime)dt.Rows[i][1];
       A= A.AddDays(6);

        dt.Rows[i][5] = A.ToString("dd - MMM - yy");
    }
    dt.Columns.Remove("StartDate");
    GridView1.DataSource = dt;
    GridView1.DataBind();
    conn.Close();
}

asp:GridView ID="GridView1" HeaderStyle-ForeColor="Black" runat="server"  CssClass="table table-bordered" >
    <Columns>
       
    </Columns>
</asp:GridView>

Best regards,
Qi You

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.