asked on 3/22/2019

query ms sql

SELECT        COUNT(Receipt_Detail.Receipt_Number) AS totalcountreceipt, SUM(Receipt_Detail.Amount_Applied) AS receiptamount, Invoice_Header.Invoice_Number,?
FROM            Invoice_Detail INNER JOIN
                         Invoice_Header ON Invoice_Detail.Invoice_Number = Invoice_Header.Invoice_Number INNER JOIN
                         Member_Association ON Invoice_Header.Member_Number = Member_Association.Member_Number INNER JOIN
                         Member ON Invoice_Header.Member_Number = Member.Member_Number INNER JOIN
                         Receipt_Detail ON Invoice_Detail.Invoice_Number = Receipt_Detail.Invoice_Number INNER JOIN
                         Receipt_Header ON Member_Association.Member_Number = Receipt_Header.Member_Number AND Receipt_Detail.Receipt_Number = Receipt_Header.Receipt_Number
WHERE        (Invoice_Detail.Charge_Code = 'D' OR
                         Invoice_Detail.Charge_Code = 'T' OR
                         Invoice_Detail.Charge_Code = 'N')  (Invoice_Header.Invoice_Date BETWEEN '20181201' AND '20190131') AND (Receipt_Header.Deposit_Date BETWEEN 
                         '20181201' AND '20190131')  and (Invoice_Header.Invoice_Amount > 0)
GROUP BY Invoice_Header.Invoice_Number
ORDER BY totalcountreceipt DESC

Open in new window

Inside of "?" if I want to add like below, is it possible? Please show me how.

select top 1 (Receipt_Detail.Amount_Applied) where Receipt_Header.Deposit_Date BETWEEN '20181201' AND '20190131' order by Receipt_Header.Deposit_Date asc

Microsoft SQL Server Microsoft SQL Server 2005 Microsoft SQL Server 2008 SQL

Last Comment

Raja Jegan R

3/23/2019

SOLUTION

Raja Jegan R

3/22/2019

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

ITsolutionWizard

3/22/2019

ASKER

i do not understand. where is value for select top 1 (Receipt_Detail.Amount_Applied)?

SOLUTION

Raja Jegan R

3/22/2019

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

ASKER CERTIFIED SOLUTION

Scott Pletcher

3/22/2019

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

ITsolutionWizard

3/22/2019

ASKER

Scott: yours does not work : Msg 8120, Level 16, State 1, Line 5
Column 'oa1.First_Amount_Applied' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Scott Pletcher

3/22/2019

Use:
MAX(oa1.First_Amount_Applied) AS First_Amount_Applied

ITsolutionWizard

3/22/2019

ASKER

THANK. but the data result has duplicated invoice number

ITsolutionWizard

3/22/2019

ASKER

Raja Jegan R: ROW_NUMBER() OVER(PARTITION BY
what is that mean? Thanks

Raja Jegan R

3/23/2019

ROW_NUMBER() OVER(PARTITION BY IH.Invoice_Number order by RH.Deposit_Date asc) rnum

ROW_NUMBER() function will create a sequence of number based upon the partitioned value(in your case it is partitioned based upon Invoice Number ordered by Deposit date ascending...
Once we get the sequence no. we can filter or extract the required values in the outer SELECT statement like the below to get the TOP 1 value..

WHERE rnum = 1