Get incorrect result in the query.

  • 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.

  • We need more information. Please provide DDL and sample data along with expected results for that sample data.

    Jared
    CE - Microsoft

  • 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.

  • 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

  • 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