none
ADDING AN ADDITIONAL FILTER WHILE JOINING TABLES

    Question

  • Hi. I may have asked this question before but I am still having trouble. I know this is probably very simple.

    I have two tables.. DEPARTMENTS and PRODUCTION_ITEMS and their schema is as follows:

    DEPARTMENTS PRODUCTION_ITEMS

    department_id department_id

    department_name item_id

    item_price

    item_status

    I am building a query in SSRS to pull the count of the distinct item_id's, the sum of the items prices between two dates. In addition, and separately, I need the count of those items that have a status of 'X'

    I have used the query below to get these other pieces but I am unable to get the count for those that have the status of 'X'. Can anyone provide me a clue as to where I am going wrong?

    SELECT  A.DEPARTMENT_ID, A.DEPARTMENT_NAME,
    COUNT(DISTINCT B.ITEM_ID) AS ITEMS_COUNT, SUM(ITEM_PRICE) AS TOTAL_PRICED
    FROM DEPARTMENTS A INNER JOIN PRODUCTION_ITEMS B ON A.DEPARTMENT_ID = B.DEPARTMENT_ID WHERE A.DEPARTMENT_ACTIVE = 'TRUE' AND B.TIME_STAMP BETWEEN @START_DATE AND @END_DATE
    GROUP BY A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.ITEM_ID
    ORDER BY A.DEPARTMENT_NAME ASC

    Thank you so much in advance!

    Patrick 


    Patrick Regis

    Saturday, September 07, 2013 10:12 PM

Answers

  • SELECT  A.DEPARTMENT_ID, A.DEPARTMENT_NAME,
    COUNT(DISTINCT B.ITEM_ID) AS ITEMS_COUNT, SUM(ITEM_PRICE) AS TOTAL_PRICED,
    SUM(CASE WHEN STATUS = 'X' THEN 1 ELSE 0 END) AS STATUS_X_COUNT
    FROM DEPARTMENTS A INNER JOIN PRODUCTION_ITEMS B ON A.DEPARTMENT_ID = B.DEPARTMENT_ID WHERE A.DEPARTMENT_ACTIVE = 'TRUE' AND B.TIME_STAMP BETWEEN @START_DATE AND @END_DATE
    GROUP BY A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.ITEM_ID
    ORDER BY A.DEPARTMENT_NAME ASC

    Tom
    • Proposed as answer by RSingh() Sunday, September 08, 2013 2:49 AM
    • Marked as answer by Patrick Regis Sunday, September 08, 2013 4:03 AM
    Sunday, September 08, 2013 1:43 AM

All replies

  • SELECT  A.DEPARTMENT_ID, A.DEPARTMENT_NAME,
    COUNT(DISTINCT B.ITEM_ID) AS ITEMS_COUNT, SUM(ITEM_PRICE) AS TOTAL_PRICED,
    SUM(CASE WHEN STATUS = 'X' THEN 1 ELSE 0 END) AS STATUS_X_COUNT
    FROM DEPARTMENTS A INNER JOIN PRODUCTION_ITEMS B ON A.DEPARTMENT_ID = B.DEPARTMENT_ID WHERE A.DEPARTMENT_ACTIVE = 'TRUE' AND B.TIME_STAMP BETWEEN @START_DATE AND @END_DATE
    GROUP BY A.DEPARTMENT_ID, A.DEPARTMENT_NAME, B.ITEM_ID
    ORDER BY A.DEPARTMENT_NAME ASC

    Tom
    • Proposed as answer by RSingh() Sunday, September 08, 2013 2:49 AM
    • Marked as answer by Patrick Regis Sunday, September 08, 2013 4:03 AM
    Sunday, September 08, 2013 1:43 AM
  • I knew it was going to be something simple :) thank you very much Tom!

    I have a second question if you don't mind? How would I also get the sum of the total of those items with the status = 'X'?

    Would it be.. 

    SUM(CASE WHEN STATUS = 'X' THEN ITEM_PRICE ELSE 0 END) AS STATUS_X_TOTAL

    Patrick Regis

    Sunday, September 08, 2013 4:06 AM
  • I have a second question if you don't mind? How would I also get the sum of the total of those items with the status = 'X'?

    Would it be.. 

    SUM(CASE WHEN STATUS = 'X' THEN ITEM_PRICE ELSE 0 END) AS STATUS_X_TOTAL


    Yes.

    Tom

    Sunday, September 08, 2013 5:05 AM