Asta e procedura.
Daca inlocuiesc variabilele cu valori merge perfect.
Cred ca-mi scapa ceva acum.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE dbo.Nota_Inchidere
@data1 smalldatetime, @data2 smalldatetime, @nr int, @explicatii varchar(50)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM notainchidere;
INSERT INTO notaInchidere (credit, sumacredit)
SELECT credit, sum(suma) AS sumacredit FROM note_contabile
WHERE (data BETWEEN @data1 AND @data2) AND (LEFT(credit,1) = '6' OR LEFT(credit,1)='7')
GROUP BY credit;
GO
WITH rulajD (debit, suma) AS
(
SELECT debit, sum(suma) AS suma FROM note_contabile
WHERE (data BETWEEN @data1 AND @data2) AND (LEFT(debit,1) = '6' OR LEFT(debit,1)='7')
GROUP BY debit
)
UPDATE notainchidere SET sumadebit = rulajd.suma FROM notainchidere LEFT JOIN rulajD
ON notainchidere.credit = rulajd.debit;
GO
WITH rulajD (debit, suma) AS
(
SELECT debit, sum(suma) AS suma FROM note_contabile
WHERE (data BETWEEN @data1 AND @data2) AND (LEFT(debit,1) = '6' OR LEFT(debit,1)='7')
GROUP BY debit
)
INSERT INTO notainchidere (debit, sumadebit)
SELECT debit, suma FROM rulajD WHERE rulajD.debit NOT IN (SELECT credit FROM notainchidere);
GO
UPDATE notainchidere SET suma = sumadebit - ISNULL(sumacredit,0), credit =
CASE
WHEN Substring(debit,5,2)='01' THEN '121001'
WHEN Substring(debit,5,2)='02' THEN '121010'
WHEN Substring(debit,5,2)='03' THEN '121011'
END
WHERE LEFT(debit,1) = '6';
GO
UPDATE notainchidere SET suma = sumacredit - ISNULL(sumadebit,0), debit =
CASE
WHEN Substring(credit,1,4)='7701' THEN '121001'
WHEN Substring(credit,1,4)='7650' THEN '121001'
WHEN Substring(credit,1,4)='7511' THEN '121010'
WHEN Substring(credit,1,4)='7790' THEN '121011'
END
WHERE LEFT(debit,1) <> '6';
GO
UPDATE NotaInchidere SET data = @data2
INSERT INTO note_contabile
(
nr,
document,
data,
explicatii,
debit,
credit,
suma
)
SELECT @nr AS nr, 'Nota inchidere' AS document, data, @explic AS explicatii,
debit AS credit, credit AS debit, suma FROM notainchidere
END
GO
Sorin Sandu