Group sums?

  • sclements

    SSC-Addicted

    Points: 495

    This is probably a fairly easy thing and I've just overlooked something...!

    I've built a report to show invoices and details of invoices, comparing purchase invoices to sales invoices.

    The issue is that I sometimes have multiple purchase invoices per sales invoice, so when I have the below code in my select statement, it's selecting the correct information, but showing multiple lines per placement!

    CAST(I.NetSum AS DECIMAL(8,2)) AS [Sales Invoice],

    CAST((CAST(SBTI.Quantity AS NUMERIC(9, 2)) * CAST(SBTI.Amount AS NUMERIC(9, 2))) AS Numeric(9,2)) [Purchase Invoice]

    So this shows me :

    Line 1 SalesValueA PurchaseValueA

    Line 2 SalesValueA PurchaseValueB

    Line 3 SalesValueA PurchaseValueC

    Whereas what I want is a sum of the purchasevalues

    I've tried changing the select to

    CAST(I.NetSum AS DECIMAL(8,2)) AS [Sales Invoice],

    SUM(CAST(CAST(SBTI.Quantity AS NUMERIC(9, 2)) * CAST(SBTI.Amount AS NUMERIC(9, 2))) AS Numeric(9,2)) [Purchase Invoice]

    AND

    CAST(I.NetSum AS DECIMAL(8,2)) AS [Sales Invoice],

    CAST(SUM(CAST(SBTI.Quantity AS NUMERIC(9, 2)) * CAST(SBTI.Amount AS NUMERIC(9, 2))) AS Numeric(9,2)) [Purchase Invoice]

    but to no avail!

    I can post the full query if needs by, but if anyone has any pointers, it would be appreciated!

  • GuruGPrasad

    SSCommitted

    Points: 1768

    Hi sclements,

    Ur Quantity have null values try for isnull

    Regards

    Guru

  • sclements

    SSC-Addicted

    Points: 495

    Hi,

    no joy with that I'm afraid!

  • Gazareth

    One Orange Chip

    Points: 27737

    Can you post the full query?

    Thanks

  • sclements

    SSC-Addicted

    Points: 495

    USE [Monarch_Pronet_ITOIL]

    GO

    /****** Object: StoredProcedure [dbo].[RPT_WHOS_MISSING] Script Date: 03/26/2012 15:39:10 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    --Report shows contract placements that we have invoiced - the pay and charge values

    ALTER PROCEDURE [dbo].[RPT_WHOS_MISSING]

    @StartDate DATETIME,

    @EndDate DATETIME

    AS

    BEGIN

    DECLARE @CreditNotes TABLE(PlacementID INT, NetSum DECIMAL(18,2))

    INSERT INTO @CreditNotes

    SELECT P.PlacementId, I.NetSum

    FROM Invoices I

    INNER JOIN PlacementInvoices PInv ON I.InvoiceId = PInv.InvoiceId

    INNER JOIN Placements P ON PInv.PlacementId = P.PlacementId

    INNER JOIN PlacementConsultants PC ON P.PlacementId = PC.PlacementId

    INNER JOIN Users CNS ON CNS.UserId = PC.UserId

    INNER JOIN UserNominalInfo NI ON NI.UserId = CNS.UserId

    INNER JOIN Person PER ON PER.PersonId = P.ApplicantId

    INNER JOIN Jobs J ON P.JobId = J.JobId

    INNER JOIN ClientSectorDefinedColumns CSDC ON CSDC.ClientId = P.ClientId

    INNER JOIN Clients C ON C.ClientId = CSDC.ClientId

    WHERE InvoiceIssuedDate BETWEEN @StartDate AND @EndDate AND I.NetSum < 0 AND P.PlacementTypeId = 6

    SELECT CNS.UserName + ' ' + CNS.Surname AS Consultant,

    Per.PersonName + ' ' + Per.Surname AS Candidate,

    I.InvoiceIssuedDate AS Date,

    J.JobRefNo AS [Placement Number],

    CAST(I.InvoiceId AS VARCHAR(MAX)) AS InvoiceId,

    CASE WHEN I.NetSum > 0 THEN 'Invoice' ELSE 'Credit Note' END AS [Type],

    CSDC.SageRef AS Client,

    C.Company AS [Delivery Name],

    CAST(I.NetSum AS DECIMAL(8,2)) AS [Sales Invoice],

    CAST(CAST(SBTI.Quantity AS NUMERIC(9, 2)) * CAST(SBTI.Amount AS NUMERIC(9, 2)) AS Numeric(9,2)) [Purchase Invoice]

    FROM Invoices I

    INNER JOIN PlacementInvoices PInv ON I.InvoiceId = PInv.InvoiceId

    INNER JOIN InvoiceDetails ID ON I.InvoiceId = ID.InvoiceId

    INNER JOIN SelfBillingInvoiceTimesheetItems SBTI ON SBTI.SelfBillingInvoiceTimesheetItemId = ID.SelfBillingInvoiceTimesheetItemId

    INNER JOIN Placements P ON PInv.PlacementId = P.PlacementId

    INNER JOIN PlacementConsultants PC ON P.PlacementId = PC.PlacementId

    INNER JOIN Users CNS ON CNS.UserId = PC.UserId

    INNER JOIN UserNominalInfo NI ON NI.UserId = CNS.UserId

    INNER JOIN Person PER ON PER.PersonId = P.ApplicantId

    LEFT OUTER JOIN Jobs J ON P.JobId = J.JobId

    INNER JOIN ClientSectorDefinedColumns CSDC ON CSDC.ClientId = P.ClientId

    INNER JOIN Clients C ON C.ClientId = CSDC.ClientId

    WHERE InvoiceIssuedDate BETWEEN @StartDate AND @EndDate AND invoicestatusid <> 53 AND p.placementtypeid = 6

    GROUP BY CNS.UserName + ' ' + CNS.Surname,

    Per.PersonName + ' ' + Per.Surname,

    I.InvoiceIssuedDate,

    J.JobRefNo,

    I.InvoiceId,

    CSDC.SageRef,

    C.Company ,

    I.NetSum,

    SBTI.Amount,

    SBTI.Quantity

    --ORDER BY CNS.UserName, CNS.Surname

    END

  • Gazareth

    One Orange Chip

    Points: 27737

    Thanks.

    You were on the right track with the changes you quoted above, but if you weren't removing SBTI.Amount & SBTI.Quantity from the GROUP BY clause, then you would have ended up with the same results.

    So I think the query should be:

    SELECT CNS.UserName + ' ' + CNS.Surname AS Consultant,

    Per.PersonName + ' ' + Per.Surname AS Candidate,

    I.InvoiceIssuedDate AS Date,

    J.JobRefNo AS [Placement Number],

    CAST(I.InvoiceId AS VARCHAR(MAX)) AS InvoiceId,

    CASE WHEN I.NetSum > 0 THEN 'Invoice' ELSE 'Credit Note' END AS [Type],

    CSDC.SageRef AS Client,

    C.Company AS [Delivery Name],

    CAST(I.NetSum AS DECIMAL(8,2)) AS [Sales Invoice],

    CAST(SUM(CAST(SBTI.Quantity AS NUMERIC(9, 2)) * CAST(SBTI.Amount AS NUMERIC(9, 2)) AS Numeric(9,2))) [Purchase Invoice]

    FROM Invoices I

    INNER JOIN PlacementInvoices PInv ON I.InvoiceId = PInv.InvoiceId

    INNER JOIN InvoiceDetails ID ON I.InvoiceId = ID.InvoiceId

    INNER JOIN SelfBillingInvoiceTimesheetItems SBTI ON SBTI.SelfBillingInvoiceTimesheetItemId = ID.SelfBillingInvoiceTimesheetItemId

    INNER JOIN Placements P ON PInv.PlacementId = P.PlacementId

    INNER JOIN PlacementConsultants PC ON P.PlacementId = PC.PlacementId

    INNER JOIN Users CNS ON CNS.UserId = PC.UserId

    INNER JOIN UserNominalInfo NI ON NI.UserId = CNS.UserId

    INNER JOIN Person PER ON PER.PersonId = P.ApplicantId

    LEFT OUTER JOIN Jobs J ON P.JobId = J.JobId

    INNER JOIN ClientSectorDefinedColumns CSDC ON CSDC.ClientId = P.ClientId

    INNER JOIN Clients C ON C.ClientId = CSDC.ClientId

    WHERE InvoiceIssuedDate BETWEEN @StartDate AND @EndDate AND invoicestatusid <> 53 AND p.placementtypeid = 6

    GROUP BY CNS.UserName + ' ' + CNS.Surname,

    Per.PersonName + ' ' + Per.Surname,

    I.InvoiceIssuedDate,

    J.JobRefNo,

    I.InvoiceId,

    CSDC.SageRef,

    C.Company ,

    I.NetSum

    Hopefully that gives the expected results! 🙂

    And as Guru mentioned, if SBTI.Amount & SBTI.Quantity are nullable, it'd be best to wrap them in ISNULLs or you may not get the correct values.

    Thanks

  • sclements

    SSC-Addicted

    Points: 495

    Unfortunately I'm now getting a crazily high number instead of the figure I was expecting!

    In my version I'm seeing (when filtering down to one contractor:

    Sales Invoice Purchase Invoice

    4275.00 1000.00

    4275.00 600.00

    So I'd ideally like to see:

    Sales Invoice Purchase Invoice

    4275.00 1600.00

    But what I'm seeing with your suggestion is:

    Sales Invoice Purchase Invoice

    4275.00 14400.00

    !!

    Not sure where that figure has come from!

  • Gazareth

    One Orange Chip

    Points: 27737

    Ha, I was going to mention it, but this can be caused by one or more of your joins causing multiple rows to be returned for the values you're summing.

    This causes values to be summed multiple times, and results in the wrong answer.

    Difficult to tell which one(s) without knowing your table structure or data itself.

    Usual way to fix is to replace some joins with subqueries, or put subselects into the select clause.

    Running this might help you identify where the 'extra' rows are being generated from:

    You're looking for duplicate values of SBTI.SelfBillingInvoiceTimesheetItemId (I'm assuming that's a unique/primary key?)

    SELECT *

    FROM Invoices I

    INNER JOIN PlacementInvoices PInv ON I.InvoiceId = PInv.InvoiceId

    INNER JOIN InvoiceDetails ID ON I.InvoiceId = ID.InvoiceId

    INNER JOIN SelfBillingInvoiceTimesheetItems SBTI ON SBTI.SelfBillingInvoiceTimesheetItemId = ID.SelfBillingInvoiceTimesheetItemId

    INNER JOIN Placements P ON PInv.PlacementId = P.PlacementId

    INNER JOIN PlacementConsultants PC ON P.PlacementId = PC.PlacementId

    INNER JOIN Users CNS ON CNS.UserId = PC.UserId

    INNER JOIN UserNominalInfo NI ON NI.UserId = CNS.UserId

    INNER JOIN Person PER ON PER.PersonId = P.ApplicantId

    LEFT OUTER JOIN Jobs J ON P.JobId = J.JobId

    INNER JOIN ClientSectorDefinedColumns CSDC ON CSDC.ClientId = P.ClientId

    INNER JOIN Clients C ON C.ClientId = CSDC.ClientId

    WHERE InvoiceIssuedDate BETWEEN @StartDate AND @EndDate AND invoicestatusid <> 53 AND p.placementtypeid = 6

    ORDER BY CNS.UserName + ' ' + CNS.Surname,

    Per.PersonName + ' ' + Per.Surname,

    I.InvoiceIssuedDate,

    J.JobRefNo,

    I.InvoiceId,

    CSDC.SageRef,

    C.Company ,

    I.NetSum,

    SBTI.SelfBillingInvoiceTimesheetItemId,

    SBTI.Amount,

    SBTI.Quantity

  • tfifield

    SSCrazy Eights

    Points: 9655

    sclements,

    Any time you join from a parent table to a child table that has multiple rows per parent, you get the child number of rows in the query. That's obvious.

    Let's say you have Table A as the invoice header table and Table B as the invoice detail and Table C as a related invoice totals table. Table A has a single record for invoice 1. Table B has 3 related detail records for invoice 1. Table C has a single record for invoice 1. Let's say that Table C has $100 for the column InvoiceTotal.

    If you join from Table A to Table B and from Table A to Table C and and SUM the InvoiceTotal, you get $300, not $100. That's because there were 3 rows in Table B that related and the InvoiceToal from Table C was summed up 3 times.

    I see in your query that you join to InvoiceDetails yet you don't use any columns from that table. That in itself could easily cause your totals to go out of whack. I'm assuming that most invoices have more than 1 detail record.

    Todd Fifield

Viewing 9 posts - 1 through 9 (of 9 total)

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