query join ms sql query

ASKER

Any one can help

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Kelvin McDaniel

Or another brute force way is to alias your tables properly and use a sub-select for the single value you're looking for. It's definitely not as efficient as what ste5an suggested when you pair that with parameterization.

To illustrate my suggestion, I've taken the queries in your OP, reformatted them, aliased the tables and integrated the two using Member.Member_Number. This approach is definitely harder to maintain without the use of parameters for those finite WHERE values because you'll have to make sure they're always in sync.

SELECT        
	ih.Member_Number AS membernumber
	, ih.Invoice_Amount AS invoiceamount
	, (SELECT SUM(rd2.Amount_Applied) 
		FROM Receipt_Detail rd2
		INNER JOIN Receipt_Header rh2 ON rd2.Receipt_Number = rh2.Receipt_Number 
		INNER JOIN Invoice_Detail id2 ON rd2.Invoice_Number = id2.Invoice_Number
		WHERE (rd2.Member_Number = m.Member_Number)
		AND (rh.Deposit_Date BETWEEN '20181201' AND '20190131')
		AND (id.Charge_Code IN ('1910D', '1910T', 'NEW19'))) as totalreceiptamount
	, ma.Member_Type_Code
	, m.Full_Name
	, ih.Office_Number
FROM 
	Invoice_Detail id
	INNER JOIN Invoice_Header ih ON id.Invoice_Number = ih.Invoice_Number 
	INNER JOIN Receipt_Detail rd ON id.Invoice_Number = rd.Invoice_Number 
	INNER JOIN Receipt_Header rh ON rd.Receipt_Number = rh.Receipt_Number 
	INNER JOIN Member_Association ma ON ih.Member_Number = ma.Member_Number 
	INNER JOIN Member m ON ih.Member_Number = m.Member_Number

WHERE
	(id.Charge_Code IN ('1910D', '1910T', 'NEW19')) 
	AND (ih.Invoice_Date BETWEEN '20181201' AND '20190131') 
	AND (rh.Deposit_Date BETWEEN '20181201' AND '20190131') 
	AND (ma.Association_ID = 'ABC') 
	AND (ma.Status = 'A')

ORDER BY 
	ih.Invoice_Amount

Open in new window

Obviously I couldn't run the query to check my work... but you should be able to make any tweaks that are necessary. If it fails completely, separate the two queries and make sure they work individually (making any necessary edits), and then put them back together.

Good Luck!

slightwv (䄆 Netminder)

You shouldn't need to access the tables twice.

You should be able to get the SUM as part of the original query using the window function like: SUM(Amount_Applied) OVER(partition by some_columns) or overall sum with SUM(Amount_Applied) OVER()

Without sample data end expected results we won't be able to provide exact syntax.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

one member one assocation

ASKER

thank you for your helps

OK, member and member associations are clear now. What about the rest? Namely duplicate rows in the original query?

I would remove the date range for Receipt_Header from the query because the range controlled by Invoice_header should be enough and the relation from invoices to receipts is controller by invoice_number. Then you could remove the Receipt_Header from the query because it is not used after the date range removal.

Then you should calculate SUM(invoice_amount) and remove invoice_amount from GROUP BY. This will reflect more invoices per member.

BUT we still have the uncertainty in number of Invoice_Detail rows per one Invoice_Header. If you have more invoice details per one invoice header which is obvious in the real word then the query result will be incorrect... Of course, this is maybe solved by the Invoice_Detail.Charge_Code filter which I cannot predict...

If you are sure each invoice has max one detail row in given condition for Charge_Code then we are done.

ASKER

the basic principle is

one member one assocation
one member has one or more than one invoices
and we want the sum the receipt amount and receipt count to aviod the duplicate invoice member

ASKER

thank you for all your help and times. you guys are awesome.
I learned a lot from all of you.

OK, we still need more info. Your original query contains filter for invoice lines:

Invoice_Detail.Charge_Code IN ('1910D', '1910T', 'NEW19')

If each invoice has max one line containing one of these charge codes then the result of the query provided in this post should be correct.

You should add Invoice_Header.Invoice_Number into the query and it will show whether you have duplicate invoices or not.

SOLUTION

John_Vidmar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

unfortunately, the invoice amount is summed up in a huge number because of the group by invoice amount. I don't think the query is working

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.