Year By Months -Columns

Question

Year By Months -Columns

angelreynosog

Mr or Mrs. 500

Points: 545
More actions
January 22, 2019 at 10:09 am

#409453

Hi guys
In what way can I have a result of a table that the columns are the years and months. I would not like to set fixed years, since I would like to take the years of the same DOCDATE column as well as the months. I would also like to determine which is the last day of that month in the year. I leave an excel so you have an idea.
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mike01 SSChampion Points: 11393 More actions · Answer 1

Can you post DDL, sample data. You will need to use a dynamic pivot to get what you want
You can use select EOMonth('1/22/2019') to get end of month

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Please don't use EOMonth. If you're trying to write criteria to isolate a month in your queries, use the standard of "SomeDate >= FirstOfMonth and SomeDate < FirstOfNextMonth". It'll work with all date resolutions (which means the last day of the month will never be missed because time wasn't accounted for) and, IIRC, I saw a test where EOMonth had a bit of a performance problem (I don't use it so I didn't save the URL).

I also recommend NOT using the PIVOT operator because I know that's slower than a good ol' CROSSTAB. The performance test can be found in the first link below and a method for doing dynamic CROSSTABs is in the article following that. Be advised that the performance tests were conducted a decade ago on older machinery and that I don't have recent quantitative test measurements. I honestly haven't used PIVOT since I made those original findings for the other reasons I mentioned.

http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

angelreynosog - Tuesday, January 22, 2019 10:09 AM
Hi guys
In what way can I have a result of a table that the columns are the years and months. I would not like to set fixed years, since I would like to take the years of the same DOCDATE column as well as the months. I would also like to determine which is the last day of that month in the year. I leave an excel so you have an idea.
Thank you

So how do you want to control which years and months to view? And what are you going to do with the output? I ask because it may make a difference in how the code should be created.

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

angelreynosog Mr or Mrs. 500 Points: 545 More actions · Answer 4

Mike01 - Tuesday, January 22, 2019 11:03 AM
Can you post DDL, sample data. You will need to use a dynamic pivot to get what you want
You can use select EOMonth('1/22/2019') to get end of month

Thanks!!! The solution Dynamics Pivot!

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

The problem here is that you don't quite understand how tables work. Columns are supposed to be attributes. But a year- month is not an attribute; it's a measurement of an interval data type on a scale called a calendar. You're trying to use SQL to write a report, not to do a query. For the last 30+ years, the correct netiquette on SQL forms has been to post DDL along with clear specs. Posting pictures is considered very rude because now people have to transcribe the data, guess at the keys, guess at the constraints, guess at the defaults and do all the things that you should have done for them. Please remember, people are helping you for free.

You can Google around for some of my other postings on this topic, but I like to borrow the MySQL convention for naming months of using zeros in the date fields -- "yyyy-mm-00", separating the months out from the year is a design flaw called attribute splitting.

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