May 16, 2006 at 1:56 pm
Hi,
I currently have the following, which if there are both Invoices and CreditNotes in the period, will return 2 values -
SELECT CASE WHEN Type = 0 THEN (SUM(NetSales)) ELSE (SUM(NetSales) * - 1) END
FROM dbo.Orders
GROUP BY MONTH(InvoiceDate), YEAR(InvoiceDate), Type
HAVING (MONTH(InvoiceDate) = @MonthNo) AND (YEAR(InvoiceDate) = @YearNo)
Where Type = 0 is Invoice and Type <> 0 is CreditNote.
How do I return a single value, being the sum of all the Invoices and all the CreditNotes for the period.
Many thanks
Tim
May 16, 2006 at 3:32 pm
Try this
SELECT
SUM(CASE WHEN Type = 0 THEN (SUM(NetSales)) ELSE (SUM(NetSales) * - 1) END)
FROM dbo.Orders
GROUP BY MONTH(InvoiceDate), YEAR(InvoiceDate), Type
HAVING (MONTH(InvoiceDate) = @MonthNo) AND (YEAR(InvoiceDate) = @YearNo)
Amit Lohia
May 16, 2006 at 8:28 pm
SELECT SUM(NetSales * CASE WHEN Type = 0 THEN 1 ELSE - 1 END)
FROM dbo.Orders
WHERE (MONTH(InvoiceDate) = @MonthNo) AND (YEAR(InvoiceDate) = @YearNo)
GROUP BY MONTH(InvoiceDate), YEAR(InvoiceDate) --, Type - remove type from grouping
Actually I don't see why you need GROUP BY at all
And I would not use WHERE clause causing table scan. If you don't want to make the query slow, use something like this:
WHERE InvoiceDate >= dateadd(mm, @Month-1, dateadd(yy, @year-1900, 0)) and InvoiceDate < dateadd(mm, @Month, dateadd(yy, @year-1900, 0))
_____________
Code for TallyGenerator
May 17, 2006 at 4:35 am
Hi Guys,
Many thanks for you input on this.
Amit - unfortunatly I get the following with your suggestion -
Server: Msg 130, Level 15, State 1, Line 3
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Sergity - I get the following if I remove Type from the Group By list
Server: Msg 8120, Level 16, State 1, Line 1
Column 'dbo.Orders.Type' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Or if I remove the Groum By entirely -
Server: Msg 8118, Level 16, State 1, Line 1
Column 'dbo.Orders.Type' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
I'm truely greatful for your ideas, but do you have any further thoughts please?
Many thanks
Tim
May 17, 2006 at 6:21 am
Tim,
I ran Sergiy's example, and it seems work:
--DROP TABLE Orders
GO
CREATE TABLE Orders
(
id int IDENTITY(1,1) PRIMARY KEY
, type int
, netsales decimal(12,4)
, invoiceDate datetime
)
GO
SET NOCOUNT ON
INSERT Orders ( type, netsales, invoicedate) VALUES ( 0, 123.45 , '20060502' )
INSERT Orders ( type, netsales, invoicedate) VALUES ( 0, 500.00 , '20060502' )
INSERT Orders ( type, netsales, invoicedate) VALUES ( 1, 200.25 , '20060503' )
INSERT Orders ( type, netsales, invoicedate) VALUES ( 0, 100.00 , '20060503' )
INSERT Orders ( type, netsales, invoicedate) VALUES ( 1, 350.00 , '20060504' )
INSERT Orders ( type, netsales, invoicedate) VALUES ( 0, 25.00 , '20060505' )
SET NOCOUNT OFF
GO
DECLARE @yearNo int, @monthNo int
SET @yearNo = 2006
SET @monthNo = 5
SELECT SUM(NetSales * CASE WHEN Type = 0 THEN 1 ELSE - 1 END)
FROM dbo.Orders
WHERE InvoiceDate >= DateAdd(mm, @MonthNo-1, DateAdd(yy, @yearNo-1900, 0))
AND InvoiceDate < DateAdd(mm, @MonthNo, DateAdd(yy, @yearNo-1900, 0))
GROUP BY MONTH(InvoiceDate), YEAR(InvoiceDate)
May 17, 2006 at 8:56 am
Oops, sorry I missed that Sergiy had changed the order on the Select line. So you are right it works just fine.
Very many thanks to you all for you help on this.
Tim
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply