November 29, 2011 at 3:12 pm
Hello,
I'm looking for an answer that seems relatively simple; however, my brain is blocked. I'm looking to partition my table from 365 DayOfYear (DATEPART(DY,GETDATE())) to a 3 (1095) year rolling partition. Example would be that I'm currently partitioning based on:
select DATEPART(DY,'01/01/2011')
UNION ALL
select DATEPART(DY,'06/01/2011')
UNION ALL
select DATEPART(DY,'12/31/2011')
Returns:
1
152
365
I need to find a way to partition based on a 3 year scale that rolls.
For example:
01/01/2010 = 1
01/01/2011 = 366
01/01/2012 = 731
01/01/2013 = 1
01/01/2014 = 366
01/01/2015 = 731
etc, etc
In order to do this, I'll need some kind of math function to do so when inserting into the table based on date. I was able to get a rolling 3 part difference based on year via (((2010+333)%3)+1), (((2011+333)%3)+1), (((2012+333)%3)+1); however, I can't seem to figure out how to get it to give me a number between 1 and 1095.
Hope this makes any sense.
November 29, 2011 at 3:46 pm
Go the other way. You need to think in days off the base year.
;WITH cte AS
(SELECT '20100105' AS dt UNION ALL
SELECT '20090603' UNION ALL
SELECT '20080321' UNION ALL
SELECT '20070910')
select
DATEDIFF( dd, 0, dt) AS NumDays,
DATEDIFF( yy, 0, dt) AS NumYears,
DATEDIFF( yy, 0, dt)/ 3 * 3 AS BaseYears,
DATEDIFF( dd, dateadd( yy, DATEDIFF( yy, 0, dt)/ 3 * 3, 0) , dt) AS NumDaysOffBaseYearStart
FROM
cte
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 29, 2011 at 3:56 pm
Tristan Chiappisi (11/29/2011)
Hello,I'm looking for an answer that seems relatively simple; however, my brain is blocked. I'm looking to partition my table from 365 DayOfYear (DATEPART(DY,GETDATE())) to a 3 (1095) year rolling partition. Example would be that I'm currently partitioning based on:
I was thinking this through, and was curious as to your intentions on this. Usually you'd partition on BaseYear in the query I provided above on something like this, but that would require you to include the 'BaseYear' column in your queries for solid partition seeking.
Under most circumstances I'd create partitions against the base datetime column without any further calculations, assuming most of my queries would be applying where clauses against that range. I'd set them up in say three year bursts for your intent, something like the following:
CREATE PARTITION FUNCTION Gap3Year (DATETIME)
AS RANGE LEFT FOR ('19900101', '19930101', '19960101',
'19990101', '20020101', '20050101',
'200801010', '20110101', '20140101',
'20170101')
CREATE PARTITION SCHEME Gap3Year_Scheme
AS Partition Gap3Year
ALL TO [PRIMARY]
CREATE CLUSTERED INDEX idx_c_table1 ON table1 ( dt)
ON Gap3Year_Scheme (dt)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 1, 2011 at 11:56 am
CELKO (11/29/2011)
Quit computing and start thinking in data.The next trick is a report period calendar. It gives a name to a range of dats.
Who gives a flight? This is for partitioning, not user level organization. It's all about computing and nothing to do with data-mechanics.
I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL.
Celko, that's like recommending we store all integers as varchars, not to mention god help you if you tried any date mechanics on the field, we've seen what happens with overloaded data-types before (WHERE ISNUMERIC() = 1 for example). It is also completely incongruent with your above DDL of using DATE. I assume you're discussing the 'report_name' field, else I have no idea why you even brought this up other than to swing the ISO moneymaker around.
Once again, I point you to the fact that this entire exercise has absolutely nothing to do with end-user requests, nor interaction. It's on the internal partition mechanics and how to most easily use them.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply