USE [FISHCO_21_jan_2014_tly]
GO
/****** Object: StoredProcedure [dbo].[sp_Sales_Report_Fishco] Script Date: 01/22/2014 16:07:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_Sales_Report_Fishco]
@TrDate Datetime = null,
@Salesman varchar(50)
As
begin
Declare @SupplierId int
Set @SupplierId = (Select Salesman_ID From Salesman Where Salesman_Name = @Salesman)
IF EXISTS (SELECT [Customer_Account_ID] from Acc_Customer as AC
Left Join Salesman On AC.Salesman_Id = Salesman.Salesman_ID
Where AC.Transaction_Date = @TrDate And Salesman.Salesman_Name = @Salesman And AC.Debit > 0)
Begin
select
AC.[Customer_ID]
,[Customer_Name]
,AC.[Receipt_No]
,AC.[Transaction_Date]
,AC.[Particulars]
,AC.[Debit]
,sum(AC.[Credit]) as payment
,AC.[Narration]
,[Sales_Master_ID]
,[Sales_Return_ID]
,AC.[Last_Modified_Date]
,AC.[Last_Modify_By]
,AC.[Cheque_Passed_Id]
,AC.[Payment_Type_Id]
,AC.[Bank_Transfer_Id]
,AC.[Salesman_Id]
,AC.[PettyCash_Id]
,[IsCreditNote]
,Salesman.Salesman_Name
,C.OB_Debit
,C.OB_Credit
,((C.OB_Debit + ISNULL(SUM(AC.Debit),0)) - (C.OB_Credit + ISNULL(SUM(AC.Credit),0))) As Balance
,(Select((Supplier.OB_Credit + ISNULL(SUM(Acc_Supplier.Credit),0)) - (Supplier.OB_Debit + ISNULL(SUM(Acc_Supplier.Debit),0)))
From Acc_Supplier Where [Transaction_Date] < @TrDate And Supplier_ID = @SupplierId ) As PrevBalance
,(Select ISNULL(Sum(Debit),0)From Acc_Supplier Where [Transaction_Date] = @TrDate And Supplier_ID = @SupplierId) As Paid
,(AC.[Debit]*AC.[Commission]/100) AS Commission
From Acc_Customer AC
Left Join Salesman On AC.Salesman_Id = Salesman.Salesman_ID
Left Join [Customer] C On C.[Customer_ID] = AC.[Customer_ID]
Left Join Acc_Supplier On Salesman.Salesman_ID = Acc_Supplier.Supplier_ID
Left Join Supplier On Salesman.Salesman_ID = Supplier.Supplier_ID
where AC.Transaction_Date = @TrDate And Salesman.Salesman_Name =@Salesman
Group By
[Customer_Account_ID]
,AC.[Customer_ID]
,[Customer_Name]
,AC.[Receipt_No]
,AC.[Transaction_Date]
,AC.[Particulars]
,AC.[Debit]
,AC.[Credit]
,AC.[Narration]
,AC.[Sales_Master_ID]
,AC.[Sales_Return_ID]
,AC.[Last_Modified_Date]
,AC.[Last_Modify_By]
,AC.[Cheque_Passed_Id]
,AC.[Payment_Type_Id]
,AC.[Bank_Transfer_Id]
,AC.[Salesman_Id]
,AC.[PettyCash_Id]
,AC.[IsCreditNote]
,Salesman.[Salesman_Name]
,C.OB_Debit
,C.OB_Credit
,Supplier.OB_Debit
,Supplier.OB_Credit
,AC.[Commission]
End
Else if EXISTS(select Supplier_Account_ID from Acc_Supplier acs
Left Join Salesman On Salesman_Id = Salesman.Salesman_ID
Where Transaction_Date = @TrDate And Salesman.Salesman_Name = @Salesman)
Begin
SELECT Acc_Supplier.Supplier_ID
,Acc_Supplier.Transaction_Date
,Acc_Supplier.Debit
,Acc_Supplier.Credit
,(select Supplier.OB_Credit + ISNULL(sum(Acc_Supplier .Credit ),0)-(Supplier.OB_Debit + ISNULL (sum(Acc_Supplier.Debit),0)) from Acc_Supplier
where [Transaction_Date] < @TrDate And Supplier_ID = @SupplierId ) as PrevBalance
,(Select ISNULL(Sum(Debit),0)From Acc_Supplier Where Transaction_Date = @TrDate And Supplier_ID = @SupplierId) As Paid
,(Select Acc_Customer.[Debit]*Acc_Customer.[Commission]/100 from Acc_Customer where [Transaction_Date]= @TrDate And Acc_Supplier.Supplier_ID = @SupplierId) AS Commission
FROM Acc_Supplier
--Left Join Supplier On Supplier .Supplier_ID = Salesman_Name
Where Acc_Supplier. Transaction_Date = @TrDate AND Acc_Supplier .Supplier_ID =@SupplierId
end
else
Begin
SELECT Supplier_ID,Receipt_No,Transaction_Date,Debit,Credit,Narration FROM Acc_Supplier
End
end
error pls correct it