Query for calculating sum of negative and +ive value.
-
Hi All
This is my query:-
SELECT NBillingF.Customer, SUM(NBillingF.InvoicedValue) ASTotalSales,Count(distinct Invoice) as NoOfInvoices, TimeDates.Month
FROM NBillingF INNER JOIN
TimeDates ON NBillingF.DateInvoiced = TimeDates.S21Date
WHERE (TimeDates.Year = 2006) AND (TimeDates.Month = 05) AND (NBillingF.Customer IN ('ARG000',
'BO08', 'CO86', 'DA55', 'DIX5', 'DS01', 'INS4', 'IT01', 'NEA8', 'PRO0', 'QV01', 'STA1', 'S008', 'SS10', 'TE02',
'VOH001', 'XMA001')) AND (NBillingF.InvoicedValue > 0)
GROUP BY NBillingF.Customer, TimeDates.Month
ORDER BY NBillingF.Customer
According to my query it's showing only Sum of +ive values means debits only ,but i want to show Six fields having two more fields sum of credit notes with condition 'invoicedvalue<0' and count of number of invoices.
I have only one table which shows both +ive and -Ive values and in a single field i have invoice numbers.
Thanks
-
You could LEFT JOIN this table with itself for which you only want to join if your data field is either positive or negative. Something like this:
SELECT NBillingF.Customer, SUM(ISNULL(NBPos.InvoicedValue, 0)) ASTotalSalesPositive, SUM(ISNULL(NBNeg.InvoicedValue, 0)) ASTotalSalesNegative
FROM NBillingF
INNER JOIN TimeDates ON NBillingF.DateInvoiced = TimeDates.S21Date
LEFT JOIN NBillingF NBPos ON NBPos.ID = NBillingF.ID AND NBPos.InvoicedValue > 0
LEFT JOIN NBillingF NBNeg ON NBNeg.ID = NBillingF.ID AND NBNeg.InvoicedValue < 0
WHERE (TimeDates.Year = 2006) AND (TimeDates.Month = 05) AND (NBillingF.Customer IN ('ARG000',
'BO08', 'CO86', 'DA55', 'DIX5', 'DS01', 'INS4', 'IT01', 'NEA8', 'PRO0', 'QV01', 'STA1', 'S008', 'SS10', 'TE02', 'VOH001', 'XMA001'))
GROUP BY NBillingF.Customer, TimeDates.Month
ORDER BY NBillingF.Customer
Hope it helps...
-
Thanks for reply
But not working output is not right. Any other ideas please..........
-
Marino, there is no need for ISNULLs because you have already eliminated all NULLs by "> 0" and "< 0" conditions.
Vandy, what's not working?
Query from Marino seems OK.
I don't see any point of GROUP BY ... TimeDates.Month because TimeDates.Month is included in WHERE clause, so there may be only one month.
But it does not affect result.
So you better inspect your data.
_____________
Code for TallyGenerator -
Vandy... how about telling us which columns the +ive and =ive numbers are stored in? If they are both in the InvoicedValue column, then Marino's should have done the trick nicely... what's not working in it?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry, query is right and it's running well but output means total sum is not right.
it is sales table so one field has credit and debit entries both.
-
Sorry Vandy... I just can't read minds today... what's not right about the total sum?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Vandy..I don't see anything wrong in the query but I need one clarification. Is the credit and debit stored as postive value in the database (with another column being an indicator)? If so then your query might not return what you expect.
Thanks
Sreejith
-
Here is the sample data :-
Customer DateInvoiced InvoiceNumber InvoicedValueBase INT11 1060501 K25257 36.74 IND02 1060501 K25260 89.28 ELE05 1060501 K87635 80.7 ELE06 1060501 D25209 -15.45 ELE07 1060501 K25212 127.66 CHI23 1060501 D52623 -3019.2 RED00 1060501 K25229 158.2 HOR06 1060501 K25244 208.08 SK016 1060501 D22647 -178.2 SK016 1060501 K25647 112.7 SK016 1060501 D22647 -56 EL035 1060501 K22649 54.6 I also tried the same thing before posting my query don't know why total is different.
Thanks
-
I'm not sure if I understand you correct. Your sample data shows only one customer (sk016) with more than one (three) record. This would result in TotalSalesPositive = 112.7 and TotalSalesNegative = -234.2 for that customer. What exactly do you get as a result? Or what SUM exactly is wrong?
-
Tell me whether InvoiceNumber is diff. for credits(-ve values) and debits(+ve). If this is case then is very simple....
-
Thanks a lot For reply
Table data which i posted is only a sample data i have arround 40,000 lines for this set of customers.
Total for debit note for customer code 'DA55' with simple query is '1343861.48' (Which is right value) and when i run this query of Marino:-
SELECT NBillingF.Customer, SUM(ISNULL(NBPos.InvoicedValue, 0)) ASTotalSalesPositive, SUM(ISNULL(NBNeg.InvoicedValue, 0)) ASTotalSalesNegative
FROM NBillingF
LEFT JOIN NBillingF NBPos ON NBPos.ID = NBillingF.ID AND NBPos.InvoicedValue > 0
LEFT JOIN NBillingF NBNeg ON NBNeg.ID = NBillingF.ID AND NBNeg.InvoicedValue < 0
WHERE (NBillingF.DateInvoiced between 1060501 and 1060531) AND (NBillingF.Customer IN ('ARG000',
'BO08', 'CO86', 'DA55', 'DIX5', 'DS01', 'INS4', 'IT01', 'NEA8', 'PRO0', 'QV01', 'STA1', 'S008', 'SS10', 'TE02', 'VOH001', 'XMA001'))
GROUP BY NBillingF.Customer
ORDER BY NBillingF.Customer
I got total of debit notes for DA55 is '8696671.50'.
I extract the table timedates also it doesn't make ant difference.
Vitthal, My Invoices start with letter 'K' and followed by number and Credit notes Start with Letter 'D' .
Regards
-
I guess your ID in table NBillingF is not actually ID, that's why join on this ID brings a lot of duplications and wrong value as a result.
Let's simplify Marino's query:
SELECT NB.Customer, TimeDates.Month,
SUM(case when NB.InvoicedValue>0 then NB.InvoicedValue else 0 end) AS TotalDebits,
SUM(case when NB.InvoicedValue<0 then NB.InvoicedValue else 0 end) AS TotalCredits,
Count(distinct Invoice) as NoOfInvoices, TimeDates.Month
FROM NBillingF NB INNER JOIN
TimeDates ON NB.DateInvoiced = TimeDates.S21Date
WHERE (TimeDates.Year = 2006) --AND (TimeDates.Month = 05)
AND (NB.Customer IN ('ARG000', 'BO08', 'CO86', 'DA55', 'DIX5', 'DS01', 'INS4', 'IT01', 'NEA8', 'PRO0', 'QV01', 'STA1', 'S008', 'SS10', 'TE02', 'VOH001', 'XMA001'))
GROUP BY NB.Customer, TimeDates.Month
ORDER BY NB.Customer
This nust return you right totals.
_____________
Code for TallyGenerator -
Hi Sergiy
Thanks a lot it's working now. Yah you are right my ID field is not actually Unique. After reading your post i checked my ID field and find out it's repeating during we get backorders.
Thanks a lot..........
Regards
Vandana
-
SELECT Customer, SUM(InvoiceAmt) TotalSales, COUNT(Invoice) NoOfInvoices, Month, SUM(CreditNoteAmt) TotalCreditNote,
COUNT(CreditNote) NoOfCreditNotes
FROM
(SELECT NBillingF.Customer,
CASE WHEN NBillingF.InvoicedValue > 0 THEN NBillingF.InvoicedValue ELSE 0 END AS InvoiceAmt,
CASE WHEN NBillingF.InvoicedValue > 0 THEN 1 ELSE NULL END as Invoice,
TimeDates.Month,
CASE WHEN NBillingF.InvoicedValue > 0 THEN NBillingF.InvoicedValue ELSE 0 END AS CreditNoteAmt,
CASE WHEN NBillingF.InvoicedValue > 0 THEN 1 ELSE NULL END as CreditNote,
FROM NBillingF INNER JOIN TimeDates ON NBillingF.DateInvoiced = TimeDates.S21Date
WHERE TimeDates.Year = 2006 AND TimeDates.Month = 05 AND NBillingF.Customer IN ('ARG000',
'BO08', 'CO86', 'DA55', 'DIX5', 'DS01', 'INS4', 'IT01', 'NEA8', 'PRO0', 'QV01', 'STA1', 'S008', 'SS10', 'TE02',
'VOH001', 'XMA001')) t
GROUP BY NBillingF.Customer, TimeDates.Month
ORDER BY NBillingF.Customer
try it.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply