InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 21:08:49 GMT20http://www.sqlservercentral.com/Forums/Topic1356562-391-1.aspxThis is an extremely convoluted query.However, breaking it up using a [url=http://msdn.microsoft.com/en-us/library/ms175972.aspx]CTE[/url], we can reduce the complexity a bit to something in the line of:[code="sql"]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 [/code]Another note:Given you are using GROUP BY's in your queries, is the use of DISTINCT also required?Mon, 10 Sep 2012 03:30:28 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1356562-391-1.aspxHi guys,I'm working on this query and the table looks like thisItem id status sub_item_id 10001 NNU no_sub 10002 Overstock no_sub10003 Dead 1000110004 Dead no_sub10005 NNU 10004I want to create one more column as status that basically need to match with the item id because the sub_item_id is also the item id. I want the table look like this Item id status sub_item_id sub_status10001 NNU no_sub no_status10002 Overstock no_sub no_status10003 Dead 10001 NNU10004 Dead no_sub no_status10005 NNU 10004 DeadSo any item_id that has sub_item_id then that sub_item_id's status has to match exactly with the status come from the first two column. I basically create sub_item_id from CASE WHEN function and think will do the same with the sub_status but it give me wrong data my query is very long I can not cut and paste to this post for you guys to see it clear but if you guys know how to create a sub_status like a Vlookup function of the first two columns of the table in excel, please let me know. I read some stuffs online about using Inner join but I don't think it works in my case as I tried. I also tried to put my query for you guys to see how I made it, but I know this is a long and complicated query so let me know what you guys think .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); SELECT DISTINCTinv.*,CASE WHEN inv.subsitute <> 'no_sub' AND sub.sub_item_id = inv.item_id THEN inv.status ELSE 'no_status' END sub_status ( this is where I tried to create sub_status but not correct) FROM(Select DISTINCTinventory_project.*,CASE WHEN sub.inv_mast_uid = inventory_project.inv_mast_uid Then sub.sub_item_id Else 'no_sub' END as subsitute FROM( 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 ( 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 ) AS inv_history ) AS inventory_project LEFT OUTER JOIN p21_view_inv_sub sub ON inventory_project.inv_mast_uid = sub.inv_mast_uid ) invLEFT OUTER JOIN p21_view_inv_sub sub ON inv.inv_mast_uid = sub.inv_mast_uidSun, 09 Sep 2012 23:35:35 GMTtantcu