Hi all,
I've got a query which returns all invoices and credit notes for a given client, in a loyalty structure query where I need the total value of the points the client achieved through invoices, minus those that was forfeited by credit notes.
Now I start my query like this (There are more unions in the query, this is just the first part)
SELECT 'Personal Purchases' as Generation, (SELECT SUM(BonusPoints * Quantity) FROM tInvoiceItem WHERE InvoiceID IN(SELECT InvoiceID FROM tInvoice WHERE IsPaid=1 AND InvoiceDate BETWEEN @StartDate AND @EndDate AND ClientID =@ClientID)) as TotalBP UNION ALL SELECT 'Personal Purchases' as Generation, (SELECT (SUM(BonusPoints * Quantity)*-1) FROM tCreditNoteItem WHERE CreditNoteID IN(SELECT CreditNoteID FROM tCreditNote WHERE IsPosted=1 AND CreditNoteDate BETWEEN @StartDate AND @EndDate AND ClientID =@ClientID)) as TotalBP
Currently, when I run this query it will return something like this:
Generation TotalBP
—————— ———–
Personal Purchases 7676
Personal Purchases -22000
Now I need some kind of group by clause or something to just give me the total for that generation "personal purchases", but the problems is that there's nothing that relates to each other to put the group by in. Any idea how I would be able to achieve this?
dp.SyntaxHighlighter.ClipboardSwf = ‘/dp.SyntaxHighlighter/Scripts/clipboard.swf’
dp.SyntaxHighlighter.HighlightAll(’0c5cb53773be44c38a6518a835320b20′)
Similar:
- Where to place if statement I have a design problem I could use some help determining how to code this problem. I have a table with 3 fields, catCode, startDate and endDate....
- Grouping fields by category and arranging by date, and calculating from another table I have a products table, with the fields product, category and cost, of type varchar, varchar and decimal. I then have a sales table, with...
- Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Clearly, when GROUP BY clause used, columns that are not aggregate function should be part of the group by clause. The problem here is, I...
- Creating An If Statement Hi guys I wonder if you could help me. I am trying to make an IF statement for the following fields: Quantity In Stock Quantity...
- C# How to create extension methods with lambda expressions Currently i am creating an extension method that accepts parameters. Using the below example, how could one convert this using lambda expressions? public static decimal...