SQL SERVER – Query to Find First and Last Day of Any month

Following queries will run respective to today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month...

First Day of Current Month

select dateadd(mm,datediff(mm,0,getdate()),0)

Last Day of Current Month

select dateadd(s,-1,dateadd(mm,datediff(mm,0,getdate())+1,0))

First Day of Last Month

select dateadd(mm,datediff(mm,0,getdate())-1,0)

Last Day of Last Month

select dateadd(s,-1,dateadd(mm,datediff(mm,0,getdate()),0))

First Day of Next Month

select dateadd(mm,datediff(mm,0,getdate())+1,0)

Last Day of Next Month

select dateadd(s,-1,dateadd(mm,datediff(mm,0,getdate())+2,0))

Major Date functions in SQL Server are

DateAdd()
DateDiff()
DataPart()
DateName()
Getdate()

 select CONVERT(DATE, dateadd(mm, datediff(mm,0, getdate())+1,-1))

 select CONVERT(DATE, CAST(YEAR(getdate()) AS VARCHAR(4)) + '/' + CAST(MONTH(getdate()) AS VARCHAR(2)) + '/' + '01', 111)  

How to get difference between two columns from two different tables?

Here you can find the differences between two columns from two different tables
Table t1
id price                            
1 12
2 10
3 18
Table t2
id price
1 22
2 6
3 18
o/p:
itemid    priceint1 priceint2 diff
1             12 22        -10
2              10 6          4
3              18 18           0

ABS It is  mathematical function that returns the absolute (positive) value of the specified numeric expression.

select coalesce(t1.id,t2.id) as itemid ,t1.price as priceint1,t2.price as priceint2,
abs(coalesce(t1.price,0)-coalesce(t2.price,0)) as [diff]
 from t1 full join t2
on t1.id=t2.id

 If you want with Negative values remove abs function from the query..

select coalesce(t1.id,t2.id) as itemid ,t1.price as priceint1,t2.price as priceint2,
(coalesce(t1.price,0)-coalesce(t2.price,0)) as [diff]
 from t1 full join t2
on t1.id=t2.id

2nd Way
DECLARE @T1 TABLE (Marks CHAR(2), Price SMALLINT);
DECLARE @T2 TABLE (Marks CHAR(2), Price SMALLINT);

INSERT INTO @T1 VALUES('A1',10);
INSERT INTO @T1 VALUES('A2',14);
INSERT INTO @T1 VALUES('A3',21);
INSERT INTO @T2 VALUES('A1',12);
INSERT INTO @T2 VALUES('A2',10);
INSERT INTO @T2 VALUES('A3',24);

SELECT t1.Marks AS ItemID, t1.Price AS PriceInT1
,ABS(t1.Price - t2.Price) AS Diff, t2.Price AS PriceInT2
FROM @T1 AS t1
INNER JOIN @T2 AS t2
ON t1.Marks = t2.Marks

Is DISTINCT faster than GROUP BY?

I have often seen a recommendation that using DISTINCT instead of GROUP BY is better from a performance perspective. DISTINCT is definitely a more readable alternative, but I wanted to test for myself as to what the truth was.
To compare the performance of the two, I wrote the following simple queries.
The query below helps in identifying the number of records that SQL Server will need to process. On my machine, when executed without the GROUP BY or the DISTINCT clause, the query generates records in excess of 380,000.

1.USE AdventureWorks2012;

2.GO

3.SELECT  COUNT(so1.object_id)

4.FROM    sys.objects AS so1

5.CROSS JOIN sys.objects AS so2 ;

6.GO

The queries below fetch the same information – one uses the GROUP BY whereas the other query uses a DISTINCT.

01.USE AdventureWorks2012;

02.GO

03.--Clean the buffers and procedure cache

04.DBCC DROPCLEANBUFFERS;

05.DBCC FREEPROCCACHE;

06.GO

07. 

08.--Evaluate GROUP BY

09.SELECT  so1.object_id,

10.so1.name

11.FROM    sys.objects AS so1

12.CROSS JOIN sys.objects AS so2

13.GROUP BY so1.object_id, so1.name ;

14.GO

15. 

16.--Clean the buffers and procedure cache

17.DBCC DROPCLEANBUFFERS;

18.DBCC FREEPROCCACHE;

19.GO

20. 

21. 

22.--Evaluate DISTINCT

23.SELECT  DISTINCT

24.so1.object_id,

25.so1.name

26.FROM    sys.objects AS so1

27.CROSS JOIN sys.objects AS so2 ;

28.GO

Before executing the two queries (one with the GROUP BY and the other with the DISTINCT), I had turned on the actual execution plan.

Upon studying the execution plan, the following observations can be made:

1. When the SQL server database engine detected that the 2^nd query did not have any aggregation function, it was smart enough to convert the GROUP BY into a DISTINCT when the query plan is generated
2. There is no difference in the execution plan across both the queries

In conclusion,

DISTINCT is not faster than GROUP BY. In fact, Both DISTINCT and GROUP BY demonstrate equal performance impact.
When aggregations are not being used, DISTINCT is more readable when compared to GROUP BY.

How to add 5 business days to all values of a date column?

First of all do not get confused between 5 days and 5 business days. There is a big difference between these two. 5 Business days means 5 working days(Not holidays). 5 days can be any day. It can be working day or holiday.

This seems to be very difficult question but, we will feel it very easy after knowing the answer. Please follow my clues below to qrite the query.

Clue 1: We need to keep three cases in mind while writing this query. 

• Saturday
• Sunday
• Weekday

Clue 2: If it is Saturday:

• Exclude Sunday(1 day) + Add 5 days which are business days(Mon - Fri) -- So if it is Saturday, add 6 days to the date.

Clue 3: If it is Saturday:

• Exclude 0 days + Add 5 days which are business days(Mon - Fri) -- So if it is Sunday, add 5 days to the date.

Clue 4: If it is Any Weekday:

• Exclude Saturday & Sunday which come in between(2 days) + Add 5 days which are business days -- So if it is Weekday, add 7 days to the date.

I hope now you do not need my query. You can write the query yourself. But I am sharing my query as my gift to you :)

---------------------------------------------------------------------------------------------
USE [ADVENTUREWORKS2012]
GO
---------------------------------------------------------------------------------------------
-- Add 5 Business days to the date.
---------------------------------------------------------------------------------------------
SELECT TOP 3 [ModifiedDate],

CASE WHEN DATENAME(WEEKDAY,[ModifiedDate]) = 'Sunday'
THEN DATEADD(dd,5,[ModifiedDate])

WHEN DATENAME(WEEKDAY,[ModifiedDate]) = 'Saturday'
THEN DATEADD(dd,6,[ModifiedDate])

ELSE DATEADD(dd,7,[ModifiedDate])

END AS Add_5_Business_Days

FROM [Person].[PersonPhone]
---------------------------------------------------------------------------------------------

To add Weekday name column 

select top 10 modifieddate,datename(weekday,modifieddate) as weekname,

CASE WHEN DATENAME(WEEKDAY,[ModifiedDate]) = 'Sunday'
THEN DATEADD(dd,5,[ModifiedDate])

WHEN DATENAME(WEEKDAY,[ModifiedDate]) = 'Saturday'
THEN DATEADD(dd,6,[ModifiedDate])

ELSE DATEADD(dd,7,[ModifiedDate])

END AS Add_5_Business_Days

FROM [Person].[PersonPhone]

Note: 

Business days means we need to exclude our holidays also. We can keep a where condition to the above query to filter that criteria.

SCOPE_IDENTITY

Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.

Syntax

SCOPE_IDENTITY( )

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

See Examples for an illustration.

Examples

This example creates two tables, TZ and TY, and an INSERT trigger on TZ. When a row is inserted to table TZ, the trigger (Ztrig) fires and inserts a row in TY.

USE tempdb
GO
CREATE TABLE TZ (
   Z_id  int IDENTITY(1,1)PRIMARY KEY,
   Z_name varchar(20) NOT NULL)

INSERT TZ
   VALUES ('Lisa')
INSERT TZ
   VALUES ('Mike')
INSERT TZ
   VALUES ('Carla')

SELECT * FROM TZ

--Result set: This is how table TZ looks
Z_id   Z_name
-------------
1      Lisa
2      Mike
3      Carla

CREATE TABLE TY (
   Y_id  int IDENTITY(100,5)PRIMARY KEY,
   Y_name varchar(20) NULL)

INSERT TY (Y_name)
   VALUES ('boathouse')
INSERT TY (Y_name)
   VALUES ('rocks')
INSERT TY (Y_name)
   VALUES ('elevator')

SELECT * FROM TY
--Result set: This is how TY looks:
Y_id  Y_name
---------------
100   boathouse
105   rocks
110   elevator

/*Create the trigger that inserts a row in table TY 
when a row is inserted in table TZ*/
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS 
   BEGIN
   INSERT TY VALUES ('')
   END

/*FIRE the trigger and find out what identity values you get 
with the @@IDENTITY and SCOPE_IDENTITY functions*/
INSERT TZ VALUES ('Rosalie')

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT   @@IDENTITY AS [@@IDENTITY]
GO

--Here is the result set.
SCOPE_IDENTITY
4
/*SCOPE_IDENTITY returned the last identity value in the same scope, which was the insert on table TZ*/

@@IDENTITY
115
/*@@IDENTITY returned the last identity value inserted to TY by the trigger, which fired due to an earlier insert on TZ*/




• IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
• @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
• SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.