Joing two Case When Statement as one

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

Joing two Case When Statement as one

Author

Topic

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-02 : 15:46:22

Hey guys,

I am trying to joing these case when statement as one. Please help.

This is what I'm working on:

...
,CASE
WHEN (SUM (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) = 0) THEN 'Hardcore'
WHEN ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) <= SUM(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) THEN 'Good'
WHEN ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) <= SUM (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) THEN 'NNU'
WHEN ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) > SUM(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)
AND ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty)<= SUM(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)THEN 'Overstock'
ELSE 'Surplus' END As 'Status'

This is what my boss asks me to join this statement into the case when statement above.

(CASE WHEN m_IN_INVENTORY.QtyOnHand_Numeric > 0
AND m_IN_INVENTORY.DateLastSale between CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,730, getdate()),0),120) AND CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,365, getdate()),0),120)
AND m_IN_INVENTORY.DateLastReceipt between CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,730, getdate()),0),120) AND CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,365, getdate()),0),120)
AND m_IN_INVENTORY.AddedToFile between CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,730, getdate()),0),120) AND CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,365, getdate()),0),120)
AND m_IN_INVENTORY.Department Between '05' And '99'
AND m_IN_INVENTORY.KeepStockInfo = 'Y'
AND m_IN_INVENTORY.KitRecord = 'N'
AND P21.dbo.p21_view_inv_loc.last_sale_date < '2011-09-01'
AND P21.dbo.p21_view_inv_loc.last_purchase_date IS NULL
AND P21.dbo.p21_view_inv_loc.date_created < '2011-09-01'
AND [QtyOnHand_Numeric]- P21.dbo.p21_view_inv_loc.qty_on_hand = 0
THEN ((([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)
ELSE 0
END) as 'No Sales > 1yrs'

This is what I tried so far but it was not working out.

,CASE
WHEN (SUM (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) = 0) THEN 'Hardcore'
WHEN ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) <= SUM(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) THEN 'Good'
WHEN ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) <= SUM (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) THEN 'NNU'
WHEN ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) > SUM(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)
AND ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty)<= SUM(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)THEN 'Overstock'
WHEN (EagleSQL.dbo.m_IN_INVENTORY.QtyOnHand_Numeric > 0
AND EagleSQL.dbo.m_IN_INVENTORY.DateLastSale between CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,730, getdate()),0),120) AND CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,365, getdate()),0),120)
AND EagleSQL.dbo.m_IN_INVENTORY.DateLastReceipt between CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,730, getdate()),0),120) AND CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,365, getdate()),0),120)
AND EagleSQL.dbo.m_IN_INVENTORY.AddedToFile between CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,730, getdate()),0),120) AND CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,365, getdate()),0),120)
AND EagleSQL.dbo.m_IN_INVENTORY.Department Between '05' And '99'
AND EagleSQL.dbo.m_IN_INVENTORY.KeepStockInfo = 'Y'
AND EagleSQL.dbo.m_IN_INVENTORY.KitRecord = 'N'
AND P21.dbo.p21_view_inv_loc.last_sale_date < '2011-09-01'
AND P21.dbo.p21_view_inv_loc.last_purchase_date IS NULL
AND P21.dbo.p21_view_inv_loc.date_created < '2011-09-01'
AND [QtyOnHand_Numeric]- P21.dbo.p21_view_inv_loc.qty_on_hand = 0
THEN ((([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)
ELSE 0
END) THEN 'No Sales > 1yrs'

ELSE 'Surplus' END As 'Status'

Let me know if you need more information.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-02 : 17:17:21

One returns a string the other a number. Since they are different datatypes, how do you (or your boss) want to handle that? I assume you'd want to cast the number to a string. In that case, you can cant the result of the second Case expression to a string and add it to the first case expression. Something like this maybe?:

CASE 
WHEN (SUM (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) = 0) THEN 'Hardcore'
WHEN ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) <= SUM(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) THEN 'Good'
WHEN ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) <= SUM (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) THEN 'NNU'
WHEN ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) > SUM(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)
AND ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty)<= SUM(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)THEN 'Overstock'
WHEN (EagleSQL.dbo.m_IN_INVENTORY.QtyOnHand_Numeric > 0 
AND EagleSQL.dbo.m_IN_INVENTORY.DateLastSale between CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,730, getdate()),0),120) AND CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,365, getdate()),0),120) 
AND EagleSQL.dbo.m_IN_INVENTORY.DateLastReceipt between CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,730, getdate()),0),120) AND CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,365, getdate()),0),120) 
AND EagleSQL.dbo.m_IN_INVENTORY.AddedToFile between CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,730, getdate()),0),120) AND CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,365, getdate()),0),120) 
AND EagleSQL.dbo.m_IN_INVENTORY.Department Between '05' And '99' 
AND EagleSQL.dbo.m_IN_INVENTORY.KeepStockInfo = 'Y'
AND EagleSQL.dbo.m_IN_INVENTORY.KitRecord = 'N'
AND P21.dbo.p21_view_inv_loc.last_sale_date < '2011-09-01'
AND P21.dbo.p21_view_inv_loc.last_purchase_date IS NULL 
AND P21.dbo.p21_view_inv_loc.date_created < '2011-09-01'
AND [QtyOnHand_Numeric]- P21.dbo.p21_view_inv_loc.qty_on_hand = 0
THEN 
CAST
(
	((([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 VARCHAR(50)
)
ELSE 'Surplus' END As 'Status'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-02 : 17:18:55

Or is it even more simple?

CASE 
WHEN (SUM (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) = 0) THEN 'Hardcore'
WHEN ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) <= SUM(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) THEN 'Good'
WHEN ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) <= SUM (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) THEN 'NNU'
WHEN ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty) > SUM(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)
AND ([P21].[dbo].p21_view_inventory_value_report.[qty_on_hand]+[P21].[dbo].p21_view_inventory_value_report.special_layer_qty)<= SUM(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)THEN 'Overstock'
WHEN (EagleSQL.dbo.m_IN_INVENTORY.QtyOnHand_Numeric > 0 
AND EagleSQL.dbo.m_IN_INVENTORY.DateLastSale between CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,730, getdate()),0),120) AND CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,365, getdate()),0),120) 
AND EagleSQL.dbo.m_IN_INVENTORY.DateLastReceipt between CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,730, getdate()),0),120) AND CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,365, getdate()),0),120) 
AND EagleSQL.dbo.m_IN_INVENTORY.AddedToFile between CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,730, getdate()),0),120) AND CONVERT(CHAR(10), DATEADD(MM, DATEDIFF (MM,365, getdate()),0),120) 
AND EagleSQL.dbo.m_IN_INVENTORY.Department Between '05' And '99' 
AND EagleSQL.dbo.m_IN_INVENTORY.KeepStockInfo = 'Y'
AND EagleSQL.dbo.m_IN_INVENTORY.KitRecord = 'N'
AND P21.dbo.p21_view_inv_loc.last_sale_date < '2011-09-01'
AND P21.dbo.p21_view_inv_loc.last_purchase_date IS NULL 
AND P21.dbo.p21_view_inv_loc.date_created < '2011-09-01'
AND [QtyOnHand_Numeric]- P21.dbo.p21_view_inv_loc.qty_on_hand = 0
THEN 'No Sales > 1yrs'
ELSE 'Surplus' END As 'Status'

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-03 : 09:16:28

Thanks for the help but it is not working at all. Here is the message.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near 'P21'.
Msg 102, Level 15, State 1, Line 89
Incorrect syntax near 'P21'.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-03 : 11:08:36

There was a parentheses that was not removed or left out depending on how you look at it. Just remove the parentheses at the start of the last WHEN:

WHEN ( EagleSQL.dbo.m_IN_INVENTORY.QtyOnHand_Numeric > 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources