mardi 22 octobre 2019

how to check there is no dues in the following cte

the CTE works awesomely but i don't know where to put put if condition if the sale is first i want sum function works in that case if the their is more than 1 debit against each accountid

create procedure [dbo].[counter_bills]
@SDate date,
@enddate date
as 
begin

 WITH CUST
 AS
(SELECT customer.customername, customer.customercontact, customer.customeraddress, account.account_id,account.account_type ,customer.customerid FROM   account 
JOIN customer 
ON customer.customerid = account.customerid ),

journal 
 as
 (select SUM(journal_entry.debit)-sum(journal_entry.credit) as [dues] , journal_entry.account_id as [journal account id] from journal_entry group by journal_entry.account_id),

SALES
AS(
 SELECT sale.saleid, 
sale.totalpaid, 
Stock.itemname         AS item, 
invoice.saleprice       AS SalePrice, 
invoice.qty,
invoice.StockID     as [invoice Stock id],
invoice.saleprice     AS [invoice saleprice],  
sale.date, 
stock.size, 
sale.customerid     AS [cust sale id ]
FROM   invoice 
JOIN Stock 
ON invoice.StockID = Stock.StockID
JOIN sale 
ON invoice.saleid = sale.saleid
join Our_orders
on Stock.orderID = Our_orders.orderID
join vendor
on Our_orders.VendorID = vendor.VendorID WHERE  sale.date between @SDate and @enddate)

SELECT * FROM CUST
right JOIN SALES
ON CUST.customerid = SALES.[cust sale id ] 
left join
journal
on journal.[journal account id] = CUST.account_id order by SALES.SaleID desc ;
end

Aucun commentaire:

Enregistrer un commentaire