June 20, 2006 at 9:42 am
i want to limit the data being stored in a table to the past 6 months only.
data in the table is refreshed each night.
how do i do this without hard coding a date?
June 20, 2006 at 9:45 am
Subtract 6 months from the MAX() date in the table ?
SELECT *
FROM YourTable
WHERE YourDateColumn >=
( SELECT DATEADD(m, -6, MAX(YourDateColumn)) FROM YourTable)
June 20, 2006 at 9:51 am
FROM YourTable
WHERE YourDateColumn >= @d
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 23, 2006 at 4:18 am
hi. i ended up using datepart, which converts the required part of a date into an integer , which is easily compared logically;;
SELECT * FROM
PSS_consolidated
WHERE
(
DATEPART(YEAR,GETDATE())) - (DATEPART(YEAR,contractStart)) <= 0
AND
(DATEPART(MONTH,GETDATE())) - (DATEPART(MONTH,ContractStart)) <= 6
Thanks for all the help folks
June 25, 2006 at 4:34 pm
that will only work once a year, at the end of june, won't it?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 27, 2006 at 9:45 am
<quote>
that will only work once a year, at the end of june, won't it?
</quote>
I think it will do exactly what i want, it will only return the last 6 months of data, from the moment the query is run.
the line below makes sure its always only going to return the current year data
(
DATEPART(YEAR,GETDATE())) - (DATEPART(YEAR,contractStart)) <= 0
the line below makes sure that it will only ever return the past 6 months of data.
eg. if its october ( 10th month) , it will return any data where 10 minus the contract start date is less than or equal to 6.
say we have a row created in july( 7th month)
10-7 = 3, so that row will be returned.
or if we have a row from january(1st month)
10-1 = 9 so it wont be returned.
AND
(DATEPART(MONTH,GETDATE())) - (DATEPART(MONTH,ContractStart)) <= 6
please someone correct me if my logic is incorrect.
June 27, 2006 at 9:51 am
Restriction to the current year means that too little data will be returned if the query is run before july.
Use of datepart(month,...) means that dates outside the last 6-month (i.e. approx last 182 day) period can be included. This may be what you want if data is to be reported in 'buckets' of monthly data.
But use of <= 6 means that 7 months' data is included if you run the query after June.
A stopped (24h) clock is correct for an instant once a day. In the same way your query is right once a year, at the moment June becomes July (of course with nightly data feeds this actually means you are right for a day).
If you had tested this code at another time of year, this would have become apparent...
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 27, 2006 at 10:02 am
apologies, you are correct. there is a problem in my logic. if the date is the january 2006, then my query will return nothing,in feb 2006 it will only return 1 month of data. thanks for pointing this out.
June 27, 2006 at 10:11 am
here is my new query
SELECT
* INTO #MSSalesTemp
FROM
MSS_SalesDate
WHERE
(
DATEPART(YEAR,GETDATE())*12 -(12 - DATEPART(MONTH,GETDATE()))) - (DATEPART(YEAR,salesDate)*12 -(12 - DATEPART(MONTH,salesDate))) <6
i convert everything to months, the current year and date as months, and the year and date of the record as months
eg. if it is currently January 2006, that will become (2006 *12) - (12 - MonthNum) = 24061
and the record is November 2005, that will be:
(2005 * 12) - (12 - MonthNum) = 2468
jsut take them away and presto!
Thanks for the help guys
June 27, 2006 at 11:16 am
If you want to report data for whole calendar months, why not use
datediff(month, salesDate, getdate())<6
?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply