How to return what the customer bought/paid?

Question

I have the following tables: using JOIN

 CREATE TABLE tbCliente
( ClienteID INT IDENTITY(1,1) PRIMARY KEY,
  ClienteNome VARCHAR(50),
  ClienteTelefone VARCHAR(15),
  ClienteDataCadastro DATE)

 CREATE TABLE tbPagamento
( PagamentoID INT IDENTITY(1,1) PRIMARY KEY,
  ClienteID INT,
  PedidoID INT,
  PagamentoValor DECIMAL(8,2),
  PagamentoData DATE)

CREATE TABLE tbPedido
( PedidoID INT IDENTITY(1,1) PRIMARY KEY,
  PedidoData DATE,
  ClienteID INT)

CREATE TABLE tbProduto
( ProdutoID INT IDENTITY(1,1) PRIMARY KEY,
  ProdutoNome VARCHAR(50),
  ProdutoValor DECIMAL(5,2))

CREATE TABLE tbPedidoItem
( PedidoItemID INT IDENTITY(1,1) PRIMARY KEY,
  PedidoID INT,
  ProdutoID INT)

ALTER TABLE tbPagamento  WITH CHECK 
ADD  CONSTRAINT FK_tbPagamento_tbCliente
FOREIGN KEY(ClienteID) REFERENCES tbCliente (ClienteID)

ALTER TABLE tbPagamento  WITH CHECK 
ADD  CONSTRAINT FK_tbPagamento_tbPedido
FOREIGN KEY(PedidoID) REFERENCES tbPedido (PedidoID)

ALTER TABLE tbPedido  WITH CHECK 
ADD  CONSTRAINT FK_tbPedido_tbCliente
FOREIGN KEY(ClienteID) REFERENCES tbCliente (ClienteID)

ALTER TABLE tbPedidoItem  WITH CHECK 
ADD  CONSTRAINT FK_tbPedidoItem_tbPedido
FOREIGN KEY(PedidoID) REFERENCES tbPedido (PedidoID)

ALTER TABLE tbPedidoItem  WITH CHECK 
ADD  CONSTRAINT FK_tbPedidoItem_tbProduto
FOREIGN KEY(ProdutoID) REFERENCES tbProduto (ProdutoID)

Answer 1

Braian, here is a sketch of the code that returns the value of each customer’s order and what has already been paid for each order.

-- código #1
with 
Valor_Pedido_Cliente as (
SELECT Pd.ClienteID, Pd.PedidoID, 
       sum (Pr.ProdutoValor) as soma_Pedido
  from tbPedido as Pd
       inner join tbPedidoItem as PI on PI.PedidoID = Pd.PedidoID
       inner join tbProduto as Pr on Pr.ProdutoID = PI.ProdutoID
  group by Pd.ClienteID, Pd.PedidoID
),
Valor_Pgto_Cliente as (
SELECT Pg.ClienteID, Pg.PedidoID,
       sum (PagamentoValor) as soma_Pgto
  from tbPagamento as Pg
  group by Pg.ClienteID, Pg.PedidoID
)
SELECT T3.ClienteID, T3.ClienteNome, T3.ClienteTelefone,
       T1.PedidoID, T1.soma_Pedido, 
       coalesce (T2.soma_Pgto, 0) as soma_Pgto
  from Cliente as T3
       inner join Valor_Pedido_Cliente as T1 on T1.ClienteID = T3.ClienteID
       left join Valor_Pgto_Cliente as T2 on T2.ClienteID = T1.ClientID
                                             and T2.PedidoID = T1.PedidoID;

I have not tested; may contain error(s).

The first CTE, Valor_Pedido_Cliente, calculates the total value of each order. The second TEC, Valor_Pgto_Cliente, returns what each customer has paid for each order. The junction of the return of the two Tpcs generates the final report.

The code was built in a modular way, according to article "Modular programming with table expressions (CTE)”.