DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

How to Delete SQL Server Database Duplicate Rows from a Table Using a Specified Column List and No Temp Tables

SELECT COUNT(*),
	Column1,
	Column2,
	.
	.
	Columnn
FROM TestTable
GROUP BY 
	Column1,
	Column2,
	.
	.
	Columnn
HAVING COUNT(*) > 1

--find duplicate emails in a 'users' table:
SELECT id, email, count(email) FROM users GROUP BY email HAVING count(email) > 1

--Another approach is to list all the duplicate entries. We assume the field 'id' is the primary key of the table 'users'
SELECT DISTINCT t1.id, t1.email FROM users t1, users t2 WHERE t1.email = t2.email AND t1.id <> t2.id


/*
How to find duplicates in multiple columns
I have a table with columns b and c that links two other tables b and c, and I want to find all rows that have duplicates in either b or c.
*/

create table a_b_c(
   a int not null primary key auto_increment,
   b int,
   c int
);

insert into a_b_c(b,c) values (1, 1);
insert into a_b_c(b,c) values (1, 2);
insert into a_b_c(b,c) values (1, 3);
insert into a_b_c(b,c) values (2, 1);
insert into a_b_c(b,c) values (2, 2);
insert into a_b_c(b,c) values (2, 3);
insert into a_b_c(b,c) values (3, 1);
insert into a_b_c(b,c) values (3, 2);
insert into a_b_c(b,c) values (3, 3);

select b, c, count(*) 
from a_b_c
group by b, c
having 
	count(distinct b) > 1
	or count(distinct c) > 1;

CREATE FUNCTION dbo.ParseAlphaOnly
(
	@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @IncorrectCharLoc SMALLINT
	SET @IncorrectCharLoc = PATINDEX('%[^A-Za-z]%', @string)

	WHILE @IncorrectCharLoc > 0
	BEGIN
		SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
		SET @IncorrectCharLoc = PATINDEX('%[^A-Za-z]%', @string)
	END

	SET @string = @string
	RETURN @string
END
GO

CREATE FUNCTION dbo.ParseAlphaNumbersOnly
(
	@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @IncorrectCharLoc SMALLINT
	SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)

	WHILE @IncorrectCharLoc > 0
	BEGIN
		SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
		SET @IncorrectCharLoc = PATINDEX('%[^0-9A-Za-z]%', @string)
	END

	SET @string = @string
	RETURN @string
END
GO


CREATE FUNCTION dbo.ParseNumbersOnly
(
	@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @IncorrectCharLoc SMALLINT
	SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)

	WHILE @IncorrectCharLoc > 0
	BEGIN
		SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
		SET @IncorrectCharLoc = PATINDEX('%[^0-9]%', @string)
	END

	SET @string = @string
	RETURN @string
END
GO

--Test
SELECT dbo.ParseAlphaOnly('ABC”_I+{D[]}4|:e;””5,<.F>/?6')
GO
--Result
ABCIDeF

SELECT dbo.ParseAlphaNumbersOnly('ABC”_I+{D[]}4|:e;””5,<.F>/?6')
GO
--Result
ABCID4e5F6


SELECT dbo.ParseNumbersOnly('ABC”_I+{D[]}4|:e;””5,<.F>/?6')
GO
--Result
456

I like to do the AlphaNumeric sorting by using the sql select query in sql server 2005

10A
10C
11
12A
17A
1A
2A
4A
.
.
.
etc

I need this list to be as to

1A
2A
4A
10A
10C
11
12A
17A
.
.
.
etc 

For that I need to Split the field in to Numbeic and Alphabet and orderby Alphabet Field, Numeric Field

Alphabet Field = "RIGHT(C.CODE,PATINDEX('%[A-Z]%',REVERSE(C.CODE)))"
Numeric Field = "LEFT(C.CODE,LEN(C.CODE) - PATINDEX('%[0-9]%',C.CODE))"

select
 DISTINCT
 C.CODE AS TXT,
 C.CODE,
 CASE WHEN ISNUMERIC(C.CODE) = 1 THEN CAST(C.CODE AS INT) ELSE CAST(LEFT(C.CODE,LEN(C.CODE) - PATINDEX('%[0-9]%',C.CODE)) AS INT) END AS NumField ,
 CASE WHEN ISNUMERIC(C.CODE) = 0 THEN RIGHT(C.CODE,PATINDEX('%[A-Z]%',REVERSE(C.CODE))) ELSE C.CODE END AS AlphaField
FROM CATEGORY AS C
WHERE (C.CAT_TYPE = 'TRAN' OR C.CAT_TYPE = 'MEAL') AND (C.IN_USE = 'Y')
ORDER BY NumField, AlphaField, CODE

Install SSRS web parts in integrated mode

Coming Soon....

Install SSRS web parts in native mode

If you want to access report server content on a SharePoint site from a native mode report server, use the SharePoint 2.0 Web Parts that are included with Reporting Services.
Web parts are delivered to a SharePoint server as a cabinet (.cab) file. Run the Stsadm.exe tool on the .cab file from the command line to install the Web Parts. The Stsadm.exe tool is included in a SharePoint installation.

1. Copy the RSWebParts.cab to a folder on the SharePoint server. The .cab is installed with Reporting Services. By default, it is located in the C:\Program Files\Microsoft SQL Server\100\Tools\Reporting Services\SharePoint folder, (if missing please download it from here [RSWebParts.cab (23.08 kb)]). You can copy it to any folder on the SharePoint server, and then delete it later after you install the Web Parts.
2. On the computer that has the installation of the SharePoint product or technology, open a Command Prompt window and navigate to the folder that has the Stsadm.exe tool. The path will vary depending on which version of Windows SharePoint Services you are running. If you are using Windows SharePoint Services 3.0, the path is C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN.
3. Run Stsadm.exe on the .cab, using the following syntax:
   STSADM.EXE -o addwppack -filename "C:\ Program Files\Microsoft SQL Server\100\Tools\Reporting Services\SharePoint\RSWebParts.cab" -globalinstall
   Specifying -globalinstall adds the Web Parts to the global assembly cache (GAC). This step is necessary if you want to connect the Web Parts.
4. After you install the Web Parts, you can add them to a Web Part Page on a SharePoint site. You must have permission to create Web sites and add content.

Note : If you get the below error

An unexpected error has occurred.

Web Parts Maintenance Page: If you have permission, you can use this page to temporarily close Web Parts or remove personal settings. For more information, contact your site administrator.
Troubleshoot issues with Windows SharePoint Services.

Changing the trust level from WSS_Minimal to WSS_Medium to resolve the problem

Links

• Installing & Viewing Reports with SharePoint 2.0 Web Parts
• Install SSRS web parts in native mode
• Using SSRS Reports in SharePoint integrated mode with Custom Authentication
• Granting Permissions on a Native Mode Report Server
• MOSS Reporting Services Native Mode Options for Adding Reports to SharePoint Sites

DECLARE @TODAY DATETIME
SET @TODAY = GETDATE()

DECLARE @YEAR INT
DECLARE @WEEK INT

SET @YEAR = YEAR(@TODAY)
SET @WEEK = DATEPART(WK,@TODAY)
CREATE TABLE #TIMEFILTER([YEAR] int NULL, [QRT] nvarchar(50) NULL, [MONTH] nvarchar(50) NULL, [WEEK] nvarchar(50) NULL)
WHILE(@YEAR>(YEAR(@TODAY) - 5))
BEGIN
    WHILE(@WEEK > 0)
    BEGIN
        INSERT INTO #TIMEFILTER SELECT @YEAR, 'Q' + CAST(CASE WHEN MONTH(DATEADD(wk,@WEEK,CAST(@YEAR AS NVARCHAR(4)) + '/01/01')) < 4 THEN 1 WHEN MONTH(DATEADD(wk,@WEEK,CAST(@YEAR AS NVARCHAR(4)) + '/01/01')) < 7 THEN 2 WHEN MONTH(DATEADD(wk,@WEEK,CAST(@YEAR AS NVARCHAR(4)) + '/01/01')) < 10 THEN 3 ELSE 4 END AS NVARCHAR(4)),DATENAME(month, DATEADD(wk,@WEEK,CAST(@YEAR AS NVARCHAR(4)) + '/01/01')),'W' + CAST(@WEEK AS NVARCHAR(4))
        SET @WEEK = @WEEK - 1
    END
    SET @WEEK = 52
    SET @YEAR = @YEAR - 1
END
SELECT * FROM #TIMEFILTER;
DROP TABLE #TIMEFILTER;

Here's an easy way to check if a temp table exists, before trying to create it (ie. for reusable scripts) from Simon Sabin's post :

IF object_id('tempdb..#MyTempTable') IS NOT NULL
BEGIN
   DROP TABLE #MyTempTable
END

CREATE TABLE #MyTempTable
(
   ID int IDENTITY(1,1),
   SomeValue varchar(100)
)
GO

That way, if you have to change databases in the query window, you don't have to drop the tables before you run it again.

When we are writing modules for a big project we might find difficult to write the basic class file with stored procedure for function like Insert, Update, Delete and Select. But Imagine how use full that, you are passing the "TABLE NAME", "CLASS NAME", "NAMESPACE" to a SQL script file, That will Generate the entire class file(in c#) with Stored procedure for that.

Their are some basic rules.

• Table should HAVE primary kwy which is int, auto increment by 1

I had used some common Columns for all tables link

• CREATED_BY int
• CREATED_ON datetime (Default value "GETDATE()")
• UPDATED_BY int
• UPDATED_ON datetime (Default value "GETDATE()")
• ISDELETED bit (Default value "0")
• STATUS bit (Default valus "1")