Untitled

 avataruser_6688396
plain_text
a month ago
1.3 kB
4
Indexable
Never
SELECT
  SUM(CASE WHEN record_type = 'SHA' THEN 1 ELSE 0 END) as qtd_compartilhamentos,
  SUM(CASE WHEN record_type = 'ACC' THEN 1 ELSE 0 END) as qtd_aceite_compartilhamento,
  SUM(CASE WHEN record_type = 'REF' THEN 1 ELSE 0 END) as qtd_recusa_compartilhamento,
  (
    SUM(CASE WHEN record_type = 'SHA' THEN 1 ELSE 0 END) - 
	(
	  SUM(CASE WHEN record_type = 'ACC' THEN 1 ELSE 0 END) +
	  SUM(CASE WHEN record_type = 'REF' THEN 1 ELSE 0 END)
    )  
  ) AS qtd_compatilhamento_pendentes  
FROM(
  SELECT
    ntf.work_item_id,
    ntf.record_type,
    ntf.origin_board_id, 
    ntf.target_board_id
  FROM
    notifications ntf
  
  WHERE ntf.record_type IN(
    'SHA', -- shared
    'ACC', -- accepted
    'REF' -- refused
    --SAW - sent information awaiting response
    --SAE - sent information accepted
    --SRF - sent information refused
  )
  --AND work_item_id = 568

  GROUP BY
    ntf.work_item_id,
    ntf.record_type,
    ntf.origin_board_id, 
    ntf.target_board_id
) foo
  
  
  /*
    SELECT
	  work_item_id,
	  record_type,
	  ROW_NUMBER() OVER (PARTITION BY work_item_id, record_type) AS linha
	FROM
      notifications
	--WHERE record_type = 'SHA'
	GROUP BY
	  work_item_id,
	  record_type
	  */