how to create a dynamic view?

Is there a way to create a dynamic view, that will read the current month and select records in table for the last 12 months?

thanks

Something like this should work for you: This will alway provide the data for the previous twelve months up to the moment of execution.

Code Snippet

CREATE TABLE MyTable
( RowID int IDENTITY,
DateCol datetime,
)
GO

CREATE VIEW MyView
AS
SELECT
RowID,
DateCol
FROM MyTable
WHERE DateCol >= ( dateadd( month, -12, getdate() ) )
GO

SET NOCOUNT ON

INSERT INTO MyTable VALUES ( '02/15/2006' )
INSERT INTO MyTable VALUES ( '03/01/2006' )
INSERT INTO MyTable VALUES ( '04/01/2006' )
INSERT INTO MyTable VALUES ( '03/15/2006' )
INSERT INTO MyTable VALUES ( '02/28/2006' )
INSERT INTO MyTable VALUES ( '02/15/2007' )
INSERT INTO MyTable VALUES ( '03/01/2007' )
INSERT INTO MyTable VALUES ( '04/01/2007' )
INSERT INTO MyTable VALUES ( '03/15/2007' )
INSERT INTO MyTable VALUES ( '02/28/2007' )

SELECT *
FROM MyView
ORDER BY DateCol DESC

DROP TABLE MyTable
DROP VIEW MyView

|||You could do something along the lines of the following:

CREATE VIEW YourLastTwelve
AS
SELECT *
FROM YourTable
WHERE YourDateField < CONVERT(varchar(25),
DATEADD(m, 1, GetDate() + 1 - DAY(GetDate())), 101)
AND YourDateField >= CONVERT(varchar(25), DATEADD(yy, -1 ,
DATEADD(m, 1, GetDate() + 1 - DAY(GetDate()))), 101)

-Sue