select records within 12 months of each other

[FALCONSEYE]

Hello,

I am trying to select all the B types within 12 months of each other.

Here is the sql to create the table. I need to select all type B s that are within 12 months. In this case, 5,4, and 3.

How do I do this?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_ads](
	[id] [int] NULL,
	[dateStamp] [date] NULL,
	[type] [char](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[tbl_ads] ([id], [dateStamp], [type]) VALUES (1, CAST(0x61370B00 AS Date), N'b')
INSERT [dbo].[tbl_ads] ([id], [dateStamp], [type]) VALUES (2, CAST(0x01380B00 AS Date), N'a')
INSERT [dbo].[tbl_ads] ([id], [dateStamp], [type]) VALUES (3, CAST(0x52380B00 AS Date), N'b')
INSERT [dbo].[tbl_ads] ([id], [dateStamp], [type]) VALUES (4, CAST(0x73380B00 AS Date), N'b')
INSERT [dbo].[tbl_ads] ([id], [dateStamp], [type]) VALUES (5, CAST(0xDA380B00 AS Date), N'b')

ColdFusion Ninja for hire.

 

[gk53]

SOMETHING LIKE

declare @tbl_ads as table(
	[id] [int] NULL,
	[dateStamp] [date] NULL,
	[type] [char](1) NULL
) 

INSERT @tbl_ads ([id], [dateStamp], [type]) VALUES (1, CAST(0x61370B00 AS Date), N'b')
INSERT @tbl_ads ([id], [dateStamp], [type]) VALUES (2, CAST(0x01380B00 AS Date), N'a')
INSERT @tbl_ads ([id], [dateStamp], [type]) VALUES (3, CAST(0x52380B00 AS Date), N'b')
INSERT @tbl_ads ([id], [dateStamp], [type]) VALUES (4, CAST(0x73380B00 AS Date), N'b')
INSERT @tbl_ads ([id], [dateStamp], [type]) VALUES (5, CAST(0xDA380B00 AS Date), N'b')

select *
from @tbl_ads 
where DATEDIFF(d, dateStamp ,getdate())< 365
AND [TYPE]='b'

 

[bborissov]

Why not 1?
It is in 12 months range from 3 and 4.

Borislav Borissov
VFP9 SP2, SQL Server