TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Navaz
NA
96
45.1k
query
Jan 22 2014 5:53 AM
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
Reply
Answers (
3
)
How to display minimum values from group?
query