We help IT Professionals succeed at work.

query ms sql

171 Views
Last Modified: 2019-03-23
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
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Few suggestions below and try the modified query..
1. Always use Table alias for better readability of query..
2. Formatting makes the query more readable..
SELECT Invoice_Number, totalcountreceipt, receiptamount, Amount_Applied
FROM (
SELECT IH.Invoice_Number, COUNT(RD.Receipt_Number) OVER (PARTITION BY IH.Invoice_Number) AS totalcountreceipt, SUM(RD.Amount_Applied) OVER (PARTITION BY IH.Invoice_Number) AS receiptamount
, RD.Amount_Applied, ROW_NUMBER() OVER(PARTITION BY IH.Invoice_Number order by RH.Deposit_Date asc) rnum
FROM Invoice_Detail ID
JOIN Invoice_Header IH ON ID.Invoice_Number = IH.Invoice_Number
JOIN Member_Association MA ON IH.Member_Number = MA.Member_Number
JOIN Member M ON IH.Member_Number = M.Member_Number
JOIN Receipt_Detail RD ON ID.Invoice_Number = RD.Invoice_Number
JOIN Receipt_Header RH ON MA.Member_Number = RH.Member_Number AND RD.Receipt_Number = RH.Receipt_Number
WHERE ID.Charge_Code IN ('D', 'T', 'N') 
AND IH.Invoice_Date BETWEEN '20181201' AND '20190131'
AND RH.Deposit_Date BETWEEN '20181201' AND '20190131'
and IH.Invoice_Amount > 0
) temp
WHERE rnum = 1
ORDER BY totalcountreceipt DESC

Open in new window

Author

Commented:
i do not understand. where is value for select top 1 (Receipt_Detail.Amount_Applied)?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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 PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Use:
MAX(oa1.First_Amount_Applied) AS First_Amount_Applied

Author

Commented:
THANK. but the data result has duplicated invoice number

Author

Commented:
Raja Jegan R: ROW_NUMBER() OVER(PARTITION BY
what is that mean? Thanks
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.