Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2008
    Posts
    7

    Unanswered: How to avoid repeated data

    Hi everyone,

    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:

    Prooduct Order Invoice Budget
    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.

    My query:
    CASE WHEN COUNT(dbo.Inv.No) <= 1 THEN dbo.Budget.Amt ELSE SUM(dbo.Exc_Budget.Amt) / dbo.Exc_Budget.Amt END AS TEST

    Is my logic wrong? HELP!
    Last edited by tqlashot_kk; 09-06-08 at 02:00.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you provide some more comprehensive sample data and desired output please?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •