How to compare different value? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 compare different value?

Author

Topic

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-06-29 : 10:04:47

Hi guys,

I'm working on the project and need some help.

This is what I'm working on:
SELECT
[P21].[dbo].[p21_view_inventory_value_report].[default_branch_id]
,[P21].[dbo].[p21_view_inventory_value_report].[branch_description]
,[P21].[dbo].p21_view_inventory_value_report.[item_id]
,[P21].[dbo].[p21_view_inventory_value_report].[item_desc]
,([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) as 'total_qty'
,(([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty)-[P21].[dbo].p21_view_inv_loc.qty_allocated) AS 'total_available'
,[P21].[dbo].[p21_view_inventory_value_report].[cost]
,((([P21].[dbo].p21_view_inventory_value_report.qty_on_hand - [P21].[dbo].p21_view_inventory_value_report.special_layer_qty) * [P21].[dbo].[p21_view_inventory_value_report].[cost]) + [P21].[dbo].p21_view_inventory_value_report.special_layer_qty) AS extended_value
,(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -3, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, -1, getdate()),112),6) THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 3 months usage'
,(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -6, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, -1, getdate()),112),6)THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 6 months usage'
,(CASE WHEN [P21].[dbo].[p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -12, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, -1, getdate()),112),6)THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 12 months usage'

Now my boss asked me to compare the quantity on hand as ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) as 'total_qty') to the last 3, 6 , 12 months usage and classified their status as
good if quantity on hand <= 90 day supply.(90 day supply means 6 months usage)
Overstock if quantity on hand > 90 days supply and <= 365 days
NNU = 120 days
Surplus > 365 days supply
Hardcode = No usage 12 months.

What function should I use in SQL to be able to do this?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-29 : 10:14:50

I may not have the classifications exactly right in the query below (for example, I didn't quite follow how 90 days supply means six months usage, if that is what your business logic is, so it is!)

SELECT
	*,
	CASE 
		WHEN [Last 12 months usage] = 0 THEN 'No usage'
		WHEN [total_qty] <= [Last 6 months usage] THEN 'Good'
		WHEN [total_qty] > [Last 6 months usage] 
			AND [total_qty] <= [Last 12 months usage]THEN 'Overstock'
		ELSE 'Way too much'
	END
FROM
(
	-- Your Current Query here
)s

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-06-29 : 10:26:48

Sorry it means 3 months usage. I typed wrong.

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-06-29 : 10:47:12

Thank you very much. It worked. :D

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-29 : 11:54:29

You are very welcome.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources