August 2010

Fine Duplicate rows & Delete duplicate rows

Fine duplicate row
SELECT ColumnName,
COUNT(ColumnName) AS ColumnName
FROM tbl_Employee_M
GROUP BY ColumnName
HAVING ( COUNT(ColumnName) > 1 )


Delete duplicate rows

WITH DUPLICATE(ROW,GROUPROW,ID,FNAME,LNAME)
AS
(
SELECT ROW,GROUPROW= CASE WHEN ID=ID
THEN
(SELECT COUNT(*) FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW,
ID,LNAME,FNAME FROM MYTABLE
) AS A WHERE A.ID=B.ID AND
A.ROW<B.ROW)+1 END,ID,FNAME,LNAME FROM (SELECT ROW_NUMBER() OVER (ORDER
BY ID) AS ROW,
ID,LNAME,FNAME FROM MYTABLE
)AS B
)
DELETE FROM DUPLICATE WHERE GROUPROW1

August 23, 2010 by SQL Server Uncategorized

Pivot in SQL Server

Declare @table1 table (
SalesPerson varchar(50), Product varchar(50), SalesAmount float )

insert into @table1(SalesPerson,Product, SalesAmount) values (‘Bob’, ‘Pickles’, 100)
insert into @table1(SalesPerson,Product, SalesAmount) values (‘Sue’, ‘Oranges’, 50)
insert into @table1(SalesPerson,Product, SalesAmount) values (‘Bob’, ‘Pickles’, 25)
insert into @table1(SalesPerson,Product, SalesAmount) values (‘Bob’, ‘Oranges’, 300)
insert into @table1(SalesPerson,Product, SalesAmount) values (‘Sue’, ‘Oranges’, 500)

select * from @table1

SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM
(SELECT SalesPerson, Product, SalesAmount
FROM @table1 ) ps
PIVOT
(
SUM (SalesAmount)
FOR Product IN
( [Oranges], [Pickles])
) AS pvt

August 13, 2010 by SQL Server Uncategorized

Crystal report XML file as DataSource

Namespace:
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Web;

Global variable:
CrystalDecisions.Web.Report rpt = new CrystalDecisions.Web.Report();
CrystalDecisions.CrystalReports.Engine.ReportDocument rpt1;

Onbutton or Page Load or any event
rpt.FileName = Server.MapPath(“EventHistory.rpt”);
crdata.Report = rpt;

rpt1 = crdata.ReportDocument;

crp.ReportSource = rpt1;
crp.RefreshReport();

crp.DataBind();

August 13, 2010 by Crystal Report Uncategorized

Client System TimeZone Offset

The getTimezoneOffset method returns an integer value representing the number of minutes between the time on the current machine and UTC. These values are appropriate to the computer the script is executed on. If it is called from a server script, the return value is appropriate to the server. If it is called from a client script, the return value is appropriate to the client.

This number will be positive if you are behind UTC (e.g., Pacific Daylight Time), and negative if you are ahead of UTC (e.g., Japan).

For example, suppose a server in New York City is contacted by a client in Los Angeles on December 1. getTimezoneOffset returns 480 if executed on the client, or 300 if executed on the server.

function TZDemo()
{
var d = new Date();
var minutes = d.getTimezoneOffset();

var s = “”;
s += “The current local time is “;
s += minutes / 60;
if (minutes < 0)
s += " hours after UTC";
else
s += " hours before UTC";

return(s);
}

August 8, 2010 by Asp.Net Uncategorized