Post reply

Query help

  • May 24, 2011 at 10:30 am

    #236784

    This query has been giving me fits so I finally broke down and realized I needed to ask for some help.

    I have two tables, TransactionDetails and TransactionHeader that join on TransactionNumber and have sales information such as ProductID, quantity, sales price etc. (Table structure is below). I need to query both tables and do some simple calculations. For each product I need to return a count for quantity sold and quantity returned, and also net sales sold, and net sales returned. The TransactionHeader table has a TransactionTypeCode field (1 = Sale and 2 = Return). Net sales sold is then just (quantity sold * sales price) and net sales returned is (quantity returned * sales price). The problems I've been having are the way the tables are structured with the TransactionTypeCode field. Maybe I'm just over thinking it. Wouldn't be surprised if I am...lol

    Here's a table example with some sample data:

    CREATE TABLE #TransactionDetails(

    TransactionNumber INT NOT NULL,

    ProductID INT NOT NULL,

    Quantity INT NOT NULL,

    SalesPrice MONEY NOT NULL

    )

    GO

    INSERT INTO #TransactionDetails (TransactionNumber,ProductID,Quantity,SalesPrice)

    SELECT 123,1,1,9.99

    UNION ALL

    SELECT 124,1,2,5.99

    UNION ALL

    SELECT 125,2,1,15.99

    UNION ALL

    SELECT 126,3,4,14.99

    UNION ALL

    SELECT 126,4,2,4.75

    UNION ALL

    SELECT 127,1,1,5.50

    UNION ALL

    SELECT 127,2,1,3.99

    UNION ALL

    SELECT 127,3,2,12.99

    UNION ALL

    SELECT 128,4,1,4.75

    UNION ALL

    SELECT 129,4,2,4.75

    GO

    CREATE TABLE #TransactionHeader(

    TransactionNumber INT NOT NULL,

    TransactionTypeCode INT NOT NULL,

    TransactionDate DATETIME NOT NULL,

    Void BIT NOT NULL

    )

    GO

    INSERT INTO #TransactionHeader (TransactionNumber,TransactionTypeCode,TransactionDate,Void)

    SELECT 123,1,'4/19/2011',1

    UNION ALL

    SELECT 124,2,'4/20/2011',1

    UNION ALL

    SELECT 125,1,'4/20/2011',1

    UNION ALL

    SELECT 126,2,'4/20/2011',1

    UNION ALL

    SELECT 127,1,'4/21/2011',1

    UNION ALL

    SELECT 128,1,'4/22/2011',0

    UNION ALL

    SELECT 129,1,'4/24/2011',1

    My only other requirements are to exclude voided transactions (Void=0) and gather all transactions between a certain date(TransactionDate Between '4/19/2011' AND '4/23/2011'). The query also needs to populate a table that looks like the following:

    CREATE TABLE #Sales(

    ProductID INT NOT NULL,

    QuantitySold INT NOT NULL,

    QuantityReturned INT NOT NULL,

    NetSalesSold MONEY NOT NULL,

    NetSalesReturned MONEY NOT NULL

    )

    GO

    So if given the sample data in the first two tables and the requirements for voids and transaction dates above. My sales table should have the following data assuming all my calculations are right and I didn't transpose any numbers or overlook any of the dummy data I put in.

    INSERT INTO #Sales (ProductID,QuantitySold,QuantityReturned,NetSalesSold,NetSalesReturned)

    SELECT 1,2,2,15.49,11.98

    UNION ALL

    SELECT 2,2,0,19.98,0

    UNION ALL

    SELECT 3,2,4,25.98,59.92

    UNION ALL

    SELECT 4,0,2,0,9.5

    GO

    SELECT * FROM #Sales

    Thanks for any and all help!

    -Brendan

  • May 24, 2011 at 10:35 am

    #1329228

    You mean you can't figure out out to split the sales and returns???

    Something like this maybe :

    Qty * Amount * CASE WHEN TransactionTypeCode = 2 THEN -1 ELSE 1 END

  • May 24, 2011 at 10:52 am

    #1329246

    Thanks for the reply. Yeah, sorry if I wasn't clear enough. My biggest problem has been splitting the returns and sales and then doing the quantity counts and sales calculations.

    Because the calculations are done by product I've been trying to group on ProductID but then I have the problem of calculating the SalesPrice because it's not included in an aggregate or group by clause. I've also tried using a CASE but I haven't gotten it in the format that I need. I need the results split horizontally in their own columns. The CASE statement you gave just gives me the sales amount as a positive it the transaction is a SALE and a negative if the transaction is a RETURN. Which isn't what I'm really looking for either. I might be missing something though.

  • May 24, 2011 at 11:04 am

    #1329259

    SUM(CASE WHEN TranType = 2 THEN Qty ELSE 0 END) AS QtyReturns

    , SUM(CASE WHEN TranType = 1 THEN Qty ELSE 0 END) AS QtySold

  • May 24, 2011 at 11:47 am

    #1329281

    Thanks Ninja! I think that did it. I was right, I was over thinking the problem and made it more complicated than it needed to be. I was actually close with what I was doing but my CASE statement wasn't correct. Thanks again.

  • May 24, 2011 at 11:53 am

    #1329284

    HTH :w00t:.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply