July 10, 2012 at 9:10 am
Posted - 07/10/2012 : 10:57:30
--------------------------------------------------------------------------------
Hey guys,
I'm working on these cases:
p21_view_inventory_value_report.[item_id]
,SUM (CASE WHEN [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, 0, getdate()),112),6) THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 3 months usage'
,SUM (CASE WHEN [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, 0, getdate()),112),6)THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 6 months usage'
,SUM (CASE WHEN [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, 0, getdate()),112),6)THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 12 months usage'
,(CASE
WHEN (p21_view_inv_mast.product_type) = 'T' THEN 'Temporary'
WHEN ([p21_view_inv_loc].stockable) = 'N' THEN 'Non-Stockable'
WHEN (SUM (CASE WHEN [p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -9, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, 0, getdate()),112),6)THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END) = 0)
THEN 'No Usage 9 Months'
WHEN (SUM (CASE WHEN [P21].[dbo].[p21_view_inv_loc].date_created <= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-3,0) THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END)= 0) THEN 'NNU'
ELSE 'Good' END) As 'Status'
I want to set up these "NNU" to be 0 for the last 3 months usage, but when I run the query it still shows the last 3 months usage other positive number but 0. I believe my query formular is alright and it should give me only 0 data for the last 3 months usage and classified it as NNU.
[P21].[dbo].[p21_view_inv_loc].date_created is very important for me to use it as a filter because my boss wants me to use it as an condition to get 0 data for NNU in the last 3 months usage.
July 10, 2012 at 9:18 am
We need more information. Please provide DDL and sample data along with expected results for that sample data.
Jared
CE - Microsoft
July 10, 2012 at 9:26 am
The data result should look like this
Last 3 month usage (column) Status( column)
0 NNU
20 Good
175 Temporary
0
78 Non- Stockable
There are several rows in the last 3 months usage not equal 0 and still classified as "NNU" as you can see I want NNU classify based on the last 3 month usage = 0.
July 10, 2012 at 9:35 am
SQLKnowItAll (7/10/2012)
We need more information. Please provide DDL and sample data
See the link in my signature by Jeff Moden if you need examples on how to post here.
Jared
CE - Microsoft
July 10, 2012 at 9:36 am
This is what I'm working on in the query:
SELECT
p21_view_inventory_value_report.[item_id]
,SUM (CASE WHEN [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, 0, getdate()),112),6) THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 3 months usage'
,SUM (CASE WHEN [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, 0, getdate()),112),6)THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 6 months usage'
,SUM (CASE WHEN [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, 0, getdate()),112),6)THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END) AS 'Last 12 months usage'
,(CASE
--WHEN (p21_view_inv_mast.product_type) = 'T' THEN 'Temporary'
--WHEN ([p21_view_inv_loc].stockable) = 'N' THEN 'Non-Stockable'
--WHEN (SUM (CASE WHEN [p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -9, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, 0, getdate()),112),6)THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END) = 0)
--THEN 'No Usage 9 Months'
WHEN (SUM (CASE WHEN [P21].[dbo].[p21_view_inv_loc].date_created <= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-3,0) THEN [P21].[dbo].[p21_inventory_usage_all_view].actual_usage ELSE 0 END)= 0) THEN 'NNU'
ELSE 'Good' END) As 'Status'
FROM
[p21_view_inventory_value_report]
LEFT OUTER JOIN p21_view_inv_loc ON (p21_view_inv_loc.inv_mast_uid = p21_view_inventory_value_report.inv_mast_uid) AND
(p21_view_inv_loc.location_id = p21_view_inventory_value_report.location_id)
LEFT OUTER JOIN [p21_inventory_usage_all_view] ON ([p21_view_inventory_value_report].inv_mast_uid = [p21_inventory_usage_all_view].inv_mast_uid AND
[p21_view_inventory_value_report].location_id = [p21_inventory_usage_all_view].location_id)
LEFT OUTER JOIN p21_view_inv_mast ON p21_view_inventory_value_report.inv_mast_uid = p21_view_inv_mast.inv_mast_uid
LEFT OUTER JOIN p21_view_address ON p21_view_inventory_value_report.primary_supplier_id = p21_view_address.id
GROUP BY
p21_view_inventory_value_report.[item_id]
The query run successfully but there are data from the last 3 month usage column rather than 0 still classified as " NNU"
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply