Common Table Expression (CTE) with a View in SQL Server 2005/8

Posted: May 25, 2011 | Author: | Filed under: SQL Server 2005, SQL Server 2005 Express, SQL Server 2008 | 1 Comment

You cannot use Common Table Expression directly inside a View with SQL Server 2005/8  but you can use an inline table function to wrap the CTE returned as table. You can call the function inside the view as you would with a table.

Here is the sample code with my early recusive CTE sample;

Step 1 to create the function:

 

CREATE FUNCTION [dbo].[mytableFunction1]

()

RETURNS TABLE

AS

RETURN

(

with mycte as

(

SELECT 1 as i, CHar(ascii('A')) as myletter

UNION ALL

select i+1 , Char(ascii('A')+i) FROM mycte WHERE i<26

)

SELECT * FROM mycte

)

Step 2 to create the view:

CREATE VIEW [dbo].[View_CTE]

AS

SELECT i, myletter    FROM dbo.mytableFunction1() AS mytableFunction1_1

Edit: Thanks to James for pointing the error from the posting.
Here is the link from MSDN:
http://msdn.microsoft.com/en-us/library/ms190766(v=sql.90).aspx

From A to Z — Char, ASCII symbols with T-SQL recursive

Posted: May 24, 2011 | Author: | Filed under: SQL Server | Leave a comment

 

;with mycte as

(

SELECT 1 as i, CHar(ascii(‘A’)) as myletter

UNION ALL

select i+1 , Char(ascii(‘A’)+i) FROM mycte WHERE i<26

)

SELECT * FROM mycte

Free DNN Skin from Chris Hammond

Posted: May 19, 2011 | Author: | Filed under: DotNetNuke (DNN) | Leave a comment

You can find a free skin from Chris Hammond. More information from this link:
http://www.dotnetnuke.com/Resources/Forums/tabid/795/forumid/112/threadid/418440/scope/posts/Default.aspx

Get today’s or yesterday’s record with T-SQL

Posted: May 19, 2011 | Author: | Filed under: SQL Server, SQL Server 2005, SQL Server 2005 Express, SQL Server 2008 | 3 Comments

You can use DATEADD and DATEDIFF functions to get the start and end time of a particular day. You can compare your datetime column value with the start and end time and retrieve all records within the range.

Here is the code snippet:


 
 SELECT DATEADD(day, DATEDIFF(day,0,GETDATE())-1,0) as StartOfYesterday   
 SELECT DATEADD(day, DATEDIFF(day,0,GETDATE()),0) as StartOftheday  
SELECT DATEADD(day, DATEDIFF(day,0,GETDATE())+1,0) as StartOfNextday  
 
--Yesterday’s data 
 
…WHERE mydate >=DATEADD(day, DATEDIFF(day,0,GETDATE())-1,0) 
AND mydate < DATEADD(day, DATEDIFF(day,0,GETDATE()),0) 
 
--*****Today’s data 
…WHERE mydate >=DATEADD(day, DATEDIFF(day,0,GETDATE()),0) 
AND mydate < DATEADD(day, DATEDIFF(day,0,GETDATE())+1,0)

--Or
…WHERE mydate >=floor(cast(getdate() as float))
AND mydate < ceiling (cast(getdate() as float))

--*******

CAST/CONVERT function in Access

Posted: May 11, 2011 | Author: | Filed under: Access 2007 | Leave a comment

There is no CAST/CONVERT function in Access. But you can find a few equivalents for the convertion functions.

For example:

Cint(x)–CONVERT(smallint,x)

Clng(x)–CONVERT(int,x)

 Cstr(x)–CONVERT(varchar,x)

Cdbl(x)–CONVERT(float,x)

You can find more VBA functions  from Office Help files (within Access) if you need other data type conversion.