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
|
CTEs statements stay inside Views definitions and we can use n number of CTEs and can meet the business requirements.
To learn more, please follow us -
http://www.sql-datatools.com
To
Learn more, please visit our YouTube channel at —
http://www.youtube.com/c/Sql-datatools
To
Learn more, please visit our Instagram account at -
https://www.instagram.com/asp.mukesh/
To
Learn more, please visit our twitter account at -
https://twitter.com/macxima
Dig into this guide on Hong Kong registration now. hong kong business registry
ReplyDeleteMail 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