COUNT of records having more than 3 within a specfied time period

[spangeman]

Hi Everyone

This is a little difficult to explain so please bear with me. I’ll use a simplified example to make it easier. I have a table called BOOKS and a table called ORDERS.
BOOKS has the fields BOOK_ID, NAME and AUTHOR.
ORDERS has the fields ORDERNO, DATE, CUSTOMER_NAME and BOOK_ID

The books are books that could be ordered and they therefore have a one to many relationship with ORDERS as one book can be ordered many times. This relationship is based on the column BOOK_ID.

I want to run a SQL query on this data that will show me the BOOKS that have been ordered 3 or more times in any 30 days. That’s ANY 30 days, so averages are no good and neither are specific periods.

Is this possible?

Cheers
Spangeman

 

[monksnake]

It is possible, here is an example of this.

DECLARE @BOOKS TABLE(BOOK_ID INT, [NAME] VARCHAR(20), AUTHOR VARCHAR(20))
DECLARE @ORDERS TABLE(ORDERNO INT, [DATE] DATETIME, BOOK_ID INT)

insert into @BOOKS values (200, 'Peter Rabbit', 'Me')
insert into @BOOKS values (300, 'Johnny Rabbit', 'You')
insert into @BOOKS values (100, 'Scary Rabbit', 'Me You')
insert into @BOOKS values (500, 'Dead Rabbit', 'Me')

insert into @ORDERS values (1, '2006-04-11 10:05:16.263', 200)
insert into @ORDERS values (2, '2007-04-11 10:05:16.263', 200)
insert into @ORDERS values (3, '2007-04-11 10:05:16.263', 200)
insert into @ORDERS values (4, '2007-04-11 10:05:16.263', 300)
insert into @ORDERS values (5, '2007-04-10 10:05:16.263', 300)
insert into @ORDERS values (6, '2007-04-09 10:05:16.263', 300)
insert into @ORDERS values (7, '2007-04-11 10:05:16.263', 500)
insert into @ORDERS values (8, '2007-05-09 10:05:16.263', 200)

select distinct a.BOOK_ID, a.[NAME], a.AUTHOR from @BOOKS a inner join @ORDERS b on a.BOOK_ID = b.BOOK_ID where (select count(*) from @ORDERS where b.[DATE] between [DATE] and dateadd(dd, 30, [DATE]) and BOOK_ID = b.BOOK_ID) >= 3

<.