How to create a query that function as Vlook Up

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

How to create a query that function as Vlook Up

Author

Topic

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-09-10 : 01:32:47

Hi guys,

I'm working on this query and the table looks like this

Item id status sub_item_id
10001 NNU no_sub
10002 Overstock no_sub
10003 Dead 10001
10004 Dead no_sub
10005 NNU 10004

I 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_status
10001 NNU no_sub no_status
10002 Overstock no_sub no_status
10003 Dead 10001 NNU
10004 Dead no_sub no_status
10005 NNU 10004 Dead

So 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 DISTINCT
inv.*
,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 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(

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

) inv
LEFT OUTER JOIN p21_view_inv_sub sub ON inv.inv_mast_uid = sub.inv_mast_uid

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-10 : 07:06:16

I must admit that I didn't read through the code that you posted - the query below is based on your initial description and sample data.

SELECT
	a.Item,
	a.Status,
	a.sub_item_id,
	COALESCE(b.status,'no_status') AS sub_status
FROM
	YourTable a
	LEFT JOIN YourTable b ON
		a.sub_item_id = b.Item;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 10:17:54

is this always one level? or can the subitems recurse to multi level?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources