I have a question about SQL 2005 query. This may sound simple, but no matter how many queries i make & test it doesnt seem to work.
In an inventory I have hundreds of food products. Each of this food product having its own budget to produce. A customer that orders a product could have more than 1 invoice in their transaction account. I have 3 tables for this, Order, Invoice and Budget.This is the query result:
Product 1 Order 1 Inv 1 Budget 100
Order 2 Inv 2 Budget 100
Order 3 Inv 3 Budget 100
My problem is that when I import the query to a pivot table in excel, the budget will appear as 300(when i drag it in the data items area) based on the invoice / order count. I want it to appear only once and show 100 instead, which is based on 1 product.
CASE WHEN COUNT(dbo.Inv.No) <= 1 THEN dbo.Budget.Amt ELSE SUM(dbo.Exc_Budget.Amt) / dbo.Exc_Budget.Amt END AS TEST