SQL Server Performance

SP Help

Discussion in 'General Developer Questions' started by dkuntz, Jun 23, 2003.

  1. dkuntz New Member

    Is it possible to use SUM and COUNT in this SP? Notice below I am using SUM(Transactions.Price) As [Price_Sum]. But I also need Transactions.Price with out the Sum. Likewise with COUNT(TransNo).
    <see below>
    I am calling this from a VB function that needs to display the Total Prices and Total Transactions in addition to each Price and Transaction separate.

    Any solutions or suggestions are welcome -


    CREATE PROCEDURE usp_GetEDIBillingRecords
    @InvDate smalldatetime,
    @EDIFlag char(1) = 'E',
    @DelType char(1)= 'C'
    AS
    SET NOCOUNT ON
    SELECT Customer.CustomerID,
    Transactions.InvoiceDate,
    Transactions.InvoiceNumber,
    Transactions.ChgToName,
    Transactions.TransNo,
    Transactions.Price,
    SUM(Transactions.Price) AS [Price_Sum],
    COUNT(Transactions.TransNo) AS [Trans_Count]
    FROM Customer
    INNER JOIN Transactions On Customer.CustomerID = Transactions.ChgToNo
    WHERE Customer.CustomerEDIFlag = @EDIFlag
    AND Transactions.DockhiFlag = @DelType
    AND Transactions.InvoiceDate = @InvDate
    GROUP BY Customer.CustomerID,
    Transactions.InvoiceDate,
    Transactions.InvoiceNumber,
    Transactions.ChgToName,
    Transactions.TransNo,
    Transactions.Price,
    ORDER BY Customer.CustomerID
    GO
    dkuntz, Jun 23, 2003
    #2

  2. vbkenya New Member

    First observation:
    - An illegal comma before the ORDER By clause
    - A GROUP BY clause will always eliminate repeating values and summarize them using the aggregated columns.


    Nathan H.O.
    vbkenya, Jun 23, 2003
    #2

  3. vbkenya New Member

    But try this out without the GROUP BY in the main query.

    CREATE PROCEDURE usp_GetEDIBillingRecords
    @InvDate smalldatetime,
    @EDIFlag char(1) = 'E',
    @DelType char(1)= 'C'
    AS
    SET NOCOUNT ON
    SELECT Customer.CustomerID,
    Transactions.InvoiceDate,
    Transactions.InvoiceNumber,
    Transactions.ChgToName,
    Transactions.TransNo,
    Transactions.Price,
    x.Price_sum,
    x.Trans_Count
    FROM Customer INNER JOIN Transactions On Customer.CustomerID = Transactions.ChgToNo
    LEFT JOIN (SELECT Transactions.Invoicenumber, SUM(Transactions.Price) AS [Price_Sum],COUNT(Transactions.TransNo) AS [Trans_Count]
    from transactions group by invoicenumber) x ON x.Invoicenumber=Transactions.Invoicenumber
    WHERE Customer.CustomerEDIFlag = @EDIFlag
    AND Transactions.DockhiFlag = @DelType
    AND Transactions.InvoiceDate = @InvDate
    ORDER BY Customer.CustomerID

    GO



    The LEFT JOIN clause on the subquery combines the results of the individual records with that of the aggregated SUM and COUNT. Inellegant but hopefully helpful.

    Nathan H.O.
    vbkenya, Jun 23, 2003
    #2

  4. rushmada New Member

    For this u can rewrite the query by changing ur query by using the below query
    as an example.

    In this i have taken sum(billed_value) in one table and count(po_no) in other
    table finally getting the results as u mentioned.

    select a.po_no,a.billval,b.pocount
    from
    (select po_no,sum(billed_value) as billval from pur_po_detail
    group by po_no) a
    ,
    (select po_no,count(po_no) as pocount from pur_po_detail group by po_no) b
    where
    a.po_no=b.po_no

    thanks


    Rushendra
    rushmada, Jun 23, 2003
    #2

  5. dkuntz New Member

    Nathan, Thanks for your time. Unfortunately I am still not getting the results I need.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------
    x.Price_Sum,
    x.Trans_Count
    FROM Customer INNER JOIN Transactions On Customer.CustomerID = Transactions.ChgToNo
    LEFT JOIN (SELECT Transactions.InvoiceNumber, SUM(Price) AS [Price_Sum], COUNT(TransNo) AS [Trans_Count]
    FROM Transactions
    GROUP BY InvoiceNumber) As x ON x.InvoiceNumber=Transactions.InvoiceNumber
    WHERE Customer.CustomerEDIFlag = @EDIFlag
    AND Transactions.DockhiFlag = @DelType
    AND Transactions.InvoiceDate = @InvDate
    ORDER BY Customer.CustomerID
    GO
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Basically I am printing the results to a text file. Here is what it should look like (condensed) :

    InvoiceNumber: 12345 / Price_Sum: 58.50 / Trans_Count: 4
    TransNo: 446
    Price: 10.25
    TransNo:447
    Price: 20.00
    TransNo:448
    Price: 22.25
    TransNo: 449
    Price: 6.00
    InvoiceNumber 54321 / Price_Sum: 32.00 / Trans_Count: 2
    TransNo: 501
    Price: 8.00
    TransNo: 502
    Price : 24.00
    etc................etc............................

    When I run the app. it appears that for EACH InvoiceNumber the Price_Sum is only printing the first Price and Trans_Count is always 1. That's why If it's possible I would love to do this from the same SP.

    Any ideas?

    Thanks again!
    dkuntz, Jun 24, 2003
    #2

  6. bambola New Member

    Could you post the tables structure, if possible with a few rows for each that do not get you the results you expect?

    Bambola.
    bambola, Jun 24, 2003
    #2

  7. bambola New Member

    Try this. I did not test it so maybe it's not error free


    CREATE PROCEDURE usp_GetEDIBillingRecords
    @InvDate smalldatetime,
    @EDIFlag char(1) = 'E',
    @DelType char(1)= 'C'
    AS
    SET NOCOUNT ON
    SELECT Customer.CustomerID,
    Transactions.InvoiceDate,
    Transactions.InvoiceNumber,
    Transactions.ChgToName,
    Transactions.TransNo,
    Transactions.Price
    FROM Customer
    INNER JOIN Transactions On Customer.CustomerID = Transactions.ChgToNo
    WHERE Customer.CustomerEDIFlag = @EDIFlag
    AND Transactions.DockhiFlag = @DelType
    AND Transactions.InvoiceDate = @InvDate
    GROUP BY Customer.CustomerID,
    Transactions.InvoiceDate,
    Transactions.InvoiceNumber,
    Transactions.ChgToName,
    Transactions.TransNo,
    Transactions.Price,
    ORDER BY Customer.CustomerID
    compute SUM(Transactions.Price),
    COUNT(Transactions.TransNo)
    BY Customer.CustomerID,
    Transactions.InvoiceDate,
    Transactions.InvoiceNumber,
    Transactions.ChgToName,
    Transactions.TransNo,
    Transactions.Price
    Bambola.
    bambola, Jun 24, 2003
    #2

  8. dkuntz New Member

    Bambola -
    Same results. How do I post a table structure? Thanks for your time and let me know if there is anything else you might need.
    dkuntz, Jun 24, 2003
    #2

  9. bambola New Member

    In QA, open the browser (press F<img src='/community/emoticons/emotion-11.gif' alt='8)' />, go to the database/user tables, right click the table and choose "script object to new window as create".<br />From EM, right click table ans select "all tasks/generate sql script"<br /><br />2 questions in the meantime. <br />1 - You say you get the same results. Did you run it in QA or from application? If you <br /> did not already, try to run it from QA and confirm that you have the same results.<br />2 - Do you have more than one line in table Transactions for each TransNo?<br /><br />Bambola.
    bambola, Jun 24, 2003
    #2

  10. bambola New Member

    Something I noticed. You are comparing the field Transactions.InvoiceDate to variable @InvDate.
    If @InvDate contains also hours and minute and your intention is to get the result for a certain day (whole day), it can be the reason you are not getting the results you expect.

    If this is the case you will have to change your query in the following way:

    Add this to the beginning of the stored procedure


    DECLARE @start_date smalldatetime
    , @end_date smalldatetime

    SELECT @start_date = DATEADD(dd, 0, DATEDIFF(dd, 0, @InvDate))
    SELECT @end_date = DATEADD(minute, 59, @start_date)

    Change this line
    AND Transactions.InvoiceDate BETWEEN @InvDate

    to this
    AND Transactions.InvoiceDate BETWEEN @start_date AND @end_date

    Bambola.




    bambola, Jun 24, 2003
    #2

  11. gaurav_bindlish New Member

    Also post some test data for the tables so that the query can be simulated.

    One more question - Do you want the sum and the Tran_count to be shown only once in the output. I really hope the answer is NO as the solution that Bambola posted will have sum and Tran_count repeated for the set for which it was calculated. If the answer is YES, I don't see other than cursor based solution for this.

    Gaurav
    gaurav_bindlish, Jun 24, 2003
    #2

  12. bambola New Member

    I wouldn't rush into using cursor. There are set-based solutions to this.
    You could create a temporary table where you just count and sum by same conditions.
    Then join this temp table in the query you have above (without sum and count). It should get
    you the results you desire.

    Bambola.

    bambola, Jun 24, 2003
    #2

  13. dkuntz New Member

    I feel like a first class fool. Everything works just fine with the code below. There was a user error - Me! In my app I failed to realize I was connecting to our "test database" rather than the "live database" this whole time. The data in our test db can be substantially different than that of the live db. I apologize for all of you who were moiling through this.
    dkuntz, Jun 24, 2003
    #2

  14. dkuntz New Member

    CREATE PROCEDURE usp_GetEDIBillingRecords
    @InvDate smalldatetime,
    @EDIFlag char(1) = 'E',
    @DelType char(1)= 'C'
    AS
    SET NOCOUNT ON
    SELECT Customer.CustomerID,
    Transactions.InvoiceDate,
    Transactions.InvoiceNumber,
    Transactions.ChgToName,
    Transactions.TransNo,
    Transactions.Price,
    x.Price_Sum,
    x.Trans_Count
    FROM Customer INNER JOIN Transactions On Customer.CustomerID = Transactions.ChgToNo
    LEFT JOIN (SELECT Transactions.InvoiceNumber, SUM(Transactions.Price) AS [Price_Sum], COUNT(Transactions.TransNo) AS [Trans_Count]
    FROM Transactions
    WHERE Transactions.DockhiFlag = @DelType
    AND Transactions.InvoiceDate = @InvDate
    GROUP BY InvoiceNumber) As x ON x.InvoiceNumber=Transactions.InvoiceNumber
    WHERE Customer.CustomerEDIFlag = @EDIFlag
    AND Transactions.DockhiFlag = @DelType
    AND Transactions.InvoiceDate = @InvDate
    ORDER BY Customer.CustomerID
    GO
    dkuntz, Jun 24, 2003
    #2

  15. bambola New Member

    eh, eh, eh... [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]
    bambola, Jun 24, 2003
    #2

  16. gaurav_bindlish New Member

    So much for a tiny error... [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Gaurav
    gaurav_bindlish, Jun 24, 2003
    #2

  17. vbkenya New Member

    Inspite of your error we are still glad to have steered you away from your initial query. Maybe it wasn't tiny after all.

    Nathan H.O.
    vbkenya, Jun 24, 2003
    #2

Share This Page