Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Last 3 months data Expand / Collapse
Author
Message
Posted Thursday, January 9, 2014 2:43 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 229, Visits: 693
I need to see records for last three months ie Dec , Nov, Oct.
No matter what the date is today...
Any advise?
My columnname is Time_Open which contains all the dates...
Post #1529579
Posted Thursday, January 9, 2014 3:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 12,994, Visits: 12,409
Something like this?

select *
from YourTable
where Time_Open > dateadd(month, datediff(month, 0, GETDATE()) - 4, 0)
and Time_Open < dateadd(month, datediff(month, 0, GETDATE()) - 1, 0)

--edit--

Fixed a couple typos.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1529590
Posted Thursday, January 9, 2014 3:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 229, Visits: 693
Thanks.
Post #1529591
Posted Thursday, January 9, 2014 9:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 35,262, Visits: 31,745
sharonsql2013 (1/9/2014)
Thanks.


Do you understand how the formulas work?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1529634
Posted Monday, January 20, 2014 2:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:06 PM
Points: 2, Visits: 14
You'd want to use getdate() to find the current day and date and use that in a formula with dateadd.
Post #1532793
Posted Monday, January 20, 2014 3:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 35,262, Visits: 31,745
trickg1 (1/20/2014)
You'd want to use getdate() to find the current day and date and use that in a formula with dateadd.


Welcome aboard. Do you have any code to support your suggestion or is Sean's good enough?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1532796
Posted Tuesday, January 21, 2014 10:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:06 PM
Points: 2, Visits: 14
Off the top of my head, no - I wanted to post yesterday in the noobs' section to get a post on the board. I've used things I've learned on this forum a lot over the years, and only yesterday I realized after trying to log in on a whim (with credentials I use on other forums) that I have had an account here since 2008.

I'll be better about posting code bits here and there from this point forward.
Post #1533458
Posted Wednesday, January 22, 2014 6:22 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:27 AM
Points: 71, Visits: 193
Such a simple question, but it made me think... There has to be a way to do this via date_sub, or datediff isn't there? I'll work on it, fun/valuable puzzle for me...
Post #1533892
Posted Wednesday, January 22, 2014 6:35 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:27 AM
Points: 71, Visits: 193
Would something like this work? I wrote it in "notepad" so not sure if syntax is correct...

SELECT *
FROM Table
WHERE Time_Open <= GETDATE()
AND Time_Open >= DATE_SUB(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0)

Post #1533895
Posted Thursday, January 23, 2014 7:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 12,994, Visits: 12,409
rho_pooka (1/22/2014)
Would something like this work? I wrote it in "notepad" so not sure if syntax is correct...

SELECT *
FROM Table
WHERE Time_Open <= GETDATE()
AND Time_Open >= DATE_SUB(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0)



It might be correct for MySQL but DATE_SUB is not a valid function in sql server. I suspect it will work just like sql server. This will get you EXACTLY 3 months ago, including the timestamp. That means that when you posted this on January 22 it would not return any data from October before the 22nd. This is why you have to do a couple of additional steps like I did so you start at the beginning of the month from 3 months ago.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1534062
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse