| 1 | --DECLARE @rok INT
|
|---|
| 2 | --DECLARE @miesiac INT
|
|---|
| 3 | --DECLARE @systemKsiegowy INT
|
|---|
| 4 |
|
|---|
| 5 | --SET @rok=2009
|
|---|
| 6 | --SET @miesiac=3
|
|---|
| 7 | --SET @systemKsiegowy=1
|
|---|
| 8 |
|
|---|
| 9 | SELECT FAKTURY.ID_SPRZEDAWCY,
|
|---|
| 10 | FAKTURY.ID_NABYWCY,
|
|---|
| 11 | FAKTURY.NUMER,
|
|---|
| 12 | FAKTURY.NUMER_ROZ,
|
|---|
| 13 | YEAR(FAKTURY.DATA_SPRZEDAZY) AS ROK,
|
|---|
| 14 | MONTH(FAKTURY.DATA_SPRZEDAZY) AS MS,
|
|---|
| 15 | FAKTURY.DATA_SPRZEDAZY,
|
|---|
| 16 | CONVERT(VARCHAR, FAKTURY.NUMER) + '/' + CONVERT(VARCHAR, FAKTURY.NUMER_ROZ) + '/' + CONVERT(VARCHAR, FAKTURY.NUMER_ROK) AS NR_FK,
|
|---|
| 17 | NETTO =
|
|---|
| 18 | CASE
|
|---|
| 19 | WHEN K.S_VAT IS NULL THEN SUM(FAKTURA_DETAILS.NETTO)
|
|---|
| 20 | ELSE K.Netto
|
|---|
| 21 | END,
|
|---|
| 22 | S_VAT =
|
|---|
| 23 | CASE
|
|---|
| 24 | WHEN K.S_VAT IS NULL THEN FAKTURA_DETAILS.S_VAT
|
|---|
| 25 | ELSE K.S_VAT
|
|---|
| 26 | END,
|
|---|
| 27 | VAT =
|
|---|
| 28 | CASE
|
|---|
| 29 | WHEN K.S_VAT IS NULL THEN SUM(FAKTURA_DETAILS.NETTO * FAKTURA_DETAILS.S_VAT)
|
|---|
| 30 | ELSE (K.Netto * K.S_VAT)
|
|---|
| 31 | END,
|
|---|
| 32 | BRUTTO =
|
|---|
| 33 | CASE
|
|---|
| 34 | WHEN K.S_VAT IS NULL THEN SUM(FAKTURA_DETAILS.NETTO * (1 + FAKTURA_DETAILS.S_VAT))
|
|---|
| 35 | ELSE (K.Netto * (1 + K.S_VAT))
|
|---|
| 36 | END,
|
|---|
| 37 | FAKTURY.suma_zaplat,
|
|---|
| 38 | FAKTURY.ID_FAKTURY,
|
|---|
| 39 | Symbol =
|
|---|
| 40 | CASE
|
|---|
| 41 | WHEN AGENCI.Symbol IS NULL THEN
|
|---|
| 42 | CASE WHEN FAKTURY.NUMER_ROZ = 'POZ' THEN 'przy³ucki' ELSE 'wodzu' END
|
|---|
| 43 | ELSE AGENCI.Symbol
|
|---|
| 44 | END,
|
|---|
| 45 | AGENCJE.Symbol AS Agencja
|
|---|
| 46 | FROM FAKTURY
|
|---|
| 47 | INNER JOIN FAKTURA_DETAILS ON FAKTURY.ID_FAKTURY = FAKTURA_DETAILS.ID_FAKTURY
|
|---|
| 48 | LEFT OUTER JOIN AGENCI ON FAKTURY.NUMER_ROZ = AGENCI.F_ROZ
|
|---|
| 49 | LEFT OUTER JOIN AGENCJE ON FAKTURY.ID_SPRZEDAWCY = AGENCJE.Id_agencji
|
|---|
| 50 | LEFT OUTER JOIN VIEW_KOREKTY K ON FAKTURY.ID_Faktury = K.idFakturyVat
|
|---|
| 51 | WHERE (YEAR(FAKTURY.DATA_SPRZEDAZY) = @rok)
|
|---|
| 52 | AND (MONTH(FAKTURY.DATA_SPRZEDAZY) = @miesiac)
|
|---|
| 53 | AND (FAKTURY.KOREKTA = 0)
|
|---|
| 54 | AND (FAKTURY.SystemKsiegowyId IN (@systemKsiegowy))
|
|---|
| 55 | GROUP BY
|
|---|
| 56 | FAKTURY.ID_SPRZEDAWCY,
|
|---|
| 57 | AGENCJE.Symbol,
|
|---|
| 58 | AGENCI.Symbol,
|
|---|
| 59 | FAKTURY.ID_NABYWCY,
|
|---|
| 60 | FAKTURY.NUMER,
|
|---|
| 61 | FAKTURY.NUMER_ROZ,
|
|---|
| 62 | YEAR(FAKTURY.DATA_SPRZEDAZY),
|
|---|
| 63 | FAKTURY.DATA_SPRZEDAZY,
|
|---|
| 64 | CONVERT(VARCHAR, FAKTURY.NUMER) + '/' + CONVERT(VARCHAR, FAKTURY.NUMER_ROZ) + '/' + CONVERT(VARCHAR, FAKTURY.NUMER_ROK),
|
|---|
| 65 | FAKTURY.suma_zaplat,
|
|---|
| 66 | FAKTURA_DETAILS.S_VAT,
|
|---|
| 67 | FAKTURY.ID_FAKTURY,
|
|---|
| 68 | K.Netto, K.S_VAT
|
|---|
| 69 | ORDER BY FAKTURY.NUMER |
|---|
