Get incorrect result - SQL Server Forums

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

Get incorrect result

Author

Topic

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-10 : 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'

In order 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 just want it to filter "0" for the last 3 months usage and I'm sure that my query is alright. I don't know why it no gave me the data that I wanted. Let me know if you guys found out the mistake on my query.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-10 : 11:30:26

I realy have no idea what you are trying to do. please post DDL, DML and expected output. The following link can help you with providing that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-10 : 11:37:29

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"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-10 : 12:34:54

again it makes no sense to someone who cant see your system neither knows about your tables. So please post sample data as per what Lamprey's posted link suggests if you need someone to help you out!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources