Update On inner Join

    select  T.id,T1.IsFinalStatus_FirstLevel,T.IsFinalStatus_SecondLevel,T1.IsFinalStatus_SecondLevel As Change
    from IGRS_Report..tbl_Complaints_CompiledStatus T inner join IGRS_Report..tbl_Complaints_CompiledStatus T1

    On T.ComplaintCode=T1.ComplaintCode and  T1.IsFinalStatus_SecondLevel='ATRS' and T1.IsFinalStatus_FirstLevel <> 'ATRS'
    and T1.ComplaintCode=10159160008930
     and T.id >=T1.id

----------------------Update----------------------------------
     Update
    T set
    T.IsFinalStatus_FirstLevel=T1.IsFinalStatus_SecondLevel
    from IGRS_Report..tbl_Complaints_CompiledStatus T inner join IGRS_Report..tbl_Complaints_CompiledStatus T1

    On T.ComplaintCode=T1.ComplaintCode and  T1.IsFinalStatus_SecondLevel='ATRS' and T1.IsFinalStatus_FirstLevel <> 'ATRS'
    and T1.ComplaintCode=10159160008930
     and T.id >=T1.id

Display Number of Online Users Asp.net- Using Global.asax file

Global .asax file-

1. <%@ Application Language="C#" %>  
2.   
3. <script runat="server">  
4.   
5.     void Application_Start(object sender, EventArgs e)   
6.     {  
7.         // Code that runs on application startup  
8.         Application["TotalOnlineUsers"] = 0;  
9.     }  
10.       
11.     void Application_End(object sender, EventArgs e)   
12.     {  
13.         //  Code that runs on application shutdown  
14.   
15.     }  
16.           
17.     void Application_Error(object sender, EventArgs e)   
18.     {   
19.         // Code that runs when an unhandled error occurs  
20.   
21.     }  
22.   
23.     void Session_Start(object sender, EventArgs e)   
24.     {  
25.         // Code that runs when a new session is started  
26.         Application.Lock();  
27.         Application["TotalOnlineUsers"] = (int)Application["TotalOnlineUsers"] + 1;  
28.         Application.UnLock();  
29.     }  
30.   
31.     void Session_End(object sender, EventArgs e)   
32.     {  
33.         // Code that runs when a session ends.   
34.         // Note: The Session_End event is raised only when the sessionstate mode  
35.         // is set to InProc in the Web.config file. If session mode is set to StateServer   
36.         // or SQLServer, the event is not raised.  
37.         Application.Lock();  
38.         Application["TotalOnlineUsers"] = (int)Application["TotalOnlineUsers"] - 1;  
39.         Application.UnLock();  
40.     }  
41.          
42. </script>

Web config-

1. <system.web>  
2. <sessionState mode="InProc" cookieless="false" timeout="20"></sessionState>  
3. </system.web> 

Default.aspx -

1. <form id="form1" runat="server">  
2.     <div>  
3.     <p>No. of Online Users:<asp:Label ID="Label1" runat="server" Text="Label" ForeColor="#CC0000"></asp:Label></p>  
4.     </div>  
5. </form>  

Default.aspx.cs -

1. protected void Page_Load(object sender, EventArgs e)  
2. {  
3.         Label1.Text = Application["TotalOnlineUsers"].ToString();  
4. }

Instant and Dynamic Cross-Tab Reports using PIVOT in SQL Server 2005

It is a usual practice that we have data in a SQL Server table but without using any frontend application tool or programming language we find quite difficult to produce the data in our desired format. Most of the times the format is a 2 dimensional matrix sort and the report generation comes out to be quite cumbersome. I would not waste enough time in elaborating but would come straight to the point. I would explain with an example and give you all a Stored Procedure which would accept the input table and field on which the dynamic header should appear and immediately generate the output table.
Here we go,
 1. Create a table as follows,
     Create table DistrictData(DistName nvarchar(100) not null,MonYear varchar(6) not null,Expense float)
 2. Insert data into the table,
insert into DistrictData values('Agra','012011',2300.00)
insert into DistrictData values('Agra','022011',2600.00)
insert into DistrictData values('Agra','032011',1100.00)
insert into DistrictData values('Agra','042011',5500.00)
insert into DistrictData values('Agra','052011',1600.00)
insert into DistrictData values('Agra','062011',3400.00)
insert into DistrictData values('Aligarh','012011',1800.00)
insert into DistrictData values('Aligarh','022011',1100.00)
insert into DistrictData values('Aligarh','032011',1700.00)
insert into DistrictData values('Aligarh','042011',2400.00)
insert into DistrictData values('Aligarh','052011',2900.00)
insert into DistrictData values('Aligarh','062011',2700.00)

3. The data in the table can be seen by giving
(select * from DistrictData),

DistName	MonYear	Expense
Aligarh	012011	1800
Aligarh	022011	1100
Aligarh	032011	1700
Aligarh	042011	2400
Aligarh	052011	2900
Aligarh	062011	2700
Agra	012011	2300
Agra	022011	2600
Agra	032011	1100
Agra	042011	5500
Agra	052011	1600
Agra	062011	3400

4. Now a report is required in the format where the header gets generated on MonthYear dynamically and the values for the Expenses are shown under the  MonthYear and in front of the District. There is a often a requirement of a report like,

DistName	012011	022011	032011	042011	052011	062011
Agra	2300	2600	1100	5500	1600	3400
Aligarh	1800	1100	1700	2400	2900	2700

As and when a new MonthYear is added a new column automatically gets generated and similarly when a new district is added a new row for the report gets created.

5. The raw data (3) can be converted into the report form as in (4) simply by running a stored procedure given in (6) by creating the stored procedure just by running (6) and after successful creation then executing it as follows, 

                  exec PivotDynamicReport 'DistrictData','MonYear','Expense'

               [Sytax: exec PivotDynamicReport 'Table Name','Header Field Name','Data Field Name' ]

              
6. The above stored procedure 'PivotDynamicReport ' can be created as,

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE Procedure [dbo].[PivotDynamicReport]

@SourceTable            nvarchar(50),

@ColumnHeadField  nvarchar(50),

@DataField              varchar(50)

As

BEGIN

Declare     @sql  nvarchar(1000)

declare @Temp table (NewField nvarchar(50))

set @sql = 'Select distinct ' + @ColumnHeadField + ' from ' + @SourceTable

Insert into @Temp(NewField) EXEC sp_executesql @sql

DECLARE @PivotColumnHeaders VARCHAR(100)

DECLARE @PivotTableSQL NVARCHAR(MAX)

SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + cast(NewField as varchar) + ']','[' + cast(NewField as varchar)+ ']')FROM @Temp

SET @PivotTableSQL = N'select * from ' + @SourceTable + ' pivot (Sum (' + @DataField + ') for ' + @ColumnHeadField + ' in ('+ @PivotColumnHeaders +')) as TotalField '

EXECUTE(@PivotTableSQL)

END

How to parse comma delimited string into IN Clause in SQL Server

declare @commasepvalue varchar(50)='rashmi,ashish,punit,vishal'

select q2.value from

(SELECT cast('<x>'+replace(@commasepvalue,',','</x><x>')+'</x>' as xml) as thexml)q1 CROSS APPLY

(SELECT x.value('.','varchar(100)') as value FROM thexml.nodes('x') as f(x))q2

 

and Output will be----

Get SQL Server Restore history using T-SQL?

SELECT *
FROM MSDB..RestoreHistory WITH (nolock)
WHERE destination_database_name = 'MyDB'
ORDER BY restore_date DESC

Best way of Show and Save Image in asp.net

  //Convert image in byte and show image...................
protected void Upload_Click(object sender, EventArgs e)
    {
        try
        {
            Session["img"] =(byte []) FileUpload1.FileBytes;
            System.IO.Stream fs = FileUpload1.PostedFile.InputStream;
            System.IO.BinaryReader br = new System.IO.BinaryReader(fs);
            Byte[] bytes = br.ReadBytes((Int32)fs.Length);
            string base64String = Convert.ToBase64String(bytes, 0, bytes.Length);
            Image1.ImageUrl = "data:image/jpg;base64," + base64String;
            Image1.Visible = true;
        }
        catch (Exception ex)
        { }
    }
 //-convert byte array to image and Save in desire location....
protected void Save_Click(object sender, EventArgs e)
    {
        try
        {
            byte[] image = (byte[])Session["img"];

                MemoryStream ms = new MemoryStream(image);

            System.Drawing.Image returnImage = System.Drawing.Image.FromStream(ms);

           string path = Server.MapPath("~/image/");
            path += "test.jpg";
            returnImage.Save(path, System.Drawing.Imaging.ImageFormat.Bmp);
        }
        catch (Exception ex)
        { }
    }

Regular expression for date mm/dd/yyyy

 ^(0?[1-9]|[12][0-9]|3[01])[\/\-](0?[1-9]|1[012])[\/\-]\d{4}$

<asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server"                            ControlToValidate="txtDOB"  ErrorMessage="*"  ForeColor="#CC0000"
 ValidationExpression="^(0?[1-9]|[12][0-9]|3[01])[\/\-](0?[1-9]|1[012])[\/\-]\d{4}$"></asp:RegularExpressionValidator>