Red: 2020

Thursday 26 November 2020

Running total in SQL

CREATE TABLE #Employee(Id INT IDENTITY,Name VARCHAR(100), Salary money)

INSERT INTO #Employee(Name, Salary)VALUES('Ram',50000)
INSERT INTO #Employee(Name, Salary)VALUES('Shyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Ghanshyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Sita',30000)
INSERT INTO #Employee(Name, Salary)VALUES('Gita',20000)
INSERT INTO #Employee(Name, Salary)VALUES('Pranita',1000)

SELECT * FROM #Employee

SELECT *, SUM(Salary) OVER(ORDER BY Id) AS 'Running Total' FROM #Employee

SELECT e1.*,(SELECT SUM(Salary) FROM #Employee e2 WHERE e1.id >= e2.id) AS 'Running Total'

FROM #Employee e1

--Note - For Running total, required column each row value must be unique. In OVER () clause or WHERE clause.

Wednesday 25 November 2020

Ranking functions in sql

CREATE TABLE #Employee(Name VARCHAR(100), Salary money)

INSERT INTO #Employee(Name, Salary)VALUES('Ram',50000)
INSERT INTO #Employee(Name, Salary)VALUES('Shyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Ghanshyam',40000)
INSERT INTO #Employee(Name, Salary)VALUES('Sita',30000)
INSERT INTO #Employee(Name, Salary)VALUES('Gita',20000)
INSERT INTO #Employee(Name, Salary)VALUES('Pranita',1000)

SELECT * FROM #Employee

SELECT
Salary
, ROW_NUMBER() OVER(ORDER BY Salary ASC) AS 'ROWNUMBER'
, RANK() OVER(ORDER BY Salary ASC) AS 'RANK'
, DENSE_RANK() OVER(ORDER BY Salary ASC) AS 'DENSERANK'
, ROW_NUMBER() OVER(PARTITION BY Salary ORDER BY Salary ASC) AS 'PARTITIONBY'
, NTILE(3) OVER(ORDER BY Salary) AS 'NTILE'

FROM #Employee

ROW_NUMBER() - Serial Number for each row.
RANK() - Rank each row and same record assign same rank and next rank number will be addition of the previous rank (same rank).
DENSE_RANK() - Rank each row and same record assign same rank and next rank number would be assigned the next rank value.
NTILE(3) - Groups the row based on number given.

Select 2nd highest salary in sql

CREATE TABLE #Employee(Name VARCHAR(100), Salary money)

--2nd highest

SELECT MAX(Salary) FROM #Employee WHERE Salary < (SELECT MAX(Salary) FROM #Employee)

--N number highest using subquery
--Change highlighted color number to get the N number of highest
SELECT TOP 1 *
FROM (
SELECT DISTINCT TOP 2 Salary
FROM #Employee
ORDER BY Salary DESC
) A
ORDER BY Salary ASC

--OR

SELECT DISTINCT Salary
FROM #Employee Emp1
WHERE 2 = (
SELECT COUNT(DISTINCT Emp2.Salary)
FROM #Employee Emp2
WHERE Emp2.Salary >= Emp1.Salary

)

--N number highest using CTE

--Change highlighted color number to get the N number of highest
;WITH RESULT
AS
(
SELECT Salary, DENSE_RANK() OVER(ORDER BY Salary DESC) AS 'DENSERANK'
FROM #Employee
)

SELECT TOP 1 * FROM RESULT WHERE DENSERANK = 2

--OFFSET

--Change highlighted color number to get the N number of highest
SELECT DISTINCT Salary
FROM #Employee
ORDER BY Salary DESC
OFFSET 2-1 ROWS

FETCH NEXT 1 ROWS ONLY

Wednesday 28 October 2020

How to Create and Read Values of Dynamic Controls in ASP.Net

Step 1: - Design.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Design.aspx.cs" Inherits="WebApplication1.Design" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title></title>
</head>
<body>
  <form id="form1" runat="server">
  <div>
  <asp:Panel ID="Panel1" runat="server"></asp:Panel>
  <asp:Button ID="Button1" runat="server" Text="Read Dynamic Control Value" OnClick="Button1_Click" />
  <br />
  <asp:GridView ID="GridView1" runat="server" GridLines="Both" CellPadding="4" ForeColor="#333333">
  <AlternatingRowStyle BackColor="White" />
  <EditRowStyle BackColor="#2461BF" />
  <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
  <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
  <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
  <RowStyle BackColor="#EFF3FB" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
  <SortedAscendingCellStyle BackColor="#F5F7FB" />
  <SortedAscendingHeaderStyle BackColor="#6D95E1" />
  <SortedDescendingCellStyle BackColor="#E9EBEF" />
  <SortedDescendingHeaderStyle BackColor="#4870BE" />
  </asp:GridView>
  </div>
  </form>
</body>
</html>

Step 2: - Design.aspx.cs

protected void Page_Init(object sender, EventArgs e)
{
GenerateDynamicControl(5);
}

protected void Button1_Click(object sender, EventArgs e)
{
  DataTable dt = new DataTable();
dt.Columns.Add("DynamicTextBoxValue", typeof(string));
  DataRow dr = null;

  foreach (Control control in Panel1.Controls)
  {
        //Find table
  if (control is Table)
  {
  Table table = (Table)control;
  foreach (Control tableRow in table.Controls)
  {
  //Find table row
  if (tableRow is TableRow)
  {
  TableRow tRow = (TableRow)tableRow;
  foreach (Control tableCell in tRow.Controls)
  {
  //Find table cell
  if (tableCell is TableCell)
                        {
  TableCell tCell = (TableCell)tableCell;
  foreach (Control ctrl in tCell.Controls)
  {
  dr = dt.NewRow();
  //Find Label in cell
  if (ctrl is Label)
  {
  Label label = (Label)ctrl;
  }
  //Find TextBox in cell
  if (ctrl is TextBox)
  {
  TextBox textBox = (TextBox)ctrl;
dr["DynamicTextBoxValue"] = textBox.Text;
  dt.Rows.Add(dr);
  }
  }
  }
  }
  }
  }
  }
  }

  GridView1.DataSource = dt;
  GridView1.DataBind();
}

private void GenerateDynamicControl(int rowsCount)
{
  Table table = new Table();
table.ID = "Table1";
  //Page.Form.Controls.Add(table);
  Panel1.Controls.Add(table);

  for (int i = 0; i < rowsCount; i++)
  {
  TableRow row = new TableRow();

  TableCell cellLabel = new TableCell();
  Label lb = new Label();
lb.ID = "LabelRow_" + i;
lb.Text = "LabelRow_" + i;
  cellLabel.Controls.Add(lb);
  row.Cells.Add(cellLabel);

  TableCell cellTextBox = new TableCell();
  TextBox tb = new TextBox();
tb.ID = "TextBoxRow_" + i;
  tb.Text = i.ToString();
  cellTextBox.Controls.Add(tb);
  row.Cells.Add(cellTextBox);

  table.Rows.Add(row);
  }
}

Output