Calculate Difference between current and previous rows…..CTE and Row_Number() rocks!

Ever wanted to calculate difference between current row’s column to previous row’s column? It has several usages like calculating trends, monitoring changes etc. Several TSQL solutions are available for this problem but Common Table Expressions and Row_Number function in SQL Server 2005 provides an excellent way to solve this. By using CTE to calculate the difference between current and last row gives you excellent performance and can be used for huge tables. Following is the TSQL code to calculate the difference between CurrentPointValue to PreviousPointValue.

—Declare a test Table variable for our sample
Declare @m_TestTable table
(
DateRecorded datetime,
PointValue int
)

—Insert sample data
Insert into @m_TestTable Values (dateadd(day,1,GetDate()),150)
Insert into @m_TestTable Values (dateadd(day,2,GetDate()),350)
Insert into @m_TestTable Values (dateadd(day,3,GetDate()),500)
Insert into @m_TestTable Values (dateadd(day,4,GetDate()),100)
Insert into @m_TestTable Values (dateadd(day,5,GetDate()),150);

—Create CTE
With tblDifference as
(
Select Row_Number() OVER (Order by DateRecorded) as RowNumber,DateRecorded,PointValue from @m_TestTable
)

—Actual Query
Select convert(varchar, Cur.DateRecorded,103) as CurrentDay, convert(varchar, Prv.DateRecorded,103) as PreviousDay,Cur.PointValue as CurrentValue,Prv.PointValue as PreviousValue,Cur.PointValue-Prv.PointValue as Difference from

tblDifference Cur Left Outer Join tblDifference Prv
On Cur.RowNumber=Prv.RowNumber+1
Order by Cur.DateRecorded

C#.Net - GetDssDuration

public static TimeSpan GetDssDuration(string fileName)

        {

            const int durationOffset = 62;

            const int durationLength = 6;

            const int headerLength = durationOffset + durationLength;

            using (var fileStream = File.OpenRead(fileName))

            {

                var fileHeader = new byte[headerLength + 1];

                fileStream.Read(fileHeader, 0, headerLength);

                var hoursData = new byte[2];

                var minutesData = new byte[2];

                var secondsData = new byte[2];

                Array.Copy(fileHeader, durationOffset, hoursData, 0, 2);

                Array.Copy(fileHeader, durationOffset + 2, minutesData, 0, 2);

                Array.Copy(fileHeader, durationOffset + 4, secondsData, 0, 2);

                return new TimeSpan(int.Parse(Encoding.ASCII.GetString(hoursData)), int.Parse(Encoding.ASCII.GetString(minutesData)), int.Parse(Encoding.ASCII.GetString(secondsData)));

            }

        }

Higlighing maximum value in DataGridView

  #region " Higlighing maximum value "

                    DataGridViewRow dgvRow = (DataGridViewRow)dgvMemberAccuracy.Rows[dgvMemberAccuracy.Rows.Count - 1];

                    foreach (DataGridViewCell dgvCell in dgvRow.Cells)

                    {

                        if (dgvCell.OwningColumn.Index > 2 )

                        {

                            object oTotal = dgvMemberAccuracy.Rows.Cast<DataGridViewRow>()

                                                 .Where(r => (r.Cells[dgvCell.OwningColumn.Index].Value != DBNull.Value && r.Cells[dgvCell.OwningColumn.Index].Value != null && r.Index != dgvRow.Index))

                                                 .Max(t => t.Cells[dgvCell.OwningColumn.Index].Value);

                            if (oTotal != null)

                            {

                                decimal dTotal = Convert.ToDecimal(oTotal);

                                Array arrTemp = (dgvMemberAccuracy.Rows.Cast<DataGridViewRow>()

                                            .Where(r => (r.Cells[dgvCell.OwningColumn.Index].Value != DBNull.Value) && r.Index != dgvRow.Index && r.Cells[dgvCell.OwningColumn.Index].Value.ToString() == dTotal.ToString())

                                            .ToArray());

                                foreach (DataGridViewRow dgr in arrTemp)

                                {

                                    dgvMemberAccuracy.Rows[dgr.Index].Cells[dgvCell.OwningColumn.Index].Style.BackColor = Color.Green;

                                }

                            }

                        }

                    }

                    #endregion " Higlighing maximum value "

Add a unique constraint of a sql table as foreign key reference to an another sql table

CREATE TABLE TableB (
    PK1 INT NOT NULL,
    PK2 INT NOT NULL,
    SomeData VARCHAR(1000),

    CONSTRAINT PK_TableB PRIMARY KEY CLUSTERED (PK1, PK2)
)

CREATE TABLE TableA (
    PK INT NOT NULL,
    FK1 INT NOT NULL,  -- Or NULL, if you''d rather.
    FK2 INT NOT NULL,
    CONSTRAINT PK_TableA PRIMARY KEY CLUSTERED (PK),
    CONSTRAINT FK_TableA_FK1FK2 FOREIGN KEY (FK1, FK2) REFERENCES TableB (PK1, PK2),
    CONSTRAINT Cons2cols UNIQUE(FK1, FK2)
)

C# Text box only allow two decimal points and numbers using text box keypress event

private void txtScore_KeyPress(object sender, KeyPressEventArgs e)

        {

            if ((e.KeyChar < '0' || e.KeyChar > '9') && (e.KeyChar != '\b') && (e.KeyChar != '.'))

            {

                e.Handled = true;

            }

            else

            {

                e.Handled = false;

            }

            if (Char.IsControl(e.KeyChar))

            {

                e.Handled = false;

            }

            else if (Char.IsNumber(e.KeyChar) || e.KeyChar == '.')

            {

                TextBox tb = sender as TextBox;

                int cursorPosLeft = tb.SelectionStart;

                int cursorPosRight = tb.SelectionStart + tb.SelectionLength;

                string result = tb.Text.Substring(0, cursorPosLeft) + e.KeyChar + tb.Text.Substring(cursorPosRight);

                string[] parts = result.Split('.');

                if (parts.Length > 1)

                {

                    if (parts[1].Length > 2 || parts.Length > 2)

                    {

                        e.Handled = true;

                    }

                }

            }

        }

MSSQL - Convert rows into column using group by statement

C#.NET - How to open windows application form from winword using Com objects ( Macro)

Step 1

1.      Add Class Library name "WordConnector"

Step 2

Rename Class1.cs as "clsConnector.cs"

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace WordConnector

{

    public class clsConnector

    {

        public void Start(Word.Document objDocument)

        {

            new frmWord(objDocument).Show();

        }

    }

}

----------------------------------------

Add form rename as "frmWord.cs"

-------------------------------------------------

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

namespace WordConnector

{

    public partial class frmWord : Form

    {

        string sDocumentName = string.Empty;

        public frmWord()

        {

            InitializeComponent();

        }

        public frmWord(Word.Document objDocument)

        {

            InitializeComponent();

            this.sDocumentName = objDocument.Name.ToString();

            this.Text = sDocumentName.Replace(".doc", string.Empty).ToUpper();

           

        }

    }

}

Step 3

Step 4

Click on Assembly Information button

Step 5

Check Make assembly COM-Visible

Step 6

Click Build and Scroll down check Register for COM interop.

Step 7

Click Signing menu

Step 8

1. Check sign the assembly.

2. Click choose a strong name key file combo box <New>

Step 9

Give key file name as "Key_WordConnector"

Step 10

Copy

WordConnector.dll and WordConnector.tlb from project bin folder

Step 11

Pate WordConnector.dll and WordConnector.tlb in " C:\WINDOWS\system32"

Step 12

1. Open winword document and Press Alt+F11

2. Write macro in module.

3.Click Tools -> References - >

5. Browse for WordConnector.tlb

Check Wordconnector

Add this macro in module

Press F8 and see form will open from worddocument