Return the last month's record base on specific day

Question

Return the last month's record base on specific day

AllHype

Newbie

Points: 5
More actions
January 14, 2013 at 9:56 am

#393932

I'm trying to figure out how to return records from the previous month up to the 10th of that month. This process will be on going for each month that passes.
For example, I should return the UserID (1,2,3) base on current month (January) and next month (February) I should return (4,5).
[UserID], [FirstName],[LastName],[CreationDate]
(1, Joe,Smith,2012-12-10)
(2, Mike,Smith,2012-12-8)
(3, Steve,Smith,2012-12-6)
(4, William,Smith,2012-01-10)
(5, Brent,Smith,2012-01-7)
I've seen a lot of the following statements and I'm wonder if I need to create a function to get the results that I'm looking for.
SELECT DATEADD(m,-1, Dateadd(d,1-DATEPART(d,getdate()),GETDATE())) AS [First Day of Previous Month]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Greg Snidow SSCoach Points: 16108 More actions · Answer 1

AllHype, is this for a class? You should read this article, http://www.sqlservercentral.com/articles/Best+Practices/61537/

, then post your sample data, ddl, and what you have tried so far. You do not need a function for this.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

AllHype (1/14/2013)
I'm trying to figure out how to return records from the previous month up to the 10th of that month. This process will be on going for each month that passes.
For example, I should return the UserID (1,2,3) base on current month (January) and next month (February) I should return (4,5).
[UserID], [FirstName],[LastName],[CreationDate]
(1, Joe,Smith,2012-12-10)
(2, Mike,Smith,2012-12-8)
(3, Steve,Smith,2012-12-6)
(4, William,Smith,2012-01-10)
(5, Brent,Smith,2012-01-7)
I've seen a lot of the following statements and I'm wonder if I need to create a function to get the results that I'm looking for.
SELECT DATEADD(m,-1, Dateadd(d,1-DATEPART(d,getdate()),GETDATE())) AS [First Day of Previous Month]

Your example doesn't make sense according to your stated requirement.

The prior month for next month, February, would be Jan 2013, but 4 and 5 are for Jan 2012.

What date range selection do you want when it is after the 10th of the month?

Greg Snidow SSCoach Points: 16108 More actions · Answer 3

Michael Valentine Jones
Your example doesn't make sense according to your stated requirement.
The prior month for next month, February, would be Jan 2013, but 4 and 5 are for Jan 2012.
What date range selection do you want when it is after the 10th of the month?

I think this is probably what was meant, I thought the same thing...

WITH Data (UserID, FirstName, LastName, CreationDate) AS

(SELECT 1, 'Joe','Smith','2012-12-10' UNION ALL

SELECT 2, 'Mike','Smith','2012-12-08' UNION ALL

SELECT 3, 'Steve','Smith','2012-12-06' UNION ALL

SELECT 4, 'William','Smith','2013-01-10' UNION ALL

SELECT 5, 'Brent','Smith','2013-01-07')

SELECT * FROM Data

ORDER BY CreationDate

If so, then, according to the specs, only 4 would not show up for a January run.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.