PhuocLe - Microsoft Dynamics CRM

Category:  SQL

Get years, months, days between 2 date Nov/08/2013

Sometime you want get how many years, months, days from 2 date.

The UDF here help you

CREATE FUNCTION [dbo].[fnGetYMD] 
(
	@d1 DATETIME,
	@d2 DATETIME
)
RETURNS NVARCHAR(MAX)
AS
BEGIN	
	IF (@d1 >= @d2) RETURN '0 day'	
	DECLARE @y INT 
	DECLARE @m INT
	DECLARE @d INT
	SET @y = DATEDIFF(yy, @d1, @d2) - CASE WHEN (MONTH(@d1) > MONTH(@d2)) OR (MONTH(@d1) = MONTH(@d2) AND DAY(@d1) > DAY(@d2)) THEN 1 ELSE 0 END
	SET @d1 = DATEADD(yy, @y, @d1)
	SET @m = DATEDIFF(m, @d1, @d2) - CASE WHEN DAY(@d1) > DAY(@d2) THEN 1 ELSE 0 END
	SET @d1 = DATEADD(m, @m, @d1)
	SET @d = DATEDIFF(d, @d1, @d2)
	DECLARE @ret NVARCHAR(MAX) = ''
	IF @y <> 0
	BEGIN
		IF @y = 1
			SET @ret = '1 year '
		ELSE
			SET @ret = CONVERT(NVARCHAR(MAX), @y) + ' years '
	END
	IF @m <> 0
	BEGIN
		IF @m = 1
			SET @ret = @ret + '1 month '
		ELSE
			SET @ret = @ret + CONVERT(NVARCHAR(MAX), @m) + ' months '
	END
	IF @d <> 0
	BEGIN
		IF @d = 1
			SET @ret = @ret + '1 day'
		ELSE
			SET @ret = @ret + CONVERT(NVARCHAR(MAX), @d) + ' days'
	END
	RETURN @ret
END

And the result from my birthday until to 11-08-2013

Category: SQL
Tags:

Delete Duplicate Rows Nov/01/2013

I have a query return a duplicate rows (duplicate by column: ColA and ColB).

And I want delete these duplicate rows (but keep the first row).

Now, I add a new column: DuplicateCount

Then I delete all rows by WHERE: DuplicateCount > 1. After that, I get the final results

SQL code snippet

IF OBJECT_ID('tempdb..#duplicate') IS NOT NULL DROP TABLE #duplicate
SELECT * 
	INTO #duplicate
FROM(
	SELECT 1 AS ColA, 1 AS ColB, 0 AS ColC
	UNION ALL
	SELECT 1, 1, 1 --duplicate
	UNION ALL
	SELECT 1, 1, 2 --duplicate
	UNION ALL
	SELECT 1, 2, 3
	UNION ALL
	SELECT 1, 2, 4 --duplicate
	UNION ALL
	SELECT 1, 3, 4
	UNION ALL
	SELECT 1, 4, 5
) AS duplicate
SELECT * FROM #duplicate
GO

;WITH CTE (ColA, ColB, ColC, DuplicateCount)
AS
(
	SELECT ColA, ColB, ColC,
		ROW_NUMBER() OVER(PARTITION BY ColA, ColB ORDER BY ColA ASC, ColB ASC) AS DuplicateCount
	FROM #duplicate
)
DELETE FROM CTE WHERE DuplicateCount > 1
GO

SELECT * FROM #duplicate
Category: SQL
Tags:

Paging in various versions of SQL Server Oct/31/2013

1. SQL CE

SELECT  *
FROM    graffiti_Posts p
ORDER BY p.Id ASC 
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY

2. SQL Server 2000

DECLARE @RowsPerPage INT = 10 , @PageNumber INT = 6
SELECT  *
FROM    ( SELECT TOP ( @RowsPerPage )
                    *
          FROM      ( SELECT TOP ( ( @PageNumber ) * @RowsPerPage )
                                *
                      FROM      dbo.graffiti_Posts
                      ORDER BY Id
                    ) AS posts1
          ORDER BY  Id DESC
        ) AS posts2
ORDER BY Id ASC

3. SQL Server 2008/R2, SQL Server 2005

DECLARE @RowsPerPage INT = 10 , @PageNumber INT = 6
SELECT  *
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( ORDER BY Id ) AS RowNum
          FROM      graffiti_Posts
        ) AS post
WHERE   post.RowNum BETWEEN ( ( @PageNumber - 1 ) * @RowsPerPage ) + 1 AND @RowsPerPage * ( @PageNumber )

4. SQL Server 2012

DECLARE @RowsPerPage INT = 10 , @PageNumber INT = 6
SELECT  *
FROM    graffiti_Posts
ORDER BY Id
OFFSET ( @PageNumber - 1 ) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY

 

Category: SQL
Tags:

Get size of all tables in database Oct/29/2013

Sometime you need to get size of all tables in database. This SQL code will help you.

DECLARE @tmpTable TABLE
    (
      [RowCount] INT ,
      [TableName] NVARCHAR(MAX)
    )
DECLARE @ResultTable TABLE
    (
      [Name] NVARCHAR(MAX) ,
      [Rows] INT ,
      [Reserverd] NVARCHAR(MAX) ,
      [Data] NVARCHAR(MAX) ,
      [IndexSize] NVARCHAR(MAX) ,
      [Unused] NVARCHAR(MAX)
    )
INSERT  INTO @tmpTable
        ( [RowCount] ,
          [TableName]
        )
        SELECT  [RowCount] = MAX(si.rows) ,
                [TableName] = so.name
        FROM    sysobjects so ,
                sysindexes si
        WHERE   so.xtype = 'U'
                AND si.id = OBJECT_ID(so.name)
        GROUP BY so.name
        ORDER BY 2 DESC
DECLARE @cursor CURSOR ,
    @tablename VARCHAR(MAX)
SET 
@cursor = cursor for
select [TableName] from @tmpTable
OPEN @cursor
WHILE 1 = 1 
    BEGIN
        FETCH FROM @cursor INTO @tablename
        IF @@fetch_status <> 0 
            BREAK
        INSERT  INTO @ResultTable
                ( [Name] ,
                  [Rows] ,
                  [Reserverd] ,
                  [Data] ,
                  [IndexSize] ,
                  [Unused]
                )
                EXEC sp_spaceused @tablename
    END
SELECT  [Name] ,
        ROUND(( CAST(REPLACE(data, ' KB', '') AS FLOAT) / 1024 ), 2) AS DataInMb ,
        [Rows] ,
        [Reserverd] ,
        [Data] ,
        [IndexSize] ,
        [Unused]
FROM    @ResultTable
ORDER BY ( CAST(REPLACE(data, ' KB', '') AS INT) ) DESC

And here the result after you run this SQL code.

Category: SQL
Tags:

Search

About Me

Connect

Projects

Categories

MB2

Tags

RSS