I'm a little new to sql that is this complex. Most of the stuff I have done is simple calls to single tables (or maybe 2). I have read the article here on groupby, and it seemed to get me close, but I am somehow still getting duplicate rows, and I am not sure why.
select cd.customer_id, cd.bill2_name, cd.bill2_csz, h.total_lines, h.total_sales, h.total_cost, h.total_sales - h.total_cost as Profit, (h.total_sales - h.total_cost) / h.total_sales as ppct from (select customer_id, count(1) as total_lines, sum(p21_sales_history_report_view.sales_price) as total_sales, sum(p21_sales_history_report_view.cogs_amount) as total_cost from p21_sales_history_report_view where invoice_date between '2015-09-07 00:00:00.000' and '2015-09-08 23:59:59.999' group by customer_id ) h inner join (Select customer_id, bill2_name as bill2_name, CONCAT(bill2_city,' ',bill2_state, ' ', bill2_postal_code) as bill2_csz from invoice_hdr) cd on h.customer_id = cd.customer_id
I get results that include the proper data (totals/etc are correct), but I get many duplicate lines of each. When I run the query for h (the table that pulls from the view) I get the data that I want without customer name/etc. h also does not have any duplication in it, so I know that part of the query is correct.
Any help would be greatly appreciated. Also any other suggestions about better ways to do this are also welcome. It is basically a simple sales history report. The catch is that the customer data has to come off of the invoice, rather than from a separate table.
Thanks in advance
ps: I know that the dates are hard coded and ugly, they will be replaced with parameters when I get this working.