SQL Server CTE Basics

Posted by Muhsinathk under Sql Server on 6/26/2012 | Points: 10 | Views : 2945 | Status : [Member] | Replies : 7

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,
I want to know more about SQL Server CTE Basics..

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	Hi, see think link http://msdn.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/ Regards sriram Muhsinathk, if this helps please login to Mark As Answer. \| Alert Moderator

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

0	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

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

0	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

0	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

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

0	you can also go refer following link http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/ Rajesh Patel R.P.A Developer \| Developer Trainer \| Clean Code Evangelist Muhsinathk, if this helps please login to Mark As Answer. \| Alert Moderator

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

0	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

0	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

Latest Posts