Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Create a query that function as Vlook up in Excel Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, September 9, 2012 11:35 PM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, November 28, 2012 2:54 PM Points: 25, Visits: 47
 This is an extremely convoluted query.However, breaking it up using a CTE, we can reduce the complexity a bit to something in the line of:`DECLARE @current_year_and_period INT;DECLARE @prev_3_months_year_and_period INT;DECLARE @prev_6_months_year_and_period INT;DECLARE @prev_9_months_year_and_period INT;DECLARE @prev_12_months_year_and_period INT;DECLARE @prev_24_months_year_and_period INT;SET @current_year_and_period = CAST(LEFT(CONVERT(CHAR(8), DATEADD(MM, 0, getdate()),112), 6) AS INT);SET @prev_3_months_year_and_period = CAST(LEFT(CONVERT(CHAR(8), DATEADD(MM, -3, getdate()),112),6) AS INT);SET @prev_6_months_year_and_period = CAST(LEFT(CONVERT(CHAR(8), DATEADD(MM, -6, getdate()),112),6) AS INT);SET @prev_9_months_year_and_period = CAST(LEFT(CONVERT(CHAR(8), DATEADD(MM, -9, getdate()),112),6) AS INT);SET @prev_12_months_year_and_period = CAST(LEFT(CONVERT(CHAR(8), DATEADD(MM, -12, getdate()),112),6) AS INT);SET @prev_24_months_year_and_period = CAST(LEFT(CONVERT(CHAR(8), DATEADD(MM, -24, getdate()),112),6) AS INT);WITH inv_history AS ( SELECT inv_value.company_id ,inv_value.default_branch_id ,inv_value.branch_description ,inv_value.item_id ,inv_value.[inv_mast_uid] ,inv_value.[item_desc] ,(inv_value.[qty_on_hand]) ,((inv_value.[qty_on_hand]- inv_loc.qty_allocated)) AS 'total_available' ,inv_loc.moving_average_cost ,(((inv_value.qty_on_hand - inv_value.special_layer_qty) * inv_value.[cost]) + inv_value.special_layer_value) AS extended_value ,CAST(ROUND(inv_loc.qty_allocated * inv_loc.moving_average_cost,2)AS DECIMAL (38,2)) AS allocated_value ,SUM ( CASE WHEN [p21_inventory_usage_all_view].year_and_period BETWEEN @prev_3_months_year_and_period AND @current_year_and_period THEN [p21_inventory_usage_all_view].actual_usage ELSE CAST(0 AS DECIMAL(19, 9)) END ) AS previous_3_month_useage ,SUM ( CASE WHEN [p21_inventory_usage_all_view].year_and_period BETWEEN @prev_6_months_year_and_period AND @current_year_and_period THEN [p21_inventory_usage_all_view].actual_usage ELSE CAST(0 AS DECIMAL(19, 9)) END ) AS previous_6_month_useage ,SUM ( CASE WHEN [p21_inventory_usage_all_view].year_and_period BETWEEN @prev_9_months_year_and_period AND @current_year_and_period THEN [p21_inventory_usage_all_view].actual_usage ELSE CAST(0 AS DECIMAL(19, 9)) END ) AS previous_9_month_useage ,SUM ( CASE WHEN [p21_inventory_usage_all_view].year_and_period BETWEEN @prev_12_months_year_and_period AND @current_year_and_period THEN [p21_inventory_usage_all_view].actual_usage ELSE CAST(0 AS DECIMAL(19, 9)) END ) AS previous_12_month_useage ,SUM ( CASE WHEN [p21_inventory_usage_all_view].year_and_period BETWEEN @prev_24_months_year_and_period AND @current_year_and_period THEN [p21_inventory_usage_all_view].actual_usage ELSE CAST(0 AS DECIMAL(19, 9)) END ) AS previous_24_month_useage ,p21_view_address.name AS supplier_name ,inv_loc.last_purchase_date ,p21_view_inv_mast.product_type ,inv_loc.stockable ,inv_loc.date_created ,inv_loc.period_first_stocked ,inv_loc.year_first_stocked ,inv_loc.replenishment_method ,inv_loc.inv_min ,inv_loc.inv_max ,class.class_description ,CAST( LEFT(CONVERT(CHAR (8),(inv_loc.period_first_stocked+(inv_loc.year_first_stocked*100)),120),6) AS INT) AS first_stocked ,@current_year_and_period AS current_year_and_period ,@prev_3_months_year_and_period AS prev_3_months_year_and_period FROM p21_view_inventory_value_report AS inv_value LEFT OUTER JOIN p21_view_inv_loc AS inv_loc ON (inv_loc.inv_mast_uid = inv_value.inv_mast_uid) AND (inv_loc.location_id = inv_value.location_id) LEFT OUTER JOIN [p21_inventory_usage_all_view] ON (inv_value.inv_mast_uid = [p21_inventory_usage_all_view].inv_mast_uid AND inv_value.location_id = [p21_inventory_usage_all_view].location_id) LEFT OUTER JOIN p21_view_inv_mast ON inv_value.inv_mast_uid = p21_view_inv_mast.inv_mast_uid LEFT OUTER JOIN p21_view_address ON inv_value.primary_supplier_id = p21_view_address.id LEFT OUTER JOIN p21_view_class AS class ON p21_view_inv_mast.class_id3 = class.class_id LEFT OUTER JOIN p21_view_inv_sub ON p21_view_inv_sub.inv_mast_uid = inv_value.inv_mast_uid GROUP BY inv_value.company_id ,inv_value.[default_branch_id] ,inv_value.[branch_description] ,inv_value.[item_id] ,inv_value.[inv_mast_uid] ,inv_value.[item_desc] ,(inv_value.[qty_on_hand]) ,((inv_value.[qty_on_hand]-inv_loc.qty_allocated)) ,inv_loc.moving_average_cost ,(((inv_value.[qty_on_hand] - inv_value.special_layer_qty) * inv_value.[cost]) + inv_value.special_layer_value ) ,inv_loc.qty_allocated * inv_loc.moving_average_cost ,inv_loc.stockable ,(p21_view_inv_mast.product_type) ,p21_view_address.name ,inv_loc.last_purchase_date ,inv_loc.date_created ,inv_loc.period_first_stocked+inv_loc.year_first_stocked ,inv_loc.period_first_stocked ,inv_loc.year_first_stocked ,inv_loc.replenishment_method ,inv_loc.inv_min ,inv_loc.inv_max ,class.class_description ), inventory_project AS ( SELECT default_branch_id ,branch_description ,inv_mast_uid ,item_id ,item_desc ,qty_on_hand ,total_available ,moving_average_cost ,extended_value ,allocated_value ,supplier_name ,replenishment_method ,inv_min ,inv_max ,previous_3_month_useage ,previous_6_month_useage ,previous_12_month_useage ,previous_24_month_useage ,class_description as equipment_flag ,last_purchase_date ,CASE WHEN date_created >= DATEADD(MM, -3, GETDATE()) AND previous_3_month_useage = 0 OR previous_24_month_useage <=0 AND last_purchase_date >= @prev_3_months_year_and_period THEN 'F Fresh No Usage' WHEN previous_24_month_useage <= 0 THEN 'E Really Dead (24)' WHEN previous_12_month_useage <= 0 THEN 'D Dead (12)' WHEN (qty_on_hand > previous_3_month_useage) and (qty_on_hand <= previous_12_month_useage) THEN 'B Overstock (3-12)' WHEN qty_on_hand <= previous_3_month_useage THEN 'A Good (QOH less than 3)' ELSE 'C Surplus (QOH > 12)' END AS [status] ,CASE WHEN date_created >= DATEADD(MM, -3, GETDATE()) AND previous_3_month_useage = 0 OR previous_24_month_useage <=0 AND last_purchase_date >= @prev_3_months_year_and_period THEN 0 WHEN previous_24_month_useage = 0 THEN 730 WHEN previous_12_month_useage = 0 THEN 365 ELSE CAST(ROUND(((qty_on_hand * 365) / previous_12_month_useage),2) AS DECIMAl (38,2)) END AS [DIO] ,CASE WHEN previous_12_month_useage = 0 THEN 0 WHEN moving_average_cost= 0 THEN 0 WHEN extended_value = 0 THEN 0 ELSE CAST(ROUND( ((previous_12_month_useage * moving_average_cost )/extended_value ),2) AS DECIMAL (38,2)) END AS [Turn] ,inv_history.date_created FROM inv_history ) , inv AS ( Select DISTINCT inventory_project.* ,CASE WHEN sub.inv_mast_uid = inventory_project.inv_mast_uid Then sub.sub_item_id Else 'no_sub' END as subsitute FROM inventory_project LEFT OUTER JOIN p21_view_inv_sub sub ON inventory_project.inv_mast_uid = sub.inv_mast_uid )SELECT DISTINCTinv.*,CASE WHEN inv.subsitute <> 'no_sub' AND sub.sub_item_id IS NOT NULL THEN inv.status ELSE 'no_status' END sub_statusFROM invLEFT OUTER JOIN inv sub ON inv.inv_mast_uid = sub.sub_item_id `Another note:Given you are using GROUP BY's in your queries, is the use of DISTINCT also required? ____________________________________________Space, the final frontier? not any more...All limits henceforth are self-imposed.“libera tute vulgaris ex”