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.
| Author |
Topic |
|
sjuust
Starting Member
7 Posts |
 Posted - 2003-09-17 : 11:08:11
|
| How can I select the records from current month and the minth before the last inserted record. All this based on a record creationdate field in a table?I think it's possible whith group by and having, but I can't figure it out! |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-17 : 11:44:13
|
| Post some DDL, DML and sample data inserts..Should help us a lot with an answer...you did give it a try didn't you?Brett8-)SELECT @@POST=NewId()That's correct! It's an AlphaNumeric! |
 |
|
|
sjuust
Starting Member
7 Posts |
Posted - 2003-09-17 : 12:06:22
|
quote:
Originally posted by X002548
Post some DDL, DML and sample data inserts..Should help us a lot with an answer...you did give it a try didn't you?
I've got this so far:SELECT * FROM TableWHERE DATEPART(WEEK,Datefield) in (SELECT DISTINCT TOP 2 Top2weeknumbers = DATEPART(WEEK,Datefield) FROM Table ORDER BY Top2weeknumbers DESC)ORDER BY Datefield DESCThis returns the record which have the last 2 weeknumbers from the last inserted record. That's ok but I only need them from the curren YEAR!..How can I get only the last two weeks from the last year from the last inserted record ? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-17 : 12:10:44
|
| Look at this thread:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29085give us some sample data, and the table you are querying from, and what you expect for results. Then, we can cut and paste into query analyzer the statements you have given us and very quickly give you the exact solution -- using your table, your data -- that you are looking for.- Jeff |
|
|
|
sjuust
Starting Member
7 Posts |
Posted - 2003-09-17 : 15:42:37
|
| Here it is, and I changed it from week to month, but I also need it for the last 2 weeks..CREATE TABLE [dbo].[HEADLINES] ([HeadlineDate] [smalldatetime] NULL ,[Headline] [varchar] (255) NULL ,)insert HEADLINES (HeadlineDate, Headline) values ('09/15/2003', 'This is a test headline')insert HEADLINES (HeadlineDate, Headline) values ('09/15/2003', 'This is a test headline')insert HEADLINES (HeadlineDate, Headline) values ('09/15/2003', 'This is a test headline')insert HEADLINES (HeadlineDate, Headline) values ('09/14/2003', 'This is a test headline')insert HEADLINES (HeadlineDate, Headline) values ('07/14/2003', 'This is a test headline')insert HEADLINES (HeadlineDate, Headline) values ('07/13/2003', 'This is a test headline')insert HEADLINES (HeadlineDate, Headline) values ('07/13/2003', 'This is a test headline')insert HEADLINES (HeadlineDate, Headline) values ('07/13/2003', 'This is a test headline')insert HEADLINES (HeadlineDate, Headline) values ('07/12/2003', 'This is a test headline')insert HEADLINES (HeadlineDate, Headline) values ('06/12/2003', 'This is a test headline')insert HEADLINES (HeadlineDate, Headline) values ('06/12/2003', 'This is a test headline')insert HEADLINES (HeadlineDate, Headline) values ('06/11/2003', 'This is a test headline')insert HEADLINES (HeadlineDate, Headline) values ('06/11/2002', 'This is a test headline')insert HEADLINES (HeadlineDate, Headline) values ('06/11/2002', 'This is a test headline')insert HEADLINES (HeadlineDate, Headline) values ('06/11/2002', 'This is a test headline')Result must be only from the last 2 month from last inserted dateHeadlineDate-|Headline-------------|09/15/2003---|This is a test headline|09/15/2003---|This is a test headline|09/15/2003---|This is a test headline|09/14/2003---|This is a test headline|07/14/2003---|This is a test headline|07/13/2003---|This is a test headline|07/13/2003---|This is a test headline|07/13/2003---|This is a test headline|07/12/2003---|This is a test headline| |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-17 : 15:51:31
|
| How are you defining last two months?becase 7/13/2003 is more than two months before 9/15/2003. Does that make sense?Look at this:select * from headlines where HeadlineDate >= DateAdd(mm,-2,(Select max(headlinedate) from headlines))FYI -- you need to put QUOTES around your date values ... they are being evaluated as a number divided by another divided by another and not as dates ....- Jeff |
|
|
|
sjuust
Starting Member
7 Posts |
Posted - 2003-09-17 : 17:14:22
|
quote:
Originally posted by jsmith8858
How are you defining last two months?becase 7/13/2003 is more than two months before 9/15/2003. Does that make sense?
Jeff, I did this on purpose, because if there are no records inserted for a month I still need the last 2 month's to be selected...sorry about the quotes, I have never created a table this way. |
|
|
|
|
|
|
|
|
Group records by N months from last inserted