Monday, October 30, 2017

TSQL - Create a view with a CTE

SQL Server comes with many benefits. One of the major valuable features is view in SQL Server. You know that we are not able to create temp tables in the view statements but we have another feature called Common Table Expression (CTE) to avoid this problem.

Now, we can use n number of CTEs in the view statement.
How to create View with a CTE: If you want to create a CTE in view then there is no need to use semi colon before with clause as given below-

Create View vw_WeekdaysList
AS
------ Create on the fly CTE Table
with CurrentDay as
(
select NameDay=DATENAME(DW,getdate()), WeekDay=DATEPART(dw, getdate())
union all
select NameDay=DATENAME(DW,getdate()+WeekDay),
WeekDay=DATEPART(dw, getdate()+WeekDay)
from CurrentDay
----- set weekday conditions
where WeekDay<7
)

------ Pull Data From CTE Table: CurrentDay
select Top 100 percent
NameDay, WeekDay,
---- Set flag 1 for current day
CurrentDay=IIF(WeekDay=DATEPART(dw, getdate()),1,0) 
from CurrentDay
order by WeekDay

Pull CTE based view dataset as given below-
---- Pull Week Days List
Select NameDay, WeekDay,
CurrentDay
from dbo.vw_WeekdaysList
order by WeekDay

NameDay
WeekDay
CurrentDay
Sunday
1
0
Monday
2
0
Tuesday
3
1
Wednesday
4
0
Thursday
5
0
Friday
6
0
Saturday
7
0

CTEs statements stay inside Views definitions and we can use n number of CTEs and can meet the business requirements.

2 comments:

  1. Dig into this guide on Hong Kong registration now. hong kong business registry

    ReplyDelete
  2. Mail forwarding is a brilliant service as you may already know in which mail is forwarded to another location, it is a service that costs, however for many it can be extremely beneficial regardless of the cost. But what are some of the most important benefits offered, when it comes to mail forwarding services? Read on to find out. nevada virtual address

    ReplyDelete

Popular Posts