SQL Server CTE Basics

Posted by Muhsinathk under Sql Server on 6/26/2012 | Points: 10 | Views : 1743 | Status : [Member] | Replies : 7
Hi,
I want to know more about SQL Server CTE Basics..




Responses

Posted by: Sriramnandha on: 6/26/2012 [Member] Starter | Points: 25
Posted by: Kirthiga on: 6/26/2012 [Member] Starter | Points: 25

Up
0
Down
The common table expression (CTE) can be used instead of temp table. It just store the result as temp result set.
;with cte as (

select GETDATE() Today
)
select * from cte


CTE can be used as Recursive CTE in which the query executes itself.

Declare @StartDay datetime, @EndDay datetime

Set @StartDay='2012-06-01'
Set @EndDay='2012-06-30'
;with cte(Date) as
(
select @StartDay
union all
select Date+1 from cte where Date < @EndDay
)
select Date,DATENAME(W,Date)Day from cte


Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/29/2012 [Member] Bronze | Points: 25

Up
0
Down
Thank you..
I want to know how can i get no of days from jan-dec

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 6/30/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

Try this below query to get No Of Days from Jan - Dec

Declare @StartDay datetime, @EndDay datetime

Set @StartDay='2012-01-01'
Set @EndDay='2012-12-31'
;with cte(Date) as
(
select @StartDay
union all
select Date+1 from cte where Date < @EndDay
)
select DATENAME(M,Date)Day ,count(Date)NoOfDays from cte
group by DATENAME(M,Date) option (MAXRECURSION 1000)


Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajkatie on: 7/1/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
you can also go refer following link
http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/

Rajesh Patel
.Net Trainer(MSCD | MCT | MS)
Brainbench (Asp.Net 4.5 and C# 5.0)

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muralidosscm on: 7/3/2012 [Member] Starter | Points: 25

Up
0
Down
msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Regards
Muralidoss M

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sqldev on: 3/11/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,

Below video have complete information about CTE like what is CTE , properties of CTE and examples for CTE in sql server.

http://www.youtube.com/watch?v=Ly-YqPVdVOk&feature=youtu.be


Best Regards,
Sql Expert
http://learnsqltips.blogspot.com/

Muhsinathk, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response