Friday, August 30, 2013

Calender table in sql server

Create a generic calender table and make use of it to calculate the Date ranges/ Weekdays/Weekends/Quarters in a year/day of a week/Month of date...

Below is the script to generate a calender.


IF EXISTS (SELECT * FROM information_schema.tables WHERE Table_Name = 'Generic_Calendar' AND Table_Type = 'BASE TABLE')
BEGIN
DROP TABLE [Generic_Calendar]
END

CREATE TABLE [Generic_Calendar]
(
DSNo INT Identity(1,1) primary Key,
    [CalendarDate]  DATE,
    WeekDay_End Tinyint,
    DayDate Tinyint,
    DayOfWeekName   Varchar(20),
    WeekNumber Tinyint,
    MonthNumber Tinyint,
    [MonthName] varchar(20),
    YearOftheDate SmallInt,
    QuarterNumber Tinyint
 )
 
 
go

 declare @date int
 WITH CTE_DatesTable
AS
(
  SELECT CAST('20000101' as date) AS [date]
  UNION ALL
  SELECT   DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= '21001231'
)
insert into [Generic_Calendar] ([CalendarDate],DayDate,WeekDay_End,DayOfWeekName,WeekNumber,MonthNumber,[MonthName],YearOftheDate,QuarterNumber )
SELECT [DWDateKey]=[date],[DayDate]=datepart(dd,[date]),DATEPART(weekday,[date]),
[DayOfWeekName]=datename(dw,[date]),[WeekNumber]=DATEPART( WEEK , [date]),[MonthNumber]=DATEPART( MONTH ,
[date]),[MonthName]=DATENAME( MONTH , [date]),[Year]=DATEPART(YY,[date]),
[QuarterNumber]=DATENAME(quarter, [date])
FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
go

Thursday, August 29, 2013

IDENTITY -misconceptions

1. A column defined with IDENTITY property always maintains unique values

   That is not true. A column with IDENTITY property maintains unique values only when UNIQUE or PRIMARY KEY constraints are included.

Check the below examples,



   Use tempdb
 
Create table #test(c1 int identity(1,1))

go
insert into #test default values 
go 10

go
DBCC CHECKIDENT ('#test', RESEED, 1);
go
insert into #test default values 
go 2

select * from #test

The table will have duplicate values inserted.  Now let us try with UNIQUE constraint.

Create table #test(c1 int UNIQUE identity(1,1))

go
insert into #test default values 
go 10

go
DBCC CHECKIDENT ('#test', RESEED, 1);
go
insert into #test default values --- This statement throws error.
go 2

select * from #test


2. The values generated by IDENTITY property are always sequential
     
    No, this statement is invalid in certain scenarios.
 When the row insert happens in a transaction and rolled back , then the generated identity values are not retained

.Create table #test_tran(c1 int UNIQUE identity(1,1))

go
insert into #test_tran default values
go 2

go
begin tran t1

insert into #test_tran default values
insert into #test_tran default values

rollback tran t1

go

insert into #test_tran default values

select * from #test_tran


Friday, August 23, 2013

Query to get No.Of specific Week days between two dates

The below query gives number of specific week days between two dates , the two dates can be in any order.

EX: Let us try to get number of Saturdays between two given dates.

 Declare @date1 datetime='20 aug 2013' ,
@date2 datetime='31 aug 2013'
 
select CASE when [PlanendDate]> [ActualEndDate] then (DATEDIFF(Day ,[ActualEndDate],[PlanendDate])/7)+1
                when [PlanendDate]< [ActualEndDate] then (DATEDIFF(Day ,[PlanendDate],[ActualEndDate])/7)+ CASE when datepart(dw,@date2)=7 then 1 else 0 end
                else 1
                end
               
                from (
select   case
                when datepart(dw,@date1)=7
                        then @date1
                else @date1+7-datepart(dw,@date1)
        end [PlanendDate],case
                when datepart(dw,@date2)=7
                        then @date2
                else @date2+7-datepart(dw,@date2)
        end [ActualEndDate]) A 

Saturday, August 17, 2013

Logical Query execution in SQL Server

The logical execution of a query in SQL server by Query processor is in the order of below clauses ,


  1. FROM Clause
  2. WHERE Clause
  3. GROUP BY Clause
  4. HAVING Clause
  5. SELECT Clause
  6. ORDER BY Clause
  7. TOP Clause