none
Group and sum with tax RRS feed

  • Question

  • Hi,

    I have a table in sql server like this:
    IDInvoice | Description | Qty | UnitPrice | LineTotal | Tax% |
    50                     Prod1      7       1.5462    10.82          10
    50                     Prod2      7       1.6365    11.46          12
    50                     Prod3      3       1.4148     4.24           10
    51                     Prod4      11      1.4067    15.47         20
    51                     Prod5      15      1.7894    26.84         10
    51                     Prod6      20      1.2458    24.92         11

    I tried to make a piece of code, but I don't know how to finish.

     k = k.GroupBy(g => g.IDInvoice)
                            .Select(group => new DetailInvoice
                            {
                                LineTotal = group.Sum(w => w.Qty * w.UnitPrice)
                                     ... how to group with tax???        
                            }).ToArray();

    I have to group for IDInvoice and then for tax, so:
    (invoice 50) 2 products with 10% (10.82 + 4.24) and one with 12% (11.46)
    ==> [(10.82 + 4.24) * 1.10] = 16.57   THEN   11.46 * 1.12 = 12.84
        TOTAL INVOICE 16.57 + 12.84 => 29.41
    (invoice 51) the same process

    I would like to know the total for each invoice using LINQ and C#.

    any suggestion?

    thanks


    Wednesday, August 12, 2015 6:42 PM

Answers

  • Hi duppino,

    You could refer to below:

    var m = (from table in k
                             group table by table.IDInvoice into g
                             select new DetailInvoice
                             {
                                 IDINVOICE = g.Key.ToString(),
                                 TOTAL_LINE = g.ToList().Sum(s => (s.LineTotal * (1 + s.Tax_ / 100))).ToString(),
                                 TOTAL_TAX = g.ToList().Sum(s => s.LineTotal).ToString(),
                                 TOTAL_INVOICE = (g.ToList().Sum(s => (s.LineTotal * (1 + s.Tax_ / 100))) + g.ToList().Sum(s => s.LineTotal)).ToString()
                             }).ToList();

    Regards,
    Youjun Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by duppino Monday, September 14, 2015 3:17 AM
    Friday, August 14, 2015 5:18 AM

All replies

  • Hi,

    could you show the result you want to output?

    Thursday, August 13, 2015 10:08 AM
  • Hi _Omar,

    IDINVOICE | TOTAL_LINE | TOTAL_TAX | TOTAL_INVOICE

       50                  26.52              29.41              55.93

       51                  67.23                8.51             75.74

    It should be corrected, if I was not wrong in the calculations :)

    thks

    Thursday, August 13, 2015 10:56 AM
  • Hi duppino,

    You could refer to below:

    var m = (from table in k
                             group table by table.IDInvoice into g
                             select new DetailInvoice
                             {
                                 IDINVOICE = g.Key.ToString(),
                                 TOTAL_LINE = g.ToList().Sum(s => (s.LineTotal * (1 + s.Tax_ / 100))).ToString(),
                                 TOTAL_TAX = g.ToList().Sum(s => s.LineTotal).ToString(),
                                 TOTAL_INVOICE = (g.ToList().Sum(s => (s.LineTotal * (1 + s.Tax_ / 100))) + g.ToList().Sum(s => s.LineTotal)).ToString()
                             }).ToList();

    Regards,
    Youjun Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by duppino Monday, September 14, 2015 3:17 AM
    Friday, August 14, 2015 5:18 AM
  • Hi Youjun Tang

    but I have to calculate the tax after I did the sum of the rows (with the same VAT) not on each line.

    So, I have to group rows of the invoice to rate, make the amount and calculate the tax amount for each..

    correct?

    thks

    Tuesday, August 18, 2015 4:18 PM
  •             k = k
                        .GroupBy(g => new
                        {
                            g.Tax
                        })
                        .Select(group => new DetailsInvoice
                        {
                            Tax = group.Key.Tax,
                            TotLine = group.Sum(q => q.TotLine),
                        }).ToArray();

                    var j = k.Sum(x => (x.TotLine * x.Tax / 100) + x.TotLine);

                    return j;

    Before I group for rate and after calculating the sum.

    But how do I get a single statement?

    thks

    Saturday, August 22, 2015 9:35 PM
  • Hi duppino,

    What is the VAT?

    In my previous reply, the result of the linq will be like as

    IDINVOICE | TOTAL_LINE | TOTAL_TAX | TOTAL_INVOICE

       50                  26.52              29.41              55.93

       51                  67.23                8.51             75.74

    It has two records, you could get any of them if needed.

    Regards,
    Youjun Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 26, 2015 8:33 AM
  • Hi Youjun Tang,

    VAT is Value-added tax.

    I specified that if I have four products: 2 with a tax and 2 with another

    I must first calculate the total taxable amount.
    Then calculate the tax on this.
    And then added this two.

    From your example, I felt that I calculated the VAT on each line.. is not it?

    thanks

    Saturday, August 29, 2015 12:04 AM
  • Hi duppino,

    the VAT and Totline? these are not mentioned in your original post.

    If the requirements have been changed, you could show the new output you want. then you could post another new one for new issue,  and if some replies are helpful, you could mark as answer to end this case.

    Regards,
    Youjun Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 9, 2015 2:47 AM