Hello, I have two columns in my database: Period - which incldues month numbers in a year, e.g. value of 1 represents January, 2 equal February and so on Year - values include 2011, 2012 and so on I want to create a custom column which will show result as 1/1/2011 if Period equals 1 and Year equals 2011 for a specific row.
Can someone please help me with how I can achieve this? Very much appreciate your help in advance,
You didn't state whether the result was month/day or day/month. I'll assume it's day/month; change the code below if it's the other way around.
ALTER TABLE dbo.tablename
ADD custom_column AS
'1/' + CAST(period AS varchar(2)) + '/' + CAST(year AS varchar(4))
Hello, Thank you for the quick reply. I am looking for dd/mm/yyyy format. For some reason I am not able to execute the query with the statement you provided. Can you please help to understand where can I place the statement you provided? Instead of Alter a table, I just want to add a column in the results window that displays date in dd/mm/yyyy format.
The table that I am pulling the data from is scpsdbs_lob, month values are listed under 'period' column and there is a column called 'year' which has 4 digit year value. I am including the query I am using right now.
select order_grp, year, period, net_sales = Sum(Case When category = 'ITEMS' and (QUANTITY < 0 AND UNIT_PRICE < 0 AND INV_TYPE <> 'ACR' ) Then (-1*([quantity]*[unit_price])) Else case when category = 'ITEMS' then ([quantity]*[unit_price]) end End),
order_grp_defined = case order_grp when 'RAM' then 'AMs' when 'APPSEL' then 'Appliance_Select' when 'ECOMM' then 'E-Commerce' else 'Augusta' end,
Month = case period when '1' then 'February' when '2' then 'March' when '3' then 'April' when '4' then 'May' when '5' then 'June' when '6' then 'July' when '7' then 'August' when '8' then 'September' when '9' then 'October' when '10' then 'November' when '11' then 'December' else 'January' end, count(distinct order_no) as order_count, from scpsdbs_lob where year in (2011, 2012, 2013) group by order_grp, year, period