Group records by N days from last inserted

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Group records by N days from last inserted

Author

Topic

sjuust
Starting Member

7 Posts

Posted - 2003-09-15 : 12:07:50

How can I group the records from the last N days from the last inserted record in a table with only the creationdate from each record?

In other words, I have a table with everyday about 30 new inserted records and want to select only the records from the last day a record is inserted AND the day before the last inserted record. Even if there are no insertions for a few day's.

I think it's possible with DATEPART(DayOfYear) from the date the last record is inserted and then a day before..

Any guru's know what I mean?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-15 : 14:03:25

If you just want the two days before the last insert.

select *
from tbl
where creationdate > convert(varchar(8),dateadd(dd,-1,(select max(creationdate) from tbl)),112)

if you want summaries for the two days
select convert(varchar(8),creationdate,112), sum(...)
from tbl
where creationdate > convert(varchar(8),dateadd(dd,-1,(select max(creationdate) from tbl)),112)
group by convert(varchar(8),creationdate,112)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-15 : 14:28:49

Does this return anything useful:


select 
  datediff(d,creationdate, getdate()) as DaysAgo,
  count(*) as [# of Rows]
from 
  yourtable
group by 
  datediff(d,creationdate, getdate())

?

just add a filter to show only the date range you want.

- Jeff

sjuust
Starting Member

7 Posts

Posted - 2003-09-15 : 18:13:29

quote:
Originally posted by nr

If you just want the two days before the last insert.

select *
from tbl
where creationdate > convert(varchar(8),dateadd(dd,-1,(select max(creationdate) from tbl)),112)

Great, thank you..but now I encounter the next challenge: There are day's when there are no records inserted and these must be skipped.

For example:

Monday - 20 records inserted
Tuesday - 32 records inserted
Wednesday - NO RECORDS INSERTED
Thursday - NO RECORDS INSERTED
Friday - 13 records inserted

Now I want to display the records inserted on Friday and from the last day before Friday. In this case that is Tuesday..and no more than 2 days! Hmmm..now it's getting difficult eh?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-16 : 01:33:17

select *
from tbl
where creationdate >
(select top 1 dte from (select top 2 dte = convert(varchar(8),creationdate,112) from tbl order by dte desc) a order by dte)
or just
select *
from tbl
where convert(varchar(8),creationdate,112) in
(select top 2 dte = convert(varchar(8),creationdate,112) from tbl order by dte desc)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sjuust
Starting Member

7 Posts

Posted - 2003-09-17 : 06:31:33

quote:
Originally posted by nr

select *
from tbl
where creationdate >
(select top 1 dte from (select top 2 dte = convert(varchar(8),creationdate,112) from tbl order by dte desc) a order by dte)
or just
select *
from tbl
where convert(varchar(8),creationdate,112) in
(select top 2 dte = convert(varchar(8),creationdate,112) from tbl order by dte desc)

Thanks! I only had to add 'DISTINCT' (select DISTINCT top 2 dte = etc..) to make it work.

Do you know how I can do the same for the last N weeks? Or months? I thought if I can get it to work for N day's its the same for weeks or month but that doesn't work ;(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources