Inner join with Sum and Top 1 Keyword and orderby

Posted by Raj.Trivedi under Sql Server on 3/30/2015 | Points: 10 | Views : 413 | Status : [Member] [MVP] | Replies : 4
SELECT TOP 1
PIM.Invoiceno, PIM.InvoiceAmount,PIM.OutstandingAmount,
CM.ClientName,
SUM(PIM.CurrentPaidAmount) as CurrentPaidAmount,TMN.BillDate
from PaymentInfo PIM
inner join TransactionMasterNew TMN on
TMN.InvoiceNo = PIM.Invoiceno
inner join ClientMaster CM on
CM.ClientId = TMN.ClientId
where PIM.Invoiceno = 'AC/012'

group by PIM.InvoiceNo, PIM.InvoiceAmount,PIM.OutstandingAmount,
CM.ClientName,
TMN.BillDate,PIM.CurrentPaidAmount
order by PIM.PaymentDate desc

Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved



Responses

Posted by: Rajnilari2015 on: 3/30/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
Hi Raj,
It becomes difficult to figure out what is the problem that you are facing. Ideally, you should post some sample input data and the corresponding expected output. Then it becomes more easy for others to understand your problem.(:

However, I just gave a shot of modifying your query and here it is (since I don't know anything about your query result, input tables and what you are trying to achieve,whether you are encountering any error????? so I cannot guarantee that it is the one that you are looking for....)

SELECT TOP 1 

PIM.Invoiceno
, PIM.InvoiceAmount
,PIM.OutstandingAmount
,CM.ClientName
,TMN.BillDate
,SUM(PIM.CurrentPaidAmount) OVER (PARTITION BY PIM.InvoiceNo ORDER BY PIM.PaymentDate desc) as CurrentPaidAmount

INNER JOIN TransactionMasterNew TMN ON TMN.InvoiceNo = PIM.Invoiceno
INNER JOIN ClientMaster CM ON CM.ClientId = TMN.ClientId
WHERE PIM.Invoiceno = 'AC/012'


--
Thanks & Regards,
RNA Team

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raj.Trivedi on: 3/31/2015 [Member] [MVP] Starter | Points: 25

Up
0
Down
I want to get sum of the Current Paid Amount with the latest first entry made by the user for the current payment and, in the above query i am getting error incorrect syntax near order by

Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 3/31/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
Dear Raj,
Please put some sample table filled with data and the expected output. e.g.

Input

Declare @t1 table(id int, name varchar(50));

Insert into @t1 values(1,'name1'),(2,'name2');


Declare @t2 table(id int, name varchar(50));
Insert into @t2 values(2,'name22'),(3,'name3');


Expected Output

ID Name

1 name1
2 name2,name22
3 name3


It will be easy (in that case) to say and to provide a query that will help you. (:

--
Thanks & Regards,
RNA Team

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raj.Trivedi on: 3/31/2015 [Member] [MVP] Starter | Points: 25

Up
-1
Down
Hello team i have rebuild the query i am not able to get the sum for this query

select Top 1 TMN.Invoiceno,TMN.BillDate,PIM.CurrentPaidAmount,PIM.InvoiceAmount,CM.ClientName,PIM.PaymentDate,PIM.OutstandingAmount,
sum(PIM.CurrentPaidAmount) as TotalPaid from TransactionMasterNew TMN
inner join PaymentInfo PIM
on
TMN.Invoiceno = PIM.InvoiceNo
inner join
ClientMaster CM on
CM.ClientId = TMN.ClientId
where
TMN.Invoiceno = 'AC/012' and PIM.OutstandingAmount > 0.00
group by TMN.Invoiceno,CM.ClientName,TMN.BillDate,PIM.CurrentPaidAmount,PIM.InvoiceAmount,PIM.PaymentDate,PIM.OutstandingAmount
order by PIM.PaymentDate Desc

Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response