Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Unanswered: Subtracting data from the same field

    Thanks for any help given! And yes I am just a newbe.

    This is a simple example of the table that is sent to me daily as a text file.

    I have a table with a column indicating number of transaction (issue). The table has another column (Type) indicating wheither or not it was a sale or return. Also, returns can be identified by CusNum = 9999 as that is the store's id.

    tblSales
    item.........issue......Type........ SalePrice........ CusNum...... RetCus
    socks ....... 1 ........ sale......... 5.00.............. 222.............9999
    shoes ....... 2 ........ sale........ 50.00.............. 324.............9999
    socks ....... 1 ........ return ..... 5.00..............9999.............222
    socks ....... 2 ........ sale......... 10.00..............324.............9999

    I can write the query to sum and group total sales and group on item, but I need to back out the returns as well.

    Could someone please help me write a query that sums and groups sales and back out the returns?
    If I were smart enough, when I brought the text file in via a DTS, I would multiply returns by -1 and I could just sum the total. But I don't know how to do that either.

    Thanks,
    Lee

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select whatever, sum(
    case when Type = 'sale' then SalePrice
    else -SalePrice
    end ) as totalsales
    from yourtable
    group by whatever

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Are you saying that a return is either when Type is "return" or when RetCus is "9999" ?

  4. #4
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    The way the data is sent to me I have not found a case where it was not true. The returns are charged back to source of the sale.

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    Just to be clear:
    So from your analysis of the data you have received - a return is either when Type is "return" or when RetCus is "9999". So in your example, even thought the RetCus is "222" because the Type is "return", it is a return (and vice-versa) ?

  6. #6
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    You could view CusNum as "charge to customer" and RetCus as a credit back to the customer.

  7. #7
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    What I tried to do was give a simple example of what the data actually is. In reality the data is of parts usage in a manufacturing facility where the Issues (CusNum) is charges to a department. If the department returns a part, it is view as a returned and then in turn charged back to the Parts Store (RetCus).

    What I am attempting to do is give the mangers over the departments a easy way to view their spending quickly and easily (web app). There are several shortcommings of the software package the way it was implemented that make it difficult for the average user get back what they need to see on a daily basis.

    I can do it via creating by returning two recordsets Charges and Returns, but it would flow better if I had the knowledge to combine the two and give them a bottom line of month to date spending (issues - returns)
    Thanks,
    Lee

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you try the query i posted?

    rudy

  9. #9
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    Sorry Rudy for taking so long to get back. I came early this morning a tried to figure out how to use you code with no sucess.

    This statement will return the total of both Returns and Sales, but the Returns are a + num. I need them to be negative.

    SELECT SUM([ISSUE]*[SALEPRICE] ) as TOTCOST
    FROM tblIssue
    WHERE [RETCUS] = '222' and ISSUEDATETIME BETWEEN GETDATE() - 45 AND GETDATE() -30 AND TRANSTYPE = 'RETURN' OR [CUSNUM] = '222' and ISSUEDATETIME BETWEEN GETDATE() - 45 AND GETDATE() -30 AND TRANSTYPE = 'SALE'

    This is my attempt at your solution. I am getting an error stating incorrect sytax near keywork as

    SELECT SUM(CASE
    WHEN [RETCUS] = '222' AND ISSUEDATETIME BETWEEN GETDATE() - 45 AND GETDATE() -30 AND TRANSTYPE = 'RETURN'THEN
    ([ISSUE]*[SALEPRICE])*-1 as TOTCOST
    WHEN [CUSNUM] ='222' AND ISSUEDATETIME BETWEEN GETDATE() - 45 AND GETDATE() -30 AND TRANSTYPE = 'SALE'THEN
    ([ISSUE]*[SALEPRICE]) as TOTCOST
    END
    FROM tblIssue

    thanks Lee

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    step 1 -- rewrite your query to fix the syntax error

    Code:
    SELECT SUM(
      CASE WHEN [RETCUS] = '222' 
            AND ISSUEDATETIME BETWEEN GETDATE() - 45 
                                  AND GETDATE() - 30 
            AND TRANSTYPE = 'RETURN' 
           THEN -[ISSUE]*[SALEPRICE]
           WHEN [CUSNUM] ='222' 
            AND ISSUEDATETIME BETWEEN GETDATE() - 45 
                                  AND GETDATE() - 30 
            AND TRANSTYPE = 'SALE'
           THEN [ISSUE]*[SALEPRICE]
      END ) as TOTCOST
    FROM tblIssue
    step 2 -- simplify by moving similar conditions to WHERE clause

    Code:
    SELECT SUM(
      CASE WHEN TRANSTYPE = 'RETURN' 
           THEN -[ISSUE]*[SALEPRICE]
           WHEN TRANSTYPE = 'SALE'
           THEN [ISSUE]*[SALEPRICE]
      END ) as TOTCOST
    FROM tblIssue
    WHERE [RETCUS] = '222' 
      AND ISSUEDATETIME BETWEEN GETDATE() - 45 
                            AND GETDATE() - 30

  11. #11
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    THANKS SO VERY MUCH!!!

    That did the trick!

    Lee

Posting Permissions

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