CODEWITHSUNDEEP
sqlsql-server
CrackkiiCrazy
CrackkiiCrazy

Reputation: 13

How to split data in SQL

I have the following code:

select
  FeeEarnerID, 
  (
    select
      (select [name] from [User] AS u where u.userid=f.userid)
    from
      feeearner AS f
    where
      f.FeeEarnerID=aa.FeeEarnerID
  ) FeeEarner, 
  sum(aa.FEES) Fees,
  sum(aa.DISB) Disbursements,
  sum(aa.CREDITORS) Creditors
from
(
  SELECT
    FeeEarner.FeeEarnerID, 
    case when WIPTransaction.WIPTransactionTypeID IN (1,17,18,20,21,25)  then WIPTransaction.Amount else 0 end 'FEES',
    case when WIPTransaction.WIPTransactionTypeID IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,26,27,28,29)  then WIPTransaction.Amount else 0 end 'DISB',
    case when WIPTransaction.WIPTransactionTypeID IN (24)  then WIPTransaction.Amount else 0 end 'CREDITORS'
  FROM
  (
    (
      FeeEarner
    JOIN
      WIPTransaction ON FeeEarner.FeeEarnerID = WIPTransaction.FeeEarnerID
    )
  JOIN
    WIPTransactionType ON WIPTransactionType.WIPTransactionTypeID = WIPTransaction.WIPTransactionTypeID
  )
  WHERE
    (WIPTransaction.TransactionDate BETWEEN '2020-10-01' AND '2020-12-31')
)
  AS aa
group by
  FeeEarnerID

Used Table names: WIPtransaction, WIPtransactiontype, Feeearner

I want to display two more columns at the end of the output, namely: Invoiced and Uninvoiced. The "Invoicenumber" field in the "WIPtransaction" database will be tested for this. If the "Invoicenumber" is NULL - the transaction amount will be added to a sum in the uninvoiced column and if "Invoicenumber" contains a number - the transaction amount will be added to a sum in the invoiced column.

What is the code that I would need to write and where would it be placed?

Upvotes: 1

Views: 123

Answers (2)

Charlieface
Charlieface

Reputation: 72470

You can remove your derived query and combine it all into one. The FeeEarner double sub-query can also be optimized:

select
  FeeEarnerID, 
  (
    select [name] from [User] AS u where u.userid=FeeEarner.userid
  ) FeeEarner, 
  sum(case when WIPTransaction.WIPTransactionTypeID IN (1,17,18,20,21,25)  then WIPTransaction.Amount else 0 end) Fees,
  sum(case when WIPTransaction.WIPTransactionTypeID IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,26,27,28,29)  then WIPTransaction.Amount else 0 end) Disbursements,
  sum(case when WIPTransaction.WIPTransactionTypeID IN (24)  then WIPTransaction.Amount else 0 end) Creditors,
  SUM(CASE WHEN WIPTransaction.Invoicenumber IS NOT NULL THEN WIPTransaction.Amount END)   AS   InvoicedAmount,
  SUM(CASE WHEN WIPTransaction.Invoicenumber IS     NULL THEN WIPTransaction.Amount END)   AS UnInvoicedAmount
FROM
    FeeEarner
JOIN
    WIPTransaction ON FeeEarner.FeeEarnerID = WIPTransaction.FeeEarnerID
JOIN
    WIPTransactionType ON WIPTransactionType.WIPTransactionTypeID = WIPTransaction.WIPTransactionTypeID
WHERE
    WIPTransaction.TransactionDate BETWEEN '2020-10-01' AND '2020-12-31'
group by
  FeeEarnerID;

Upvotes: 0

MatBailie
MatBailie

Reputation: 86798

select
  FeeEarnerID, 
  (
    select
      (select [name] from [User] AS u where u.userid=f.userid)
    from
      feeearner AS f
    where
      f.FeeEarnerID=aa.FeeEarnerID
  ) FeeEarner, 
  sum(aa.FEES) Fees,
  sum(aa.DISB) Disbursements,
  sum(aa.CREDITORS) Creditors,
  ----------
  SUM(  InvoicedAmount)   AS   InvoicedAmount,
  SUM(UnInvoicedAmount)   AS UnInvoicedAmount
  ----------
from
(
  SELECT
    FeeEarner.FeeEarnerID, 
    case when WIPTransaction.WIPTransactionTypeID IN (1,17,18,20,21,25)  then WIPTransaction.Amount else 0 end 'FEES',
    case when WIPTransaction.WIPTransactionTypeID IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,26,27,28,29)  then WIPTransaction.Amount else 0 end 'DISB',
    case when WIPTransaction.WIPTransactionTypeID IN (24)  then WIPTransaction.Amount else 0 end 'CREDITORS',
    ----------
    CASE WHEN WIPTransaction.Invoicenumber IS NOT NULL THEN WIPTransaction.Amount END   AS InvoicedAmount,
    CASE WHEN WIPTransaction.Invoicenumber IS     NULL THEN WIPTransaction.Amount END   AS UnInvoicedAmount
    ----------
  FROM
    FeeEarner
  JOIN
    WIPTransaction ON FeeEarner.FeeEarnerID = WIPTransaction.FeeEarnerID
  JOIN
    WIPTransactionType ON WIPTransactionType.WIPTransactionTypeID = WIPTransaction.WIPTransactionTypeID
  WHERE
    WIPTransaction.TransactionDate BETWEEN '2020-10-01' AND '2020-12-31'
)
  AS aa
group by
  FeeEarnerID

Upvotes: 1

Related Questions