Change table column order - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Change table column order

Author

Topic

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-13 : 09:57:33

Hi,

Do you guys know how to change table column order, I understand that the columns will arrange themselve as wrote it in code with the order.However the problem I encounter is that some of the code because of the use and logic, it appear not following as an order. Therefore, now I want to arrange it again as the following order like : sale, cogs, gross margin in $, gross margin in percent. This is what I have so far but the 3 columns gross margin in percent apprear at the end of the table.

DECLARE @current_month INT;
DECLARE @previous_month INT;
DECLARE @previous_2_months INT;
DECLARE @previous_3_months INT;
SET @current_month =CAST(DATEPART(m,getdate())as INT);
SET @previous_month =CAST( DATEPART(m, DATEADD(m, -1, getdate()))as INT);
SET @previous_2_months =CAST( DATEPART(m, DATEADD(m, -2, getdate())) as INT);
SET @previous_3_months =CAST( DATEPART(m, DATEADD(m, -3, getdate())) as INT);

SELECT
*
, CASE WHEN current_month_sales <>0
THEN
((current_month_sales - current_month_cogs ) / (current_month_sales ) *100) ELSE 0 END AS current_month_gm_percent

,CASE WHEN previous_month_sales <> 0
THEN ((previous_month_sales -previous_month_cogs )/ (previous_month_sales )*100)ELSE 0 END AS previous_month_gm_percent

,CASE WHEN previous_2_months_sales <>0
THEN ((previous_2_months_sales - previous_2_months_cogs )/ (previous_2_months_sales )* 100) ELSE 0 END AS previous_2_months_gm_percent

FROM
(

SELECT
[branch_id]+' - '+[branch_description] as 'branch'
--,CAST (CONVERT(CHAR(1), [p21_sales_history_report_view].period)
,COALESCE(p21_view_oe_hdr.class_2id,'STORE') as 'invoice_status'
,@current_month as current_period
,SUM(CASE WHEN [p21_sales_history_report_view].period = @current_month THEN [detail_price] ELSE 0 END) as current_month_sales
,SUM(CASE WHEN [p21_sales_history_report_view].period = @current_month THEN [detail_cogs] ELSE 0 END) as current_month_cogs
,SUM(CASE WHEN [p21_sales_history_report_view].period = @current_month THEN ([detail_price])-([detail_cogs]) ELSE 0 END) as current_month_gm$
--,CASE WHEN [p21_sales_history_report_view].period = @current_month THEN ((SUM([detail_price])- SUM([detail_cogs]))/ NULLIF((SUM([detail_price])),0))*100 ELSE 0 END AS cuurent_month_gm_percent

--,SUM(CASE WHEN [p21_sales_history_report_view].period = @current_month THEN ((([detail_price])-([detail_cogs]))/([detail_price])*100) ELSE 0 END) as current_month_gm_percent
,@previous_month as previous_period
,SUM(CASE WHEN [p21_sales_history_report_view].period = @previous_month THEN [detail_price] ELSE 0 END) as previous_month_sales
,SUM(CASE WHEN [p21_sales_history_report_view].period = @previous_month THEN [detail_cogs] ELSE 0 END) as previous_month_cogs
,SUM(CASE WHEN [p21_sales_history_report_view].period = @previous_month THEN ([detail_price])-([detail_cogs]) ELSE 0 END) as previous_month_gm$
--,SUM(CASE WHEN [p21_sales_history_report_view].period = @previous_month THEN (([detail_price])-([detail_cogs]))/([detail_price])*100 ELSE 0 END) as previous_month_gm_percent
,@previous_2_months as previous_2_period
,SUM(CASE WHEN [p21_sales_history_report_view].period = @previous_2_months THEN [detail_price] ELSE 0 END) as previous_2_months_sales
,SUM(CASE WHEN [p21_sales_history_report_view].period = @previous_2_months THEN [detail_cogs] ELSE 0 END) as previous_2_months_cogs
,SUM(CASE WHEN [p21_sales_history_report_view].period = @previous_2_months THEN ([detail_price])-([detail_cogs]) ELSE 0 END) as previous_2_months_gm$
--,(CASE WHEN [p21_sales_history_report_view].period = @previous_2_months THEN (([detail_price])-([detail_cogs]))/([detail_price])*100 ELSE 0 END) as previous_2_months_gm_percent

FROM [P21].[dbo].[p21_sales_history_report_view]
LEFT OUTER JOIN hsc_p21_view_inv_mast ON [p21_sales_history_report_view].inv_mast_uid = hsc_p21_view_inv_mast.inv_mast_uid
LEFT OUTER JOIN p21_view_oe_hdr ON [p21_sales_history_report_view].order_no = p21_view_oe_hdr.order_no
WHERE

year_for_period = 2012
AND [p21_sales_history_report_view].parent_oe_line_uid = 0
AND (invoice_adjustment_type='C' AND [p21_sales_history_report_view].source_type_cd = 2638 OR NOT (invoice_adjustment_type = 'C' OR invoice_adjustment_type = 'D'))
--AND [p21_sales_history_report_view].vendor_consigned = 'N' AND projected_order = 'N' AND (detail_type IS NULL OR detail_type = 0)
--AND (progress_bill_flag = 'N' OR progress_bill_flag IS NULL )

GROUP BY

[branch_id]+' - '+[branch_description]

,COALESCE(p21_view_oe_hdr.class_2id,'STORE') WITH ROLLUP

) as sales_history

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-13 : 10:00:33

there's no need to do this. As you say you can always retrieve them in order you want. Didnt quite understand what you mean by

.However the problem I encounter is that some of the code because of the use and logic, it appear not following as an order

can you elaborate?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-13 : 10:07:10

I mean in order to get the formula for current_month_gross_margin_in_percent column I have to use subquery function as it is above. Then those gross_margin_in_percent column will apprear at the end of the table. Now I want them to locate as an order like current_month_sales, current_month_cog, current_month_gm,current_month_gm_in _percent. I can drag these columns as order I want and export it into excel, however because I want the data is updated every month so that I look for a way that query expresses the order as I wanted.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-13 : 10:33:24

why? there's no necessity that subquery have to be at end. you can have it in any position you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources