Database

kenn_rosie wrote:

What is a named variable and why use it?

SELECT TOP 10
    Customers.customerid,
    Customers.customername,
    Log.LastModified
FROM
    Customers
INNER JOIN
    (SELECT customerId, Max(dateModified) AS LastModified
     FROM CustomerLogs
     GROUP BY customerId) as Log
    ON (Customers.customerId = Log.customerId)
ORDER BY LastModified DESC

SELECT Type,Balance,Category
FROM Account
ORDER BY (CASE WHEN Category ="Current Assets" THEN 1
          ELSE WHEN Category = 'Net Property and Equipment'THEN 2
          ELSE WHEN Category ='Non current Assets' THEN 3
          ELSE WHEN Category ='Current Libality' THEN 4
          ELSE WHEN Category = Long Term Liablities'THEN 5
          ELSE WHEN Category = 'Equity' THEN 6
          ELSE WHEN Category = 'Sales' THEN 7
          ELSE WHEN Category ='Cost of Sales' THEN 8
          ELSE 0 END);

satishrg wrote:

Can any one explain me why I got this error

satishrg wrote:

How to resolve this error

SqlCommand cmd = new SqlCommand("Reportes_RecoleccionHistoricaDiaria",
    App.GetConnection(false));
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ID_semana1", weekFrom);
cmd.Parameters.Add("@ID_semana2", weekTo);
 
cmd.CommandTimeout = 300;

SqlDataAdapter da = new SqlDataAdapter(cmd);
ds = new DataSet("RecoleccionHistoricaDiaria");
da.Fill(ds);

CREATE PROCEDURE [dbo].[Reportes_RecoleccionHistoricaDiaria]
(
    @ID_semana1 int,
    @ID_semana2 int
)
AS
BEGIN
 
    DECLARE @Abonos TABLE (ID_semana int, ID_cliente int, Abono int)
    INSERT INTO @Abonos
        SELECT R.ID_semana, R.ID_cliente, MIN(R.ID_abono)
          FROM Recibos R
          WHERE R.Estatus = 1 AND R.ID_semana >= @ID_semana1 AND R.ID_semana <= @ID_semana2
          GROUP BY R.ID_semana, R.ID_cliente
 
    DECLARE @AbonosPorDia TABLE (ID_semana int, DiaPago int, Pagados int)
    INSERT INTO @AbonosPorDia
        SELECT OS.ID_semana, Recibos.DiaPago, COUNT(Recibos.ID_recibo) AS Pagados
          FROM Operacion_Semanas OS
          INNER JOIN Recibos ON OS.ID_semana = Recibos.ID_semana
          INNER JOIN @Abonos A ON Recibos.ID_abono = A.Abono
          WHERE OS.ID_semana >= @ID_semana1 AND OS.ID_semana <= @ID_semana2
          GROUP BY OS.ID_semana, Recibos.DiaPago
          ORDER BY OS.ID_semana
 
    SELECT OS.ID_semana, OS.Fecha,
        (
            SELECT COUNT(ID_recibo)
              FROM Recibos
              WHERE Extra = 0 AND ID_semana = OS.ID_semana
        ) AS Emitidos,
        (ISNULL(
            (SELECT Pagados FROM  @AbonosPorDia T
              WHERE T.ID_semana = OS.ID_semana AND T.DiaPago = 4
        ), 0)) AS Jueves,
        (ISNULL(
            (SELECT Pagados FROM  @AbonosPorDia T
              WHERE T.ID_semana = OS.ID_semana AND T.DiaPago = 5
        ), 0)) AS Viernes,
        (ISNULL(
            (SELECT Pagados FROM  @AbonosPorDia T
              WHERE T.ID_semana = OS.ID_semana AND T.DiaPago = 6
        ), 0)) AS Sabado,
        (ISNULL(
            (SELECT Pagados FROM  @AbonosPorDia T
              WHERE T.ID_semana = OS.ID_semana AND T.DiaPago = 0
        ), 0)) AS Domingo,
        (ISNULL(
            (SELECT Pagados FROM  @AbonosPorDia T
              WHERE T.ID_semana = OS.ID_semana AND T.DiaPago = 1
        ), 0)) AS Lunes,
        (ISNULL(
            (SELECT Pagados FROM  @AbonosPorDia T
              WHERE T.ID_semana = OS.ID_semana AND T.DiaPago = 2
        ), 0)) AS Martes,
        (ISNULL(
            (SELECT Pagados FROM  @AbonosPorDia T
              WHERE T.ID_semana = OS.ID_semana AND T.DiaPago = 3
        ), 0)) AS Miercoles
      FROM Operacion_Semanas OS
      WHERE OS.ID_semana >= @ID_semana1 AND OS.ID_semana <= @ID_semana2
 
END

Week    Day     Count
---------------------
1        0       100
1        1       123
2        0       122
2        1       134

Week     Total Day 0   Day 1
----------------------------
1         589   100     123 ....
2         628   122     134 ....

EXEC	Reportes_RecoleccionHistoricaDiaria
		@ID_semana1 = 1,
		@ID_semana2 = 100

GO

CREATE TABLE #Abonos (ID_semana int, ID_cliente int, Abono int)
INSERT INTO #Abonos
    SELECT R.ID_semana, R.ID_cliente, MIN(R.ID_abono)
      FROM Recibos R
      WHERE R.Estatus = 1 AND R.ID_semana >= @ID_semana1 AND R.ID_semana <= @ID_semana2
      GROUP BY R.ID_semana, R.ID_cliente

CREATE TABLE #AbonosPorDia (ID_semana int, DiaPago int, Pagados int)
INSERT INTO #AbonosPorDia
    SELECT OS.ID_semana, Recibos.DiaPago, COUNT(Recibos.ID_recibo) AS Pagados
      FROM Operacion_Semanas OS
      INNER JOIN Recibos ON OS.ID_semana = Recibos.ID_semana
      INNER JOIN #Abonos A ON Recibos.ID_abono = A.Abono
      WHERE OS.ID_semana >= @ID_semana1 AND OS.ID_semana <= @ID_semana2
      GROUP BY OS.ID_semana, Recibos.DiaPago
      ORDER BY OS.ID_semana

nguyenvhn wrote:

My email address: happy#rainy@gmail#com