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

present yyyymm in format good for calcuation Expand / Collapse
Posted Tuesday, August 19, 2014 2:00 PM



Group: General Forum Members
Last Login: Yesterday @ 5:10 PM
Points: 21,222, Visits: 33,731
CELKO (8/19/2014)
I'm working on setup where we have period like 201401 ( jan 2014), etc... And it is INTEGER , however it doesn't help if you need to some datediff operation.

Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML.

The report period table gives a name to a range of dates that is common to the entire enterprise.

CREATE TABLE Something_Report_Periods
(something_report_name CHAR(10) NOT NULL PRIMARY KEY
CHECK (something_report_name LIKE <pattern>),
something_report_start_date DATE NOT NULL,
something_report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (something_report_start_date <= something_report_end_date),

These report periods can overlap or have gaps. 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 advantages are that it will sort with the ISO-8601 data format required by Standard SQL and it is language independent. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[01][0-9]-00'

Mr. Celko,

Unfortunately YYYY-MM-00 and YYYY-00-00 are not dates. Also this is MS SQL Server not Oracle MySQL. And also, aren't you the one who insists EVERYTHING should be ANSI STANDARD?

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1605175
Posted Tuesday, August 19, 2014 5:58 PM



Group: General Forum Members
Last Login: Today @ 11:37 AM
Points: 37,077, Visits: 33,942
CELKO (8/19/2014)
5. Keep the rows short by using computed columns that are not materialized until needed for somethings. Overloading rows in a Calendar is a common problem.

Careful now... "It Depends" on what is being calculated and how it will be used. Non "materialized" (i.e. non "PERSISTED") computed columns cannot be indexed and, if someone makes the mistake of joining to such a column, I believe that the whole table will need to be read so that the entire column can be calculated before the join takes place. I believe it would perform as poorly as a non-SARGable date calculation on a column in a WHERE clause.

--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 #1605251
Posted Tuesday, August 19, 2014 8:42 PM



Group: General Forum Members
Last Login: Today @ 5:33 AM
Points: 1,945, Visits: 3,497
I was just trying to group a table by weeks. My manager told me to use a table like the following (I've changed the names and I have no control on this structure so please don't ask me to change it ):
The table has 17 years with just 52 or 53 rows per year.
The problem was that a bad execution plan was generated caused by the join on this table. As you can see, the rows are short, the range is short as well, the key is ordered but it still won't be the best option for that particular case.

I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is a separator token, ww is (01-53) week number and d is (1-7) day of the week.

This sucks! You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.
WHERE sale_day LIKE '2012W26-[67]'

There are several websites with calendars you can cut & paste, but you can start your search with:

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1605266
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse