Add records of a DataTable

Question

Add records of a DataTable

Navigation

#1 by (2 votes)
#2 by (1 votes)

3

Dear good afternoon, someone can help me with the following problem ... I need to make an sql query on my system in Visual Basic, which sums up all the amounts registered when the field called "typedocument" is invoice or ticket, but When the field is a credit note, subtract it.

THE CODE IS THE FOLLOWING

Dim varconexion = New SqlConnection(cadena)
    varconexion.Open()
    Dim DT As New DataTable

    Dim DA As New SqlDataAdapter("SELECT numdocpago AS NUM, tipodocpago AS TIPO, fechadocpago AS FECHA, montodocpago AS MONTO, detalledocpago AS DETALLE, numordcomp AS ORDEN_COMPRA, numctactble AS CTA_CTBLE, rutprov AS RUT, digitoprov AS DIG, nomprov AS PROVEEDOR FROM docpago WHERE numdecpago = '" & tbxNumDecreto.Text & "' AND fechadecpago = '" & dtpFechaDecreto.Text & "' AND nomdepto = '" & cbxDepto.Text & "' ORDER BY numdocpago", varconexion)
    DA.Fill(DT)
    dgvVisualizaPago.DataSource = DT

    varconexion.Close()

    'SUMAR COLUMNA
    dgvVisualizaPago.Columns(3).DefaultCellStyle.Format = "##,##0"
    Dim ddt As DataTable = DirectCast(dgvVisualizaPago.DataSource, DataTable)

    Dim sumMONTO As Object = ddt.Compute("SUM(MONTO)", Nothing) 'aqui quiero el codigo, actualmente solo entrega una suma, pero quiero hacer el filtro antes mencionado para restar si el tipodocpago es = "NOTA DE CREDITO"

    lblMontoTotal.Text = Nothing

    If sumMONTO Is DBNull.Value Then
        sumMONTO = "0"
    Else
        lblMontoTotal.Text = sumMONTO
        lblMontoTotal.Text = Convert.ToDecimal(lblMontoTotal.Text).ToString("N0")
    End If

vb.net

asked by Francisco 10.08.2017 в 18:17

source

2 answers

1

One way to do what you need through a query would be something like this:

SELECT (factura+boleta-credito) AS total

FROM

(SELECT 
      CASE WHEN tipodocumento = 'factura' THEN SUM(montodocumento) ELSE 0 END AS 
      factura,
      CASE WHEN tipodocumento = 'boleta' THEN SUM(montodocumento) ELSE 0 END AS 
      boleta, 
      CASE WHEN tipodocumento = 'nota de credito' THEN SUM(montodocumento) ELSE 0 END AS credito

FROM docspago 

GROUP BY tipodocumento)

answered by 10.08.2017 в 18:52

Copy structure of MySQL tables without the data Transform an "exec ()" of python into str

score 2 · Accepted Answer

Ah ok, what you need is that in your DataTable the operation is done, I'm not sure if Compute can be made since the condition you have is not trivial.

What you can do is add via LINQ transformed your datatable to enumerable

It would be something like that

'Suma positivo
Dim cargoTotal = ddt .AsEnumerable().Where(Function(c) c.Field(Of String)("TIPO") = "factura"  Or c.Field(Of String)("TIPO") = "boleta").Sum(Function(c) c.Field(Of Integer)("MONTO"))

'Suma negativo
Dim abonoTotal = ddt .AsEnumerable().Where(Function(c) c.Field(Of String)("TIPO") = "credito").Sum(Function(c) c.Field(Of Integer)("MONTO"))

And you just have to subtract them to get the amount you want. Maybe there's a way to do it only in a query, but since it's been a while since I last played the VB.NET, it can at least help you.

First response

You can use something like this:

SELECT 

SUM(
  CASE WHEN tipodocumento = 'factura' OR tipodocumento = 'boleta' 
  THEN montodocumento -- aqui suma
  WHEN tipodocumento = 'credito' 
  THEN  montodocumento * -1 END --aqui resta (puede ser tambien -montodocumento )
)

FROM MITABLA

Here we use a CASE with the condition you ask, if you invoice or sum ticket

And maybe in the end you should use a group by for some element