Query SQL - Rank employees / Columns with null values

Question

I need to fix the following SQL query:

SELECT  sep.name AS company_name,
    atnp.name AS employee_name,
    (Case WHEN ip.id_item = 0001 THEN 'Product A'  
        ELSE 'Product B'
        END ) AS product_name,
    (Case WHEN ivcp.id_item = 0001 THEN SUM(ivcp.quantity) 
        ELSE NULL
        END ) AS quantityA,
    (Case WHEN ivcp.id_item = 0002 THEN SUM(ivcp.quantity) 
        ELSE NULL
        END ) AS quantityB
FROM    sale_item AS ivcp
    INNER JOIN stock AS mep ON (mep.id_stock = ivcp.id_stock)
    INNER JOIN sale AS vcp ON (vcp.id_sale = ivcp.id_sale)
    INNER JOIN terminal_sale AS mvtp ON (mvtp.id_terminal_sale = vcp.id_terminal_sale)
    INNER JOIN item AS ip ON (ivcp.id_item = ip.id_item)
    INNER JOIN item_category AS cip ON (ip.id_category_item = cip.id_category_item)
    LEFT OUTER JOIN employee AS atnp ON (atnp.id_employee = ivcp.id_employee)
    INNER JOIN sis_company AS sep ON (sep.id_company = mvtp.id_company)
WHERE   mvtp.id_company IN (1)
AND mvtp.date_sale BETWEEN :BeginDate AND  :EndDate
AND DATE(ivcp.date_sale) BETWEEN :BeginDate AND  :EndDate
AND DATE(vcp.data_rec) BETWEEN :BeginDate AND  :EndDate
AND mep.date_sale BETWEEN :BeginDate AND  :EndDate
AND (-1 = -1 OR ivcp.id_employee IN
           (SELECT id_employee FROM team_employee WHERE id_employee = -1))
AND ip.id_category_item = 100
AND ivcp.id_item IN (0002,0001)
AND (ivcp.cancel = 'N' AND vcp.cancel = 'N')
GROUP BY sep.name,
    atnp.name,
    ip.id_item,
    ivcp.id_item
ORDER BY sep.name

Results:

COMPANY   | EMPLOYEE   | PRODUCT NAME | QUANTITY A | QUANTITY B
Company A | Employee A | Product A    | 1.64       | NULL
Company A | Employee A | Product B    | NULL       | 4.70
Company A | Employee B | Product A    | 84.191     | NULL
Company A | Employee B | Product B    | NULL       | 91.885

But I want this:

COMPANY   | EMPLOYEE   | QUANTITY A | QUANTITY B
Company A | Employee A | 1.64       | 4.70
Company A | Employee B | 84.191     | 91.885

• This query objective is to order some employees sales into a rank of best sellers. The rank will be ordered by the 'product A' and 'product B' sales (ivcp.quantity, id_item = 0001 and id_item = 0002).

• The inner joins are used to match the sales date, item category, employees ids, stock, etc.

I'm working with a Stimulsoft based program to design the report layout, but Im not very used to it. So if it's possible to solve the problem within the query, great!

Answer 1

It's basically a simplification of your query. While being at it, I fixed a couple of other things:

SELECT sc.name AS company_name
     , em.name AS employee_name
     , SUM((CASE WHEN si.id_item = 0001 THEN si.quantity END) AS quantity_a
     , SUM((CASE WHEN si.id_item = 0002 THEN si.quantity END) AS quantity_b
FROM   team_employee  te
JOIN   sale_item      si USING (id_employee)
JOIN   stock          st ON st.id_stock = si.id_stock
JOIN   sale           sa ON sa.id_sale = si.id_sale
JOIN   terminal_sale  ts ON ts.id_terminal_sale = sa.id_terminal_sale
JOIN   sis_company    sc ON sc.id_company = ts.id_company
JOIN   item           ip ON ip.id_item = si.id_item
JOIN   item_category  ic ON ic.id_category_item = ip.id_category_item
LEFT   JOIN employee  em ON em.id_employee = si.id_employee
WHERE  te.id_employee = -1
AND    st.date_sale BETWEEN :BeginDate AND :EndDate
AND    ts.date_sale BETWEEN :BeginDate AND :EndDate
AND    si.date_sale >= :BeginDate      -- inlude lower border
AND    si.date_sale <  (:EndDate + 1)  -- exclude upper border
AND    sa.data_rec  >= :BeginDate
AND    sa.data_rec  <  (:EndDate + 1)
AND    ts.id_company = 1
AND    ip.id_category_item = 100
AND    si.id_item IN (0002,0001)
AND    si.cancel = 'N'
AND    sa.cancel = 'N'
GROUP  BY 1, 2
ORDER  BY 3 DESC NULLS LAST
        , 4 DESC NULLS LAST;

• As to your question: revert the approach with like demonstrated: sum of a CASE statement instead of the other way round. And adapt GROUP BY and ORDER BY accordingly.
  In Postgres 9.4 you would use an aggregate FILTER:

  • How can I simplify this game statistics query?

• -1 = -1 OR ... was just noise. I removed it.

• Weird:

  AND   (ivcp.id_employee IN 
    (SELECT id_employee FROM team_employee WHERE id_employee = -1))
  

  Assuming -1 is supposed to be a parameter. Either way, I rewrote that into another join.

• Why the leading zeros in ip.id_item = 0001? If it's a number then drop the zeros. If it's a string add single quotes to make it a sting literal instead of a numeric literal.

• id_company IN (1) is just a convoluted way of saying id_company = 1.

• Why the cast DATE(si.date_sale)? The name suggests it's already a date. Either the name is misleading or the expression is. Either way, this is bad for performance because the expression is not sargable.
  Assuming it's a timestamp I rewrote it to be sargable. Likewise with sa.data_rec. Also assuming :EndDate is a date, so we can just add an integer: :EndDate + 1

• si.cancel = 'N' and sa.cancel = 'N' look like columns that should really be implemented as boolean.

• I adapted ORDER BY to your description:

  The rank will be ordered by the 'product A' and 'product B' sales

  In descending order NULL values come first unless you make it DESC NULLS LAST:

  • PostgreSQL sort by datetime asc, null first?