| 11 | | update dbo.PLAN_TYLKO_CT set |
| 12 | | w_budzet=wk_budzet, |
| 13 | | w_spotkania=spotkanie, |
| 14 | | w_tel=tel, |
| 15 | | w_email=email, |
| 16 | | w_list=list, |
| 17 | | w_fax=fax, |
| 18 | | w_n_klienci=nkl, |
| 19 | | w_klienci=il_klientow, |
| 20 | | w_reklamy=il_reklam |
| 21 | | from |
| 22 | | ( |
| 23 | | --WYLICZENIE ILOSCI KONTAKTOW ITP |
| 24 | | SELECT ROK, MS, AGENT, IL_KONTKTOW, IL_KLIENTOW, SPOTKANIE, FAX, EMAIL, LIST, TEL , |
| 25 | | ISNULL(IL_REKLAM,0) AS IL_REKLAM, ROUND(ISNULL(BUDZET,0) ,0) AS wk_BUDZET, ISNULL(NKL,0) AS NKL, HANDEL, PRODUKT, RYNEK, FIRMA |
| 26 | | FROM |
| 27 | | ( |
| 28 | | SELECT ROK , MS, AGENT, COUNT(Id_kontaktu) AS IL_KONTKTOW, COUNT(DISTINCT customerId) AS IL_KLIENTOW, |
| 29 | | |
| 30 | | SUM( CASE WHEN RODZAJ='TEL' THEN 1 ELSE 0 END) AS TEL, |
| 31 | | SUM( CASE WHEN RODZAJ='FAX' THEN 1 ELSE 0 END) AS FAX, |
| 32 | | SUM( CASE WHEN RODZAJ='LIST' THEN 1 ELSE 0 END) AS LIST, |
| 33 | | SUM( CASE WHEN RODZAJ='E-MAIL' THEN 1 ELSE 0 END) AS EMAIL, |
| 34 | | SUM( CASE WHEN RODZAJ='SPOTKANIE' THEN 1 ELSE 0 END) AS SPOTKANIE |
| 35 | | FROM |
| 36 | | ( |
| 37 | | SELECT dbo.KONTAKTY.Id_kontaktu, dbo.KONTAKTY.rodzaj as RODZAJ, |
| 38 | | symbol_agenta AS AGENT, dbo.KONTAKTY.customerId, |
| 39 | | DATEPART(YEAR, dbo.KONTAKTY.data) AS ROK, |
| 40 | | DATEPART(Month, dbo.kontakty.data) As MS |
| 41 | | FROM dbo.KONTAKTY |
| 42 | | WHERE DATEPART(YEAR, dbo.KONTAKTY.data) = @ROK and DATEPART(Month, dbo.kontakty.data) = @MS |
| 43 | | ) KO |
| 44 | | GROUP BY ROK, MS , AGENT |
| 45 | | ) P_K |
| 46 | | --WYLICZENIE ILOSCI REKLAM |
| 47 | | FULL OUTER JOIN |
| 48 | | ( |
| 49 | | SELECT [SYMBOL AKWIZYTORA] AS R_AGENT, COUNT(*) AS IL_REKLAM_1, |
| 50 | | DATEPART(YEAR,[DATA ZAMÓWIENIA]) AS R_ROK, |
| 51 | | DATEPART(MONTH,[DATA ZAMÓWIENIA]) AS R_MS |
| 52 | | FROM REKLAMA |
| 53 | | WHERE |
| 54 | | DATEPART(YEAR,[DATA ZAMÓWIENIA])=@ROK AND |
| 55 | | DATEPART(MONTH,[DATA ZAMÓWIENIA])=@MS AND |
| 56 | | [ZATWIERDZONO DO DRUKU]=1 |
| 57 | | GROUP BY [SYMBOL AKWIZYTORA] , DATEPART(YEAR,[DATA ZAMÓWIENIA]), DATEPART(MONTH,[DATA ZAMÓWIENIA]) |
| 58 | | ) P_R |
| 59 | | ON R_AGENT=AGENT AND ROK=R_ROK AND MS=R_MS |
| 60 | | FULL OUTER JOIN |
| 61 | | ( |
| 62 | | SELECT USERNAME, DATEPART(YEAR,DATA) AS K_ROK, DATEPART(MONTH,DATA) AS K_MS, COUNT(*) AS NKL FROM KLIENCI |
| 63 | | WHERE DATEPART(YEAR,DATA)=@ROK AND DATEPART(MONTH,DATA)=@MS |
| 64 | | GROUP BY USERNAME, DATEPART(YEAR,DATA), DATEPART(MONTH,DATA) |
| 65 | | ) AS P_NK |
| 66 | | ON USERNAME=AGENT AND ROK=K_ROK AND MS=K_MS |
| 67 | | --WYLICZENIE BUD¯ETU |
| 68 | | FULL OUTER JOIN |
| 69 | | ( |
| 70 | | SELECT B_ROK, B_MS, |
| 71 | | Sum(NETTO) AS BUDZET, |
| 72 | | count(LiczbaReklam) as IL_REKLAM, |
| 73 | | b_agent as B_AGENT |
| 74 | | FROM dbo.VIEW_POMOCNICZY_BUDZET |
| 75 | | where B_ROK =@ROK and |
| 76 | | B_MS =@MS |
| 77 | | GROUP BY B_ROK, B_MS,B_AGENT ) P_B |
| 78 | | ON B_AGENT=AGENT AND ROK=B_ROK AND MS=B_MS |
| 79 | | -- WYLICZENIE DANYCH DO TESTU WIEDZY |
| 80 | | FULL OUTER JOIN |
| 81 | | ( |
| 82 | | SELECT user_name AS T_AGENT, DATEPART(YEAR,[DATA]) AS T_ROK, DATEPART(MONTH,[DATA]) AS T_MS, |
| 83 | | SUM(HANDEL) AS HANDEL, SUM(PRODUKT) AS PRODUKT, SUM(RYNEK) AS RYNEK, SUM(FIRMA) AS FIRMA |
| 84 | | FROM VIEW_ZESTAWIENIE_TEST_WIEDZY |
| 85 | | WHERE |
| 86 | | DATEPART(YEAR,[DATA])=@ROK AND |
| 87 | | DATEPART(MONTH,[DATA])=@MS |
| 88 | | GROUP BY user_name, DATEPART(YEAR,[DATA]) , DATEPART(MONTH,[DATA]) |
| 89 | | ) P_T |
| 90 | | ON T_AGENT=AGENT AND ROK=T_ROK AND MS=T_MS |
| 91 | | ) wl |
| 92 | | --Koñcówka instrukcji Update |
| 93 | | inner join dbo.[PLAN_TYLKO_CT] on wl.agent=[dbo].[PLAN_TYLKO_CT].symbol and wl.rok=[dbo].[PLAN_TYLKO_CT].rok and wl.ms=[dbo].[PLAN_TYLKO_CT].ms |
| 94 | | |
