Returning Date Range from Function (Day, Month, Quarter, Year)

Some times we need to return date data as per our requirement like monthly ( start and end date of each) data for given date range. this function do the same


following is the code.


CREATE FUNCTION [dbo].[GetDateRange] (
  @StartDate DATETIME,
  @EndDate   DATETIME,
  @type varchar(20)
)
RETURNS @A TABLE (
  stdate datetime,eddate datetime, PartName varchar(30),[Year] int
)
AS
BEGIN
  if @type = 'daily'
begin
;with cte as 
( select CONVERT(DATETIME,@startDate) AS DATE 
UNION ALL 
select Dateadd(dd,1,DATE) as date 
from cte   
where date <= @endDate - 1 
)  

insert into @a
SELECT DATE, DATE + 1 - 1, 'Day ' + CAST(DatePart(dd, DATE) as varchar(10))  as Week_NO_Of_Year, DatePart(yy, DATE) as [Year]
FROM CTE
--Group by 'Day ' + CAST(DatePart(dd, DATE) as varchar(10))
order by (DATE) ASC

OPTION (MAXRECURSION 0)
end


else if @type = 'Weekly'
begin
;with cte as 
( select CONVERT(DATETIME,@startDate) AS DATE 
UNION ALL 
select Dateadd(dd,1,DATE) as date 
from cte   
where date <= @endDate - 1 
)  

insert into @a
SELECT MIN(DATE), MAX(DATE), 'Week ' + CAST(DatePart(ww, DATE) as varchar(10))  as Week_NO_Of_Year,DatePart(yy, DATE) as [Year]
FROM CTE
Group by 'Week ' + CAST(DatePart(ww, DATE) as varchar(10)), DatePart(yy, DATE)
order by MIN(DATE) ASC

OPTION (MAXRECURSION 0)
end
else if @type = 'Monthly'
begin

;with cte as 
( select CONVERT(DATETIME,@startDate) AS DATE 
UNION ALL 
select Dateadd(dd,1,DATE) as date 
from cte   
where date <= @endDate - 1 
)  
insert into @a
SELECT MIN(DATE), MAX(DATE), CAST(DATENAME(month, DATE) as varchar(10))  as Week_NO_Of_Year, DatePart(yy, DATE) as [Year]
FROM CTE
Group by CAST(DATENAME(month, DATE) as varchar(10)), DatePart(yy, DATE)
order by MIN(DATE) ASC

OPTION (MAXRECURSION 0)
end
else if @type = 'quarterly'
begin

;with cte as 
( select CONVERT(DATETIME,@startDate) AS DATE 
UNION ALL 
select Dateadd(dd,1,DATE) as date 
from cte   
where date <= @endDate - 1 
)  
insert into @a
SELECT MIN(DATE), MAX(DATE), 'Quarter ' + CAST(DatePart(QQ, DATE) as varchar(10))  as Week_NO_Of_Year, DatePart(yy, DATE) as [Year]
FROM CTE
Group by 'Quarter ' + CAST(DatePart(QQ, DATE) as varchar(10)), DatePart(yy, DATE)
order by MIN(DATE) ASC

OPTION (MAXRECURSION 0)
end
else if @type = 'yearly'
begin

;with cte as 
( select CONVERT(DATETIME,@startDate) AS DATE 
UNION ALL 
select Dateadd(dd,1,DATE) as date 
from cte   
where date <= @endDate - 1 
)  
insert into @a
SELECT MIN(DATE), MAX(DATE), 'year ' + CAST(DatePart(yy, DATE) as varchar(10))  as Week_NO_Of_Year, DatePart(yy, DATE) as [Year]
FROM CTE
Group by 'year ' + CAST(DatePart(yy, DATE) as varchar(10)),DatePart(yy, DATE)
order by MIN(DATE) ASC

OPTION (MAXRECURSION 0)
end


  RETURN;
END


When execute following results will return

Creating Computed / Calculated Column in SQL SERVER 2008

Calculated Field or Computed field can be created for multiple purposes. It can be created to sum up columns from a table or it receives a function as a data type that can use for any computation or restriction of data. You can use this column to avoid complex calculations at query time.

There are two ways to create calculated fields.

CREATING CALCULATED FIELD THROUGH SSMS.

In this example TotalAmont Column returns data by sum of Price and Other Charges values of each column.

CREATING CALCULATED FIELD THROUGH FUNCATION.

In This screen Part column returns data on the basis of CalcPart function.

USES OF CALCULATED FIELD


1- Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:

     1.1- Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED. A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.

     1.2- For example, if the table has integer columns a and b, the computed column a + b can be indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed because the value may change in subsequent invocations.

The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most expressions is considered nullable even if only nonnullable columns are present, because possible underflows or overflows will produce null results as well. Use the COLUMNPROPERTY function with the AllowsNull property to investigate the nullability of any computed column in a table. An expression that is nullable can be turned into a nonnullable one by specifying ISNULL(check_expression, constant), where the constant is a nonnull value substituted for any null result.


RESTRICTIONS OF CALCULATED FIELD

1- A computed column cannot be the target of an INSERT or UPDATE statement.