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