Transform Period and Year fields into Month Year

Question

Hello -

I currently have a table that has 10,000+ row, which includes the following fields:

Year | Period | Balance
2018 1 20
...
...
2018 2 20
...
...
2019 1 20

My goal is to reference the Period & Year values and concatenate/replace the values to "January 2018" or "February 2018" or "January 2019". I have attempted to transform data --> add column from examples --> and reference the two fields, however, it adds "January 2018" to the entire table in which I am unable to fix.

Thank you,
Justice

Answer 1

Declare @Sample Table(Year int, Period int, Balance decimal(9,2));  
Insert @Sample(Year, Period, Balance) Values  
(2018, 1, 20),  
(2018, 2, 20),  
(2019, 1, 20);  
  
Select Year, Period, Balance,   
   DateName(month, DateFromParts(Year, Period, 1)) + ' ' +  
   DateName(year, DateFromParts(Year, Period, 1))  
From @Sample;  

Tom

Answer 2

Check this approach too:

declare @t table ([Year] int, [Period] int, Balance money)

insert @t values
( 2018, 1, 20 ),
( 2018, 2, 20 ),
( 2019, 1, 20 )

select FORMAT(DATEFROMPARTS([Year], [Period], 1), 'MMMM yyyy')
from @t

It is also possible to insert the results into new column.

Answer 3

Please read the ANSI/ISO standards for this language. A field is defined as a meaningful subpart of the column. The way you're using this, it looks like you confused a field in the column. It's also too bad you didn't think enough of us to actually post DDL, as has been required in SQL forms the last 30 years. You've committed a common design flaw. It is called attributes splitting and it means that you separated out what should be fields within the column into their own columns. I think if you wanted to get this nameless table into at least first normal form. You should have declared it as

CREATE TABLE Foobar
(foo_period CHAR(7) NOT NULL PRIMARY KEY
CHECK (foo_period LIKE '[12][0-9][0-9]-[12]'),
foo_balance INTEGER NOT NULL);

I had to guess the key since you didn't bother to tell us.

> My goal is to reference the Period & Year values and concatenate/replace the values to "January 2018" or "February 2018" or "January 2019". I have attempted to transform data --> add column from examples --> and reference the two fields [sic], however, it adds "January 2018" to the entire table in which I am unable to fix.<<

For the past 30+ years, client/server computing has been based on the idea of separate tiers. There is one for the database and there is another for the display and they are separate. Your impulse of getting the period and year into a single meaningful atomic column was correct. However, you were trying to do it in the wrong tier of the system. You need to sit down and design a notation for these periods; I chose a four digit year and a dash to separate the period number. You might want to use a letter "P" or something else. However, the order of the fields should be something that will sort easily into proper chronological order.

Later, Microsoft may give us the interval data type in its temporal offerings. Currently, you're going to have to do your own, and I would recommend building a calendar table that consists of (period_code, start_timestamp, end_timestamp) keyed on the period_code and the constraint that (start_timestamp < end_timestamp)