How to return what the customer bought / paid for?

Question

How to return what the customer bought / paid for?

Navigation

#1 by (2 votes)

0

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)

sql sql-server

asked by anonymous 07.12.2018 / 22:52

1 answer

How do I get data from an Entry and add it to the Database (sqlite3) How to use async / await in methods that return void?

score 2 · Accepted Answer

Braian, this is a sketch of the code that returns the value of each request for each client and what has already been paid for each request.

-- 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 did not test; may contain error (s).

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

The code was built in a modular way, according to article " Modular Programming with Table Expressions (CTE) .