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  SQL Server 2000 Forums  Transact-SQL (2000)  Reorganizing views\tables into a columnar style

Author  Topic  Parkaw Starting Member 16 Posts Posted - 2005-06-03 : 17:21:05 Ok, so I am trying to Reorganize a view for item usage by period so that it just returns one row for each item. I've already done this before, but due to the scale of this, I was wondering if there was a more efficient way to do it than writing one view period and integrating the views in a hierarchical fashion. My boss wants me to pull usage data for 3 years, by month. So all told that would be about 38 views if I did it in the way I did it for my sales querys which I will post in this topic later. I have no problem doing it that way, and could have it done in about 6 hours. But I have this nagging sensation that there is a better way to do it. I will be using \\ for comments, and !<> for titles and such. !<Stock Status Query> SELECT item_id, item_desc, product_group_id, product_group_desc, SUM(qty_on_hand) AS [Total on hand], SUM(qty_backordered) AS [Total Back Ordered], supplier_id, supplier_name FROM dbo.TMC_view_stockstatus_report tmc_view_stockstatus_report GROUP BY item_id, product_group_id, product_group_desc, item_desc, supplier_id, supplier_name !<Sales data, current year> SUM(p21_view_invoice_line.qty_shipped) AS ['Total Shipped'] FROM dbo.p21_view_invoice_hdr p21_view_invoice_hdr INNER JOIN dbo.p21_view_invoice_line p21_view_invoice_line ON p21_view_invoice_hdr.invoice_no = p21_view_invoice_line.invoice_no GROUP BY p21_view_invoice_hdr.year_for_period, p21_view_invoice_line.item_id HAVING (p21_view_invoice_hdr.year_for_period = DATEPART([year], GETDATE())) //The ones for last year are the exact same query with the exception //of the DATEPART([year], GETDATE() - 365)) being different for each //These three are what is really bothering me, I know there has to be //a better way to do these. !<Stock by sales, query 1; title:TMC_s_x_s_1> SELECT dbo.TMC_Aggregate_Sales_data.item_id, dbo.TMC_Aggregate_Sales_data.[Total Shipped], dbo.TMC_Aggregate_Sales_data.[Last Order date], dbo.TMC_Aggregate_Sales_data.[Total sales], dbo.TMC_SALES_DATA_THISYEAR_x_ITEM.[Sum of extended_price] AS [Current year sales], dbo.TMC_SALES_DATA_THISYEAR_x_ITEM.['Total Shipped'] AS [YTD Shipped] FROM dbo.TMC_Aggregate_Sales_data LEFT OUTER JOIN dbo.TMC_SALES_DATA_THISYEAR_x_ITEM ON dbo.TMC_Aggregate_Sales_data.item_id = dbo.TMC_SALES_DATA_THISYEAR_x_ITEM.item_id !<Stock by sales, query 2; title:TMC_s_x_s_2> SELECT dbo.TMC_s_x_s_1.*, dbo.TMC_SALES_DATA_LASTYEAR_x_ITEM.[Sum of extended_price] AS [LYTD Sales], dbo.TMC_SALES_DATA_LASTYEAR_x_ITEM.['Total Shipped'] AS [LYTD Shipped] FROM dbo.TMC_s_x_s_1 LEFT OUTER JOIN dbo.TMC_SALES_DATA_LASTYEAR_x_ITEM ON dbo.TMC_s_x_s_1.item_id = dbo.TMC_SALES_DATA_LASTYEAR_x_ITEM.item_id !<Stock by sales, query 3; title:TMC_s_x_s_3> SELECT dbo.TMC_s_x_s_2.*, dbo.TMC_SALES_DATA_YEAR_before_last_x_ITEM.[Sum of extended_price] AS [YBL Sales], dbo.TMC_SALES_DATA_YEAR_before_last_x_ITEM.['Total Shipped'] AS [YBL Shipped] FROM dbo.TMC_s_x_s_2 LEFT OUTER JOIN dbo.TMC_SALES_DATA_YEAR_before_last_x_ITEM ON dbo.TMC_s_x_s_2.item_id = dbo.TMC_SALES_DATA_YEAR_before_last_x_ITEM.item_id //query 1 is current year, 2 is last year, 3 is year before last. //And this is the one that ties it all together. !<Stock by Sales Analysis> SELECT dbo.TMC_stock_x_status_qry_1.item_id, dbo.TMC_stock_x_status_qry_1.item_desc, dbo.TMC_stock_x_status_qry_1.product_group_id, dbo.TMC_stock_x_status_qry_1.product_group_desc, dbo.TMC_stock_x_status_qry_1.[Total on hand], dbo.TMC_stock_x_status_qry_1.[Total Back Ordered], dbo.TMC_stock_x_status_qry_1.supplier_id, dbo.TMC_stock_x_status_qry_1.supplier_name, dbo.TMC_s_x_s_3.[Total Shipped], dbo.TMC_s_x_s_3.[Last Order date], dbo.TMC_s_x_s_3.[Total sales], dbo.TMC_s_x_s_3.[Current year sales] AS [YTD Sales], dbo.TMC_s_x_s_3.[YTD Shipped], dbo.TMC_s_x_s_3.[LYTD Sales], dbo.TMC_s_x_s_3.[LYTD Shipped], dbo.TMC_s_x_s_3.[YBL Sales], dbo.TMC_s_x_s_3.[YBL Shipped] FROM dbo.TMC_s_x_s_3 RIGHT OUTER JOIN Anyone know of a help topic in the MS SQL Server 2000 Query Analyizer Help that would... well... help with this? Thank you for your time; Signed sincerely, Aaron W. Park