get last month

with this query below, need to get data only from last month

where lookup_date between (last month) any ideas
declare @sql1 varchar(1000) -- table structure
DECLARE @tableName1  varchar (8000) -- table name as previous month
declare @mapping varchar(8000) --sql query for each db
SET @tableName1 = '[dbo].[mapping' + DATENAME(m, DATEADD(month, -1, GETDATE()))+ CAST(YEAR(GETDATE()) AS char(4))+']' -- gets previous month
declare @lmon =DATENAME(m, DATEADD(month, -1, GETDATE()))+ CAST(YEAR(GETDATE()) AS char(4))
 
-- create table
SET @sql1 = 'CREATE TABLE ' + @tableName1 +'
(
control_id varchar(200),
lookup_date dattime  ,
pca_external_lookups varchar(200),
member_id varchar(200)
)'
 
EXEC(@sql1)
 
set @mapping = ' insert into '+ @tableName1 + ' select ?.dbo.control.member_id, ?.dbo.map_lookups.lookup_date, ?.dbo.map_lookups.pca_external_lookups, ?.dbo.control.member_id
FROM            ?.dbo.map_lookups CROSS JOIN
                         ?.dbo.control'
 
EXEC sp_MSForEachDb  @command1= @mapping

Open in new window

LVL 31
James MurrellProduct SpecialistAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
where lookup_date >= dateadd(month, -1, convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120))
  and lookup_date < convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120)

Open in new window

0
 
James MurrellProduct SpecialistAuthor Commented:
wow thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.