Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create a query that function as Vlook up in Excel


Create a query that function as Vlook up in Excel

Author
Message
tantcu
tantcu
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 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
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6708 Visits: 7206
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 DISTINCT
inv.*
,CASE WHEN inv.subsitute <> 'no_sub' AND sub.sub_item_id IS NOT NULL THEN inv.status ELSE 'no_status' END sub_status


FROM
inv
LEFT 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”
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search