| 180 | | |
| 181 | | union all |
| 182 | | --dodajemy SAMPRESS |
| 183 | | SELECT ROK, MS, AGENT, IL_KONTKTOW, IL_KLIENTOW, SPOTKANIE, FAX, EMAIL, LIST, TEL , |
| 184 | | ISNULL(IL_REKLAM,0) AS IL_REKLAM, ROUND(ISNULL(BUDZET,0) ,0) AS wk_BUDZET, ISNULL(NKL,0) AS NKL, HANDEL, PRODUKT, RYNEK, FIRMA |
| 185 | | FROM |
| 186 | | ( |
| 187 | | SELECT ROK , MS, AGENT, COUNT(Id_kontaktu) AS IL_KONTKTOW, COUNT(DISTINCT customerId) AS IL_KLIENTOW, |
| 188 | | |
| 189 | | SUM( CASE WHEN RODZAJ='TEL' THEN 1 ELSE 0 END) AS TEL, |
| 190 | | SUM( CASE WHEN RODZAJ='FAX' THEN 1 ELSE 0 END) AS FAX, |
| 191 | | SUM( CASE WHEN RODZAJ='LIST' THEN 1 ELSE 0 END) AS LIST, |
| 192 | | SUM( CASE WHEN RODZAJ='E-MAIL' THEN 1 ELSE 0 END) AS EMAIL, |
| 193 | | SUM( CASE WHEN RODZAJ='SPOTKANIE' THEN 1 ELSE 0 END) AS SPOTKANIE |
| 194 | | FROM |
| 195 | | ( |
| 196 | | SELECT truck_expo.dbo.KONTAKTY.Id_kontaktu, truck_expo.dbo.KONTAKTY.rodzaj as RODZAJ, |
| 197 | | symbol_agenta AS AGENT, truck_expo.dbo.KONTAKTY.customerId, |
| 198 | | DATEPART(YEAR, truck_expo.dbo.KONTAKTY.data) AS ROK, |
| 199 | | DATEPART(Month, truck_expo.dbo.kontakty.data) As MS |
| 200 | | FROM truck_expo.dbo.KONTAKTY |
| 201 | | WHERE DATEPART(YEAR, truck_expo.dbo.KONTAKTY.data) = @ROK and DATEPART(Month, truck_expo.dbo.kontakty.data) = @MS |
| 202 | | ) KO |
| 203 | | GROUP BY ROK, MS , AGENT |
| 204 | | ) P_K |
| 205 | | --WYLICZENIE ILOSCI REKLAM |
| 206 | | FULL OUTER JOIN |
| 207 | | ( |
| 208 | | SELECT [SYMBOL AKWIZYTORA] AS R_AGENT, COUNT(*) AS IL_REKLAM_1, |
| 209 | | DATEPART(YEAR,[DATA ZAMÓWIENIA]) AS R_ROK, |
| 210 | | DATEPART(MONTH,[DATA ZAMÓWIENIA]) AS R_MS |
| 211 | | FROM truck_expo.dbo.REKLAMA |
| 212 | | WHERE |
| 213 | | DATEPART(YEAR,[DATA ZAMÓWIENIA])=@ROK AND |
| 214 | | DATEPART(MONTH,[DATA ZAMÓWIENIA])=@MS AND |
| 215 | | [ZATWIERDZONO DO DRUKU]=1 |
| 216 | | GROUP BY [SYMBOL AKWIZYTORA] , DATEPART(YEAR,[DATA ZAMÓWIENIA]), DATEPART(MONTH,[DATA ZAMÓWIENIA]) |
| 217 | | ) P_R |
| 218 | | ON R_AGENT=AGENT AND ROK=R_ROK AND MS=R_MS |
| 219 | | FULL OUTER JOIN |
| 220 | | ( |
| 221 | | SELECT USERNAME, DATEPART(YEAR,DATA) AS K_ROK, DATEPART(MONTH,DATA) AS K_MS, COUNT(*) AS NKL FROM truck_expo.dbo.KLIENCI |
| 222 | | WHERE DATEPART(YEAR,DATA)=@ROK AND DATEPART(MONTH,DATA)=@MS |
| 223 | | GROUP BY USERNAME, DATEPART(YEAR,DATA), DATEPART(MONTH,DATA) |
| 224 | | ) AS P_NK |
| 225 | | ON USERNAME=AGENT AND ROK=K_ROK AND MS=K_MS |
| 226 | | --WYLICZENIE BUD¯ETU |
| 227 | | FULL OUTER JOIN |
| 228 | | ( |
| 229 | | SELECT [SYMBOL AKWIZYTORA] AS B_AGENT, SUM(NETTO) AS BUDZET, COUNT(*) AS IL_REKLAM, |
| 230 | | DATEPART(YEAR,[FAKTURA DATA WYSTAWIENIA]) AS B_ROK, |
| 231 | | DATEPART(MONTH,[FAKTURA DATA WYSTAWIENIA]) AS B_MS |
| 232 | | FROM truck_expo.dbo.REKLAMA |
| 233 | | WHERE |
| 234 | | DATEPART(YEAR,[FAKTURA DATA WYSTAWIENIA])=@ROK AND |
| 235 | | DATEPART(MONTH,[FAKTURA DATA WYSTAWIENIA])=@MS AND |
| 236 | | [ZATWIERDZONO DO DRUKU]=1 and |
| 237 | | [FAKTURA WYSTAWIONO]=1 |
| 238 | | GROUP BY [SYMBOL AKWIZYTORA] , DATEPART(YEAR,[FAKTURA DATA WYSTAWIENIA]), DATEPART(MONTH,[FAKTURA DATA WYSTAWIENIA]) |
| 239 | | ) P_B |
| 240 | | ON B_AGENT=AGENT AND ROK=B_ROK AND MS=B_MS |
| 241 | | -- WYLICZENIE DANYCH DO TESTU WIEDZY |
| 242 | | FULL OUTER JOIN |
| 243 | | ( |
| 244 | | SELECT user_name AS T_AGENT, DATEPART(YEAR,[DATA]) AS T_ROK, DATEPART(MONTH,[DATA]) AS T_MS, |
| 245 | | SUM(HANDEL) AS HANDEL, SUM(PRODUKT) AS PRODUKT, SUM(RYNEK) AS RYNEK, SUM(FIRMA) AS FIRMA |
| 246 | | FROM truck_expo.dbo.VIEW_ZESTAWIENIE_TEST_WIEDZY |
| 247 | | WHERE |
| 248 | | DATEPART(YEAR,[DATA])=@ROK AND |
| 249 | | DATEPART(MONTH,[DATA])=@MS |
| 250 | | GROUP BY user_name, DATEPART(YEAR,[DATA]) , DATEPART(MONTH,[DATA]) |
| 251 | | ) P_T |
| 252 | | ON T_AGENT=AGENT AND ROK=T_ROK AND MS=T_MS |
