March 26, 2012 at 4:54 am
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!
March 26, 2012 at 5:39 am
Hi sclements,
Ur Quantity have null values try for isnull
Regards
Guru
March 26, 2012 at 7:02 am
Hi,
no joy with that I'm afraid!
March 26, 2012 at 8:37 am
Can you post the full query?
Thanks
March 26, 2012 at 8:40 am
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
March 26, 2012 at 9:02 am
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
March 26, 2012 at 9:14 am
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!
March 26, 2012 at 9:57 am
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
March 27, 2012 at 12:45 pm
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply