Nawigacja

Zbiór zmian 355 dla trunk/SQL

Data:

2009-02-16 13:31:06 (17 years ago)

Autor:

marek

Opis:

re #92

Lokalizacja:

trunk/SQL

Pliki:

r352	r355
1		~~-- =============================================~~
2		~~-- Create basic stored procedure template~~
3		~~-- =============================================~~
4	1
5		-- Drop stored procedure if it already exists
6		IF EXISTS (
7		SELECT *
8		FROM INFORMATION_SCHEMA.ROUTINES
9		WHERE SPECIFIC_SCHEMA = N'dbo'
10		AND SPECIFIC_NAME = N'sp_adMotoRaportProwizja'
11		)
12		DROP PROCEDURE dbo.sp_adMotoRaportProwizja
	2	SET ANSI_NULLS ON
	3	GO
	4	SET QUOTED_IDENTIFIER ON
13	5	GO
14	6
15		CREATE PROCEDURE dbo.sp_adMotoRaportProwizja
	7	ALTER PROCEDURE [dbo].[sp_adMotoRaportProwizja]
16	8	@rok int,
17	9	@miesiac int,
18		@tytul nvarchar(100)
	10	@tytul nvarchar(100),
	11	@prowizjaPoz money,
	12	@prowizjaKat money
19	13	AS
20
21	14	DECLARE @Wplywy TABLE
22	15	(
…	…
60	53	AND (Tytul IN (@tytul))
61	54
	55	IF @prowizjaPoz IS NOT NULL
	56	BEGIN
	57	UPDATE @Wplywy
	58	SET Prowizja=ROUND(NETTO * @prowizjaPoz, 2)
	59	WHERE Agencja='GS O/POZNAÑ'
	60	END
	61
	62	IF @prowizjaKat IS NOT NULL
	63	BEGIN
	64	UPDATE @Wplywy
	65	SET Prowizja=ROUND(NETTO * @prowizjaKat, 2)
	66	WHERE Agencja='GS O/KATOWICE'
	67	END
	68
	69
62	70	UPDATE @Wplywy
63	71	SET Netto = NULL
…	…
86	94	WHERE IdFaktury NOT IN (SELECT IdFaktury FROM @Wplywy)
87	95	ORDER BY DataWplaty, Agent, NumerFaktury
88
89
90	96	GO
91	97
92		-- =============================================
93		-- Example to execute the stored procedure
94		-- =============================================
95		EXECUTE dbo.sp_adMotoRaportProwizja 2009, 1, 'adMot'
	98	SET ANSI_NULLS OFF
96	99	GO
	100	SET QUOTED_IDENTIFIER OFF
	101	GO
	102

r354	r355
7	7	ALTER VIEW [dbo].[ReklamyWyroznienie] AS
8	8	SELECT
	9	F.ID_FAKTURY AS IdFaktury,
	10	F.Numer AS NumerFaktury,
9	11	YEAR(F.DATA_SPRZEDAZY) AS Rok,
10	12	MONTH(F.DATA_SPRZEDAZY) AS Miesiac,
11	13	AGENCJE.Symbol AS Agencja,
12	14	LOWER(AGENCI.Symbol) AS Agent,
13		~~--AGENCI.Nazwisko + ' ' + AGENCI.Imiê AS Agent,~~
14	15	R.[ID REKLAMY] AS IdReklamy,
15		CONVERT(VARCHAR, F.NUMER) + '/' + CONVERT(VARCHAR, F.NUMER_ROZ) + '/' + CONVERT(VARCHAR, F.NUMER_ROK) AS NumerFaktury,
	16	CONVERT(VARCHAR, F.NUMER) + '/' + CONVERT(VARCHAR, F.NUMER_ROZ) + '/' + CONVERT(VARCHAR, F.NUMER_ROK) AS SymbolFaktury,
	17	F.DATA_Wystawienia AS DataFaktury,
	18	W.DataWplaty,
16	19	SUM(ROUND(0.15 * FD.NETTO / 1.15, 2)) AS Wyroznienie
17	20	FROM FAKTURY AS F
…	…
20	23	LEFT OUTER JOIN AGENCJE ON AGENCI.ID_AGENCJI = AGENCJE.Id_agencji
21	24	LEFT OUTER JOIN REKLAMA AS R ON FD.reklamaId = R.ReklamaID
	25	LEFT OUTER JOIN Wplaty AS W ON F.ID_FAKTURY = W.idFaktury
22	26	WHERE
23	27	--(YEAR(F.DATA_SPRZEDAZY) = @rok)
…	…
26	30	AND F.Numer_Roz <> 'POZ' AND F.Numer_Roz <> 'KAT'
27	31	GROUP BY
28		~~YEAR(F.DATA_SPRZEDAZY)~~,
29		~~MONTH(F.DATA_SPRZEDAZY)~~,
	32	F.ID_FAKTURY,
	33	F.DATA_SPRZEDAZY,
30	34	AGENCJE.Symbol,
31	35	AGENCI.Symbol,
…	…
34	38	F.NUMER,
35	39	F.NUMER_ROZ,
36		F.NUMER_ROK
	40	F.NUMER_ROK,
	41	F.Data_Wystawienia,
	42	W.DataWplaty
37	43	GO
38	44