Inner join with Sum and Top 1 Keyword and orderby

Posted by Raj.Trivedi under Sql Server on 3/30/2015 | Points: 10 | Views : 1147 | Status : [Member] [MVP] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

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

-1	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

-1

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

Latest Posts