Query to Find FirstDayoftheMonth, LastDayoftheMonth , WeekStartDate and @WeekEndDate for given date

July 15, 2010 Leave a comment

/*Sets the first day of the week to a number from 1 through
7.

1-Monday, 2-Tuesday, 3-Wednesday,

4-Thursday, 5-Friday, 6-Saturday, 7-(default,U.S.English)
Sunday

Use the

@@DATEFIRST function to check the current setting of SET
DATEFIRST.

*/

 

SET DATEFIRST
1;

 

DECLARE @CurDate DateTime

 

DECLARE @FirstDayoftheMonth DateTime

 

DECLARE @LastDayoftheMonth DateTime

 

DECLARE @WeekStartDate Datetime

 

DECLARE @WeekEndDate Datetime

 

SET @CurDate = GETDATE()

 

SELECT @FirstDayoftheMonth = @CurDate -(DAY(@CurDate))+ 1

 

SELECT @LastDayoftheMonth =DATEADD(M,1,@CurDate)-DAY((DATEADD(M,1,@CurDate)))

 

SELECT @WeekStartDate = @CurDate -(DATEPART(DW,@CurDate)-1)

 

SELECT @WeekEndDate = @CurDate +(7-DATEPART(DW,@CurDate))

 

SELECT CONVERT(VARCHAR(10),@FirstDayoftheMonth,101)AS Date ,‘FirstDateoftheMonth’ASDescriptionUNION

 

SELECT CONVERT(VARCHAR(10),@LastDayoftheMonth,101)AS Date ,‘LastDateoftheMonth’ASDescriptionUNION

 

SELECT CONVERT(VARCHAR(10),@CurDate,101)AS Date ,‘CurrentDate’ASDescriptionUNION

 

SELECT CONVERT(VARCHAR(10),@WeekStartDate,101)AS Date ,‘WeekStartDate’ASDescriptionUNION

 

SELECT CONVERT(VARCHAR(10),@WeekEndDate,101)AS Date ,‘WeekEndDate’ASDescription

Categories: Date Format

UNION Vs UNION ALL

June 22, 2010 2 comments

UNION Vs UNION ALL

Combines the results of two or more queries into a single result set
that includes all the rows that belong to all queries in the union. The UNION
operation is different from using joins that combine columns from two tables.

UNION

Combines the results of two or more
queries(Table) into a single result set that includes all the rows that belong
to all queries in the union. The UNION operation is different from using joins
that combine columns from two tables. However, when using the UNION command, all
selected columns need to be of the same data type. With UNION, only distinct
values are selected.

UNION ALL

The UNION ALL command is equal to the
UNION command, except that UNION ALL selects all values. The difference between
Union and Union all is that Union all will not eliminate duplicate rows,
instead it just pulls all rows from all tables fitting your query specifics and
combines them into a table.

Example

–Create First Table

DECLARE @Table1 TABLE(ID
Int,
EmployeeName VarChar(100))

INSERT INTO @Table1

SELECT 1,‘Mano’

INSERT INTO @Table1

SELECT 2,‘Bala’

INSERT INTO @Table1

SELECT 3,‘Arun’

INSERT INTO @Table1

SELECT 4,‘Kathir’

INSERT INTO @Table1

SELECT 5,‘Harish’

 

–Create Second Table

DECLARE @Table2 TABLE(ID
Int,
EmployeeName VarChar(100))

 

INSERT INTO @Table2

SELECT 1,‘Raj’

INSERT INTO @Table2

SELECT 2,‘Kathir’

INSERT INTO @Table2

SELECT 3,‘Vinoth’

INSERT INTO @Table2

SELECT 3,‘Bala’

–UNION ALL

SELECT EmployeeName FROM @Table1

UNION ALL

SELECT EmployeeName FROM @Table2

 

–UNION

SELECT EmployeeName FROM @Table1

UNION

SELECT EmployeeName FROM @Table2

We can see from the result set of UNION
ALL
that it returns everything from both the table but from UNION returns only DISTINCT rows from both the table is only retrieved
.

 

CONCLUSION: UNION eliminates
any duplicate rows from final result set while UNION ALL returns basically all
rows available including duplicates.  That is the cause of UNION being
slow.  For each row UNION operator checks whether the entire row exists in previous rows or
not.  And for making this validation UNION by default 1st sort the result
set on the 1st available column of the result set.

Categories: UNION Vs UNIONALL
Follow

Get every new post delivered to your Inbox.