First and Last day of a month.

To find First and last days of the month..

here I use the DATEDIFF function with m datepart and 0 which is for '1900-01-01'

First day of the month

For previous month
SELECT DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0)

For current month
SELECT DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)

For next month
SELECT DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)

Last day of the month
Here in this I have used the same code to compute First day of a month ,but after that I have added the result with by -1 second..
So when a date has 12.00 Am today then -1 second of it 11.59 Pm of the previous day.
this is how it has been done..

For previous month
select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

For current month
select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

For next month
select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))

Precision and scale values.

Sometimes I wonder why SQL server is not returning the exact number of decimals while multiplying or dividing two variables or columns….!
I hope everyone might have faced this problem.

For example:
declare @a numeric(18,2)
declare @b numeric(18,2)

set @a = 10.35
set @b = 20.34

In the above declared variables it is mentioned that precision = 18 and scale = 2, so I am expecting the result of same format. But here SQL server adds more scale while multiplying or dividing.

select @a*@b

Ans: 210.5190


select @a/@b

Ans: 0.50884955

But these were correct results and the scale value is decided based on the below formula.

Here let’s consider @a has p1 as precision and s1 as scale and @b p2 as precision and s2 as scale

Here p1 and p2 = 18 and both s1 and s2 = 2.

So the precision value would be of
p1 - s1 + s2 + max(6, s1 + p2 + 1) = 18 – 2 + 2 + max(6, 2 + 18 + 1) = 18 + 21 = 39

and scale value would be of
max(6, s1 + p2 + 1) = max(6, 2 + 18 + 1) = 21.

So to avoid more scale value we need to use cast or convert in the result

select cast(@a*@b as numeric(18,2))

Ans: 210.52

select cast(@a/@b as numeric(18,2))

Ans: 0.51

Multiply values of a column.

Multiplying with coalesce. its quite simple but I get values in a column multiplied...

declare @MyTable table (col int)

Insert into @MyTable values (1)
Insert into @MyTable values (2)
Insert into @MyTable values (3)
Insert into @MyTable values (4)
Insert into @MyTable values (5)

declare @a int
select @a = col*coalesce(@a,col) from @MyTable
select @a

Top with Ties

Specifies that additional rows be returned from the base result set with the same value in the
ORDER BY columns appearing as the last of the TOP n (PERCENT) rows.
TOP...WITH TIES can be specified only in SELECT statements,
and only if an ORDER BY clause is specified.

The following example obtains the top 10 percent of all employees with the highest salary
and returns them in descending order according to salary base rate.
Specifying WITH TIES makes sure that any employees that have salaries equal to the
lowest salary returned are also included in the result set,
even if doing this exceeds 10 percent of employees.

Here We Go..!!!

DECLARE @A TABLE (ID INT IDENTITY,NAM VARCHAR(12),SALARY INT)
INSERT INTO @A VALUES('A',1000)
INSERT INTO @A VALUES('B',5000)
INSERT INTO @A VALUES('C',3000)
INSERT INTO @A VALUES('D',1000)
INSERT INTO @A VALUES('E',2000)
INSERT INTO @A VALUES('F',1000)
SELECT * FROM @A ORDER BY SALARY



TOP(n)PERCENT WITH TIES :
SELECT TOP(10) PERCENT WITH TIES * FROM @A ORDER BY SALARY



TOP(n)WITH TIES :
SELECT TOP(1) WITH TIES * FROM @A ORDER BY SALARY

RENAME objects

Here in this Blog I have mentioned how to rename a object in the database.

while renaming a database name It should be changed to single user.

Renaming a table,Stored procedure,function,view,trigger uses same the way sp_renamedb
how ever renaming stored procedure,function,view and trigger is not a good option.

SQL server will not update sys.syscomments table while renaming objects.

so when ever we use sp_helptext to retrieve a Stored procedure or a function.
you can clearly see that the Stored procedure or the function name is a old one.

If you want to rename a object better drop and create again.

see for samples below..

Here we GO...!!!

Renaming a Database

use master
go
exec sp_dboption DBname_OLD ,'Single User',True
go
exec sp_renamedb 'DBname_OLD','DBname_NEW'
go
exec sp_dboption DBname_NEW,'Single User',False


Renaming a Table
sp_rename 'Tablename_OLD','Tablename_NEW'

Renaming a column
sp_rename 'Tablename.column_OLD','column_NEW','column'

Renaming a Stored Procedure,Function,View,Trigger
sp_rename 'Old_Name','New_Name'

In the below image I have created a procedure named SPTEST and renamed to SPTEST_NEW
But still when I use sp_helptext to retrieve the procedure it shows us the old Procedure name.
this is because sys.syscomments table has not been updated.



here either we can drop and create the procedure or we can alter the procedure from the SSMS window by right click on it.

Select TOP N Records

Most times while retrieving records from database we like to fetch top N records
There are couple of ways to do it..
here in this blog I have tried with

1) TOP
2) ROWCOUNT
3) ROW_NUMBER()

out of all the three I could prefer to use TOP.
ROWCOUNT will take priority if its specified smaller than TOP.
ROWCOUNT cannot be used in Functions.
ROW_NUMBER() can be used to fetch top N records with partition.

Here We Go..!!

USE AdventureWorks

Using TOP:

SELECT TOP 10 * FROM Person.StateProvince

with variable:

DECLARE @N INT
SET @N = 10
SELECT TOP (@N) * FROM Person.StateProvince

Using ROWCOUNT:

SET ROWCOUNT 10
SELECT * FROM Person.StateProvince

--TO RESET THE ROWCOUNT
SET ROWCOUNT 0

Using ROWNUMBER:

SELECT * FROM
( SELECT ROW_NUMBER()OVER(PARTITION BY 1 ORDER BY NAME) AS ROW_NUM,* FROM
Person.StateProvince
) TAB
WHERE ROW_NUM <=10

Ranking Functions

Ranking functions are used to partition the records based on the needs.
There are 4 types of ranking functions available in SQL server.

ROW_NUMBER()
Used to generate identity numbers for each rows.
RANK()
Rank function will partition the record but skip the numbers.
DENSE_RANK()
This function will partition the record with out skipping the numbers.
NTILE()
This will equally divide the record set into number of parts based on the number which we specify.


Lets use all those things in a single query to see the different..

In the below example we can partition the records by giving any of the column
Name (Multiple columns can also be given with comma seperator), I have given the integer value 1 to get the identity rownumbers

Here We GO..!!!

USE ADVENTUREWORKS

GO

SELECT LOGINID,
TITLE,
ROW_NUMBER()OVER(PARTITION BY 1 ORDER BY TITLE) AS ROWNUM,
RANK()OVER(PARTITION BY 1 ORDER BY TITLE) AS RANK,
DENSE_RANK()OVER(PARTITION BY 1 ORDER BY TITLE) AS DENSERANK,
NTILE(4)OVER(PARTITION BY 1 ORDER BY TITLE) AS NTILE
FROM HUMANRESOURCES.EMPLOYEE

Result window: