Group by month and year

Question

Post reply

Group by month and year

Viewing 9 posts - 16 through 23 (of 23 total)

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

JaybeeSQL Hall of Fame Points: 3501 More actions · Answer 1

Lynn Pettis - Friday, April 27, 2018 8:24 AM
Don't blame my code, I used what you posted.

Strange request, I had and have no intention of assigning blame.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

JaybeeSQL - Friday, April 27, 2018 6:53 PM
Lynn Pettis - Friday, April 27, 2018 8:24 AM
Don't blame my code, I used what you posted.
Strange request, I had and have no intention of assigning blame.

You said my code had errors. That meant your code had errors. Just saying. 😀

mattbr80 SSC Enthusiast Points: 150 More actions · Answer 3

Try:

WHERE CONVERT(VARCHAR(7),ContractEnrollDate,126) IN (‘2006-03’, ‘2006-12’)

sgmunson SSC Guru Points: 110639 More actions · Answer 4

JaybeeSQL - Friday, April 27, 2018 6:39 PM
sgmunson - Friday, April 27, 2018 9:06 AM
Let's just fix it:SELECT ContactEnrollDate FROM PersonalProfiles WHERE YEAR(ContactEnrollDate) = 2006 AND MONTH(ContactEnrollDate) IN (3,12) AND ContactCancelDate IS NOT NULL AND LastAttended IS NOT NULL;
No reason to add a condition for ContactEnrollDate to check for NULL when that will be met merely by having other conditions on it that specify values.
Curious, that too does not return any rows...

Then there aren't any rows that meet ALL the conditions. I copied the criteria other than that associated with the ContactEnrollDate from your original code. I'd also prefer not to use functions on a column in the WHERE clause, so Scott's condition for your WHERE clause is better from a performance perspective. However, do you get any rows returned if you take the last two conditions I show in my version of your query and add them to Scott's WHERE clause?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

jcelko212 32090 SSCrazy Eights Points: 9295 More actions · Answer 5

You don't seem to understand that dates are not kept in strings in SQL. That was COBOL, so doing string operations to pick out the the year, month and date fields from the date doesn't work. In Standard SQL you would use a procedure called EXTRACT( [YEAR | MONTH | DAY] FROM <date expression>).

A useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a skeleton:

CREATE TABLE Report_Periods (report_name CHAR(10) NOT NULL PRIMARY KEY, report_start_date DATE NOT NULL, report_end_date DATE NOT NULL, CONSTRAINT date_ordering CHECK (report_start_date <= report_end_date), ordinal_period INTEGER NOT NULL UNIQUE CHECK(ordinal_period > 0) etc);

These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. 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. Remember that when you design report name column.These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. 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. Remember that when you design report name column.

Please post DDL and follow ANSI/ISO standards when asking for help.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

jcelko212 32090 - Monday, April 30, 2018 10:46 AM
You don't seem to understand that dates are not kept in strings in SQL. That was COBOL, so doing string operations to pick out the the year, month and date fields from the date doesn't work. In Standard SQL you would use a procedure called EXTRACT( [YEAR | MONTH | DAY] FROM <date expression>).
A useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a skeleton:
CREATE TABLE Report_Periods (report_name CHAR(10) NOT NULL PRIMARY KEY, report_start_date DATE NOT NULL, report_end_date DATE NOT NULL, CONSTRAINT date_ordering CHECK (report_start_date <= report_end_date), ordinal_period INTEGER NOT NULL UNIQUE CHECK(ordinal_period > 0) etc);
These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. 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. Remember that when you design report name column.These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. 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. Remember that when you design report name column.

Hey, Mr. Celko, stop bashing old COBOL programmers and realize that this is a MS SQL Server forum and praising MySQL extensions to the SQL Language.

ScottPletcher SSC Guru Points: 100956 More actions · Answer 7

jcelko212 32090 - Monday, April 30, 2018 10:46 AM
You don't seem to understand that dates are not kept in strings in SQL. That was COBOL, so doing string operations to pick out the the year, month and date fields from the date doesn't work. In Standard SQL you would use a procedure called EXTRACT( [YEAR | MONTH | DAY] FROM <date expression>).
A useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a skeleton:
CREATE TABLE Report_Periods (report_name CHAR(10) NOT NULL PRIMARY KEY, report_start_date DATE NOT NULL, report_end_date DATE NOT NULL, CONSTRAINT date_ordering CHECK (report_start_date <= report_end_date), ordinal_period INTEGER NOT NULL UNIQUE CHECK(ordinal_period > 0) etc);
These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. 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. Remember that when you design report name column.These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. 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. Remember that when you design report name column.

Actually COBOL dates were typically stored not in a string format but in "packed decimal" format to save space, disk and memory being relatively vastly more expensive then. We designers then even omitted storing the "19" of the century just to save one byte per date (and, yes, it was worthwhile tradeoff!), which is what lead to the Y2K issue.
Also, EXTRACT in SQL would be a function, not a procedure, a fundamental difference in SQL.
I don't see the objection to temporal math for calendar ranges. It's the most efficient method. A calendar is a very simple concept, so I don't need a convoluted table to determine that the first calendar quarter is Jan-01 thru Mar-31 ... every year, every time.
Yes, custom business reporting date ranges would often require a table. But that's different than pure calendar ranges.
As to the MySQL convention, overloading the "date" data type to include date (temporal) ranges as well is an extremely complicating, and thus very dangerous, extension. For example, how do joins work against those values?

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003917 More actions · Answer 8

jcelko212 32090 - Monday, April 30, 2018 10:46 AM
You don't seem to understand that dates are not kept in strings in SQL. That was COBOL, so doing string operations to pick out the the year, month and date fields from the date doesn't work. In Standard SQL you would use a procedure called EXTRACT( [YEAR | MONTH | DAY] FROM <date expression>).
A useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a skeleton:
CREATE TABLE Report_Periods (report_name CHAR(10) NOT NULL PRIMARY KEY, report_start_date DATE NOT NULL, report_end_date DATE NOT NULL, CONSTRAINT date_ordering CHECK (report_start_date <= report_end_date), ordinal_period INTEGER NOT NULL UNIQUE CHECK(ordinal_period > 0) etc);
These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. 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. Remember that when you design report name column.These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. 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. Remember that when you design report name column.

The trouble is that neither of those formats is supported either by ANSI or ISO standards.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100956 More actions · Answer 9

Jeff Moden - Monday, April 30, 2018 11:43 AM
jcelko212 32090 - Monday, April 30, 2018 10:46 AM
You don't seem to understand that dates are not kept in strings in SQL. That was COBOL, so doing string operations to pick out the the year, month and date fields from the date doesn't work. In Standard SQL you would use a procedure called EXTRACT( [YEAR | MONTH | DAY] FROM <date expression>).
A useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a skeleton:
CREATE TABLE Report_Periods (report_name CHAR(10) NOT NULL PRIMARY KEY, report_start_date DATE NOT NULL, report_end_date DATE NOT NULL, CONSTRAINT date_ordering CHECK (report_start_date <= report_end_date), ordinal_period INTEGER NOT NULL UNIQUE CHECK(ordinal_period > 0) etc);
These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. 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. Remember that when you design report name column.These report periods can overlap; a fiscal quarter will be contained in the range of its fiscal year. 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. Remember that when you design report name column.
The trouble is that neither of those formats is supported either by ANSI or ISO standards.

A far bigger trouble is overloading a "date" data type to include date ranges, and inconsistent ones at that! (a single day/month/quarter/year all in the same column!) [Btw, isn't that a violation of 1NF as well, since a month is effectively a list of dates??]
What are the join rules for such a column? What are the comparison rules?
If my query has:
WHERE date_column >= '20170101' AND date_column < '20180101'
does that match '2017-00-00' or not? Does it match '2017-04-00' or not?
Does it match every separate date as well? Does it just match individual dates, not the date ranges? How does SQL "know" what to match?
That's an extremely complex extension of a single data type to a potential chimera.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.