Pular para o conteúdo principal

Postagem em destaque

BlackTDN :: Como Criar Relatórios de Cotações com Dados Agrupados Usando SQL

img: DALL·E 2024-08-09 07.00.00 - A high-quality image showcasing a detailed SQL query being written in a code editor, such as VS Code, on a dark theme background. ... ## Como Criar Relatórios de Cotações com Dados Agrupados Usando SQL Quando trabalhamos com sistemas ERP e precisamos gerar relatórios de cotações que apresentam dados de múltiplos fornecedores, é comum encontrarmos a necessidade de pivotar registros que, originalmente, são apresentados de forma vertical. Isso é especialmente útil quando o objetivo é comparar preços ou condições de diferentes fornecedores para um mesmo produto em uma única linha do relatório. Neste artigo, vamos explorar uma abordagem para transformar registros verticais em colunas, facilitando a impressão de relatórios que consolidam informações de vários fornecedores em uma única linha. Vamos utilizar SQL com técnicas de pivotagem, e ao final, mostraremos como estender essa técnica para um número variável de fornecedores. ### Estrutura do Relatór

BlackTDN :: Exemplo de “Query” para popular uma tabela vazia no Protheus

   1: /*
   2:      Limpa a tabela para o Insert Total baseado na Classificacao
   3: */
   4:  
   5: TRUNCATE TABLE SZD030
   6:  
   7: /*
   8:      Popula a Tabela SZD de acordo com a Linha de Produto e Classificacao do Cliente
   9: */
  10: INSERT INTO
  11:      SZD030
  12:      (
  13:           ZD_FILIAL,
  14:           ZD_CLIENTE,
  15:           ZD_LOJA,
  16:           ZD_CODTMRG,
  17:           ZD_LINHAP,
  18:           D_E_L_E_T_,
  19:           R_E_C_N_O_,
  20:           R_E_C_D_E_L_
  21:      )
  22: SELECT 
  23:      SZD.ZD_FILIAL                                     AS ZD_FILIAL,
  24:      SZD.ZD_CLIENTE                                    AS ZD_CLIENTE,
  25:      SZD.ZD_LOJA                                       AS ZD_LOJA,
  26:      SZD.ZD_CODTMRG                                    AS ZD_CODTMRG,
  27:      ZD_LINHAP                                         AS ZD_LINHAP,
  28:      ' '                                               AS D_E_L_E_T_,
  29:      ROW_NUMBER() OVER ( ORDER BY SZD.R_E_C_N_O_ )     AS R_E_C_N_O_,
  30:      0                                                 AS R_E_C_D_E_L_
  31: FROM
  32: (
  33:      --Linha de Produto 01
  34:      SELECT 
  35:           SA1030.A1_FILIAL                             AS ZD_FILIAL,
  36:           SA1030.A1_COD                                AS ZD_CLIENTE,
  37:           SA1030.A1_LOJA                               AS ZD_LOJA,
  38:           CODTMRG.ZD_CODTMRG                           AS ZD_CODTMRG,
  39:           '01'                                         AS ZD_LINHAP,
  40:           0                                            AS R_E_C_N_O_
  41:      FROM
  42:           SA1030,
  43:           (
  44:                SELECT
  45:                     SE1.E1_FILIAL,
  46:                     SE1.E1_CLIENTE,
  47:                     SE1.E1_LOJA,
  48:                     SE1.E1_VALOR,
  49:                     (
  50:                          CASE 
  51:                               WHEN 
  52:                                    SE1.E1_VALOR < 15000 
  53:                               THEN 
  54:                                    '001'
  55:                               ELSE 
  56:                                    CASE
  57:                                         WHEN
  58:                                              SE1.E1_VALOR BETWEEN 15000 AND 24999.99
  59:                                         THEN
  60:                                              '002'
  61:                                         ELSE 
  62:                                              CASE
  63:                                                   WHEN
  64:                                                        SE1.E1_VALOR >= 25000
  65:                                                   THEN '003' 
  66:                                              END
  67:                                    END
  68:                          END
  69:                     ) AS ZD_CODTMRG
  70:                FROM
  71:                     (
  72:                          SELECT 
  73:                               SE1_S.E1_FILIAL,
  74:                               SE1_S.E1_CLIENTE,
  75:                               SE1_S.E1_LOJA,
  76:                               SUM(SE1_S.E1_VALOR) E1_VALOR 
  77:                          FROM 
  78:                               SE1030 SE1_S
  79:                          WHERE
  80:                               SE1_S.D_E_L_E_T_  <> '*'
  81:                          AND
  82:                               SE1_S.E1_FILIAL   =  '01'
  83:                          AND 
  84:                               SE1_S.E1_EMISSAO BETWEEN '20110101' AND '20111231'
  85:                          GROUP BY 
  86:                               SE1_S.E1_FILIAL,
  87:                               SE1_S.E1_CLIENTE,
  88:                               SE1_S.E1_LOJA     
  89:                     ) AS SE1
  90:                ) AS CODTMRG
  91:           WHERE 
  92:                SA1030.D_E_L_E_T_    <> '*'
  93:           AND
  94:                SA1030.A1_FILIAL     =  ' '
  95:           AND 
  96:                SA1030.A1_MSBLQL     <> '1'
  97:           AND
  98:                CODTMRG.E1_FILIAL    =  '01'
  99:           AND     
 100:                CODTMRG.E1_CLIENTE   = SA1030.A1_COD
 101:           AND
 102:                CODTMRG.E1_LOJA      = SA1030.A1_LOJA
 103:           AND
 104:           NOT EXISTS 
 105:           (
 106:                SELECT
 107:                     1
 108:                FROM
 109:                     SZD030 
 110:                WHERE
 111:                     SZD030.ZD_FILIAL     = SA1030.A1_FILIAL
 112:                AND
 113:                     SZD030.ZD_CLIENTE    = SA1030.A1_COD
 114:                AND
 115:                     SZD030.ZD_LOJA       = SA1030.A1_LOJA
 116:                AND
 117:                     SZD030.ZD_LINHAP     = '01'
 118:           )
 119:      UNION ALL
 120:           --Linha de Produto 02
 121:           SELECT 
 122:                SA1030.A1_FILIAL     AS ZD_FILIAL,
 123:                SA1030.A1_COD        AS ZD_CLIENTE,
 124:                SA1030.A1_LOJA       AS ZD_LOJA,
 125:                CODTMRG.ZD_CODTMRG   AS ZD_CODTMRG,
 126:                '02'                 AS ZD_LINHAP,
 127:                0                    AS R_E_C_N_O_
 128:           FROM
 129:                SA1030,
 130:                (
 131:                     SELECT
 132:                          (
 133:                               CASE 
 134:                                    WHEN 
 135:                                         SE1.E1_VALOR < 15000 
 136:                                    THEN 
 137:                                         '001'
 138:                                    ELSE 
 139:                                         CASE
 140:                                              WHEN
 141:                                                   SE1.E1_VALOR BETWEEN 15000 AND 24999.99
 142:                                              THEN
 143:                                                   '002'
 144:                                              ELSE 
 145:                                                   CASE
 146:                                                        WHEN
 147:                                                             SE1.E1_VALOR >= 25000
 148:                                                        THEN '005' 
 149:                                                   END
 150:                                         END
 151:                               END
 152:                          ) AS ZD_CODTMRG,
 153:                          SE1.E1_CLIENTE,
 154:                          SE1.E1_LOJA,
 155:                          SE1.E1_VALOR
 156:                     FROM
 157:                          (
 158:                               SELECT 
 159:                                    SE1_S.E1_CLIENTE,
 160:                                    SE1_S.E1_LOJA,
 161:                                    SUM(SE1_S.E1_VALOR) E1_VALOR 
 162:                               FROM 
 163:                                    SE1030 SE1_S
 164:                               WHERE
 165:                                    SE1_S.D_E_L_E_T_     <> '*'
 166:                               AND 
 167:                                    SE1_S.E1_EMISSAO BETWEEN '20110101' AND '20111231'
 168:                               GROUP BY 
 169:                                    SE1_S.E1_CLIENTE,
 170:                                    SE1_S.E1_LOJA     
 171:                          ) AS SE1
 172:                     ) AS CODTMRG
 173:                WHERE 
 174:                     SA1030.D_E_L_E_T_    <> '*'
 175:                AND 
 176:                     SA1030.A1_MSBLQL     <> '1'
 177:                AND
 178:                     CODTMRG.E1_CLIENTE   = SA1030.A1_COD
 179:                AND
 180:                     CODTMRG.E1_LOJA      = SA1030.A1_LOJA
 181:                AND
 182:                NOT EXISTS 
 183:                (
 184:                     SELECT
 185:                          1
 186:                     FROM
 187:                          SZD030 
 188:                     WHERE
 189:                          SZD030.ZD_FILIAL    = SA1030.A1_FILIAL
 190:                     AND
 191:                          SZD030.ZD_CLIENTE   = SA1030.A1_COD
 192:                     AND
 193:                          SZD030.ZD_LOJA      = SA1030.A1_LOJA
 194:                     AND
 195:                          SZD030.ZD_LINHAP    = '02'
 196:                )
 197:      UNION ALL
 198:           --Linha de Produto 03
 199:           SELECT 
 200:                SA1030.A1_FILIAL     AS ZD_FILIAL,
 201:                SA1030.A1_COD        AS ZD_CLIENTE,
 202:                SA1030.A1_LOJA       AS ZD_LOJA,
 203:                CODTMRG.ZD_CODTMRG   AS ZD_CODTMRG,
 204:                '03'                 AS ZD_LINHAP,
 205:                0                    AS R_E_C_N_O_
 206:           FROM
 207:                SA1030,
 208:                (
 209:                     SELECT
 210:                          (
 211:                               CASE 
 212:                                    WHEN 
 213:                                         SE1.E1_VALOR < 15000 
 214:                                    THEN 
 215:                                         '001'
 216:                                    ELSE 
 217:                                         CASE
 218:                                              WHEN
 219:                                                   SE1.E1_VALOR BETWEEN 15000 AND 24999.99
 220:                                              THEN
 221:                                                   '002'
 222:                                              ELSE 
 223:                                                   CASE
 224:                                                        WHEN
 225:                                                             SE1.E1_VALOR >= 25000
 226:                                                        THEN '003' 
 227:                                                   END
 228:                                         END
 229:                               END
 230:                          ) AS ZD_CODTMRG,
 231:                          SE1.E1_CLIENTE,
 232:                          SE1.E1_LOJA,
 233:                          SE1.E1_VALOR
 234:                     FROM
 235:                          (
 236:                               SELECT 
 237:                                    SE1_S.E1_CLIENTE,
 238:                                    SE1_S.E1_LOJA,
 239:                                    SUM(SE1_S.E1_VALOR) E1_VALOR 
 240:                               FROM 
 241:                                    SE1030 SE1_S
 242:                               WHERE
 243:                                    SE1_S.D_E_L_E_T_ <> '*'
 244:                               AND 
 245:                                    SE1_S.E1_EMISSAO BETWEEN '20110101' AND '20111231'
 246:                               GROUP BY 
 247:                                    SE1_S.E1_CLIENTE,
 248:                                    SE1_S.E1_LOJA     
 249:                          ) AS SE1
 250:                     ) AS CODTMRG
 251:                WHERE 
 252:                     SA1030.D_E_L_E_T_    <> '*'
 253:                AND 
 254:                     SA1030.A1_MSBLQL     <> '1'
 255:                AND
 256:                     CODTMRG.E1_CLIENTE   = SA1030.A1_COD
 257:                AND
 258:                     CODTMRG.E1_LOJA      = SA1030.A1_LOJA
 259:                AND
 260:                NOT EXISTS 
 261:                (
 262:                     SELECT
 263:                          1
 264:                     FROM
 265:                          SZD030 
 266:                     WHERE
 267:                          SZD030.ZD_FILIAL     = SA1030.A1_FILIAL
 268:                     AND
 269:                          SZD030.ZD_CLIENTE    = SA1030.A1_COD
 270:                     AND
 271:                          SZD030.ZD_LOJA       = SA1030.A1_LOJA
 272:                     AND
 273:                          SZD030.ZD_LINHAP     = '03'
 274:                )
 275: ) AS SZD
 276:  
 277: /*
 278:      Totaliza os Clientes Processados
 279: */
 280:  
 281: SELECT 
 282:      ( COUNT(1) / 3 ) AS TOTAL_CLIENTES 
 283: FROM 
 284:      ( SELECT * FROM SZD030 ) SZD
 285:  
 286: /*
 287:      Lista os Clientes Processados Classificados por Filial,Codigo, Loja, Linha
 288: */
 289: SELECT
 290:      SZD.ZD_FILIAL,
 291:      SZD.ZD_CLIENTE,
 292:      SZD.ZD_LOJA,
 293:      SZD.ZD_LINHAP,
 294:      SZD.E1_VALOR, 
 295:      SZD030.ZD_CODTMRG
 296: FROM
 297:      SZD030,
 298:      (
 299:           SELECT 
 300:                SZD.ZD_FILIAL,
 301:                SZD.ZD_CLIENTE,
 302:                SZD.ZD_LOJA,
 303:                SZD.ZD_LINHAP,
 304:                SUM(SE1.E1_VALOR) E1_VALOR 
 305:           FROM 
 306:                SZD030 SZD,
 307:                SE1030 SE1
 308:           WHERE
 309:                SZD.D_E_L_E_T_    <> '*'
 310:           AND
 311:                SZD.ZD_FILIAL     =  '  '     
 312:           AND
 313:                SE1.D_E_L_E_T_    <> '*'
 314:           AND
 315:                SE1.E1_FILIAL     =  '01'          
 316:           AND 
 317:                SZD.ZD_CLIENTE    = SE1.E1_CLIENTE
 318:           AND 
 319:                SZD.ZD_LOJA       = SE1.E1_LOJA
 320:           AND
 321:                SZD.ZD_LINHAP     = '01'
 322:           AND 
 323:                SE1.E1_EMISSAO BETWEEN '20110101' AND '20111231'
 324:           GROUP BY 
 325:                SZD.ZD_FILIAL,
 326:                SZD.ZD_CLIENTE,
 327:                SZD.ZD_LOJA,
 328:                SZD.ZD_LINHAP
 329:           UNION ALL
 330:           SELECT 
 331:                SZD.ZD_FILIAL,
 332:                SZD.ZD_CLIENTE,
 333:                SZD.ZD_LOJA,
 334:                SZD.ZD_LINHAP,
 335:                SUM(SE1.E1_VALOR) E1_VALOR 
 336:           FROM 
 337:                SZD030 SZD,
 338:                SE1030 SE1
 339:           WHERE
 340:                SZD.D_E_L_E_T_    <> '*'
 341:           AND
 342:                SZD.ZD_FILIAL     =  '  '
 343:           AND
 344:                SE1.D_E_L_E_T_    <> '*'
 345:           AND
 346:                SE1.E1_FILIAL     =  '01'
 347:           AND 
 348:                SZD.ZD_CLIENTE = SE1.E1_CLIENTE
 349:           AND 
 350:                SZD.ZD_LOJA    = SE1.E1_LOJA
 351:           AND
 352:                SZD.ZD_LINHAP   = '02'
 353:           AND 
 354:                SE1.E1_EMISSAO BETWEEN '20110101' AND '20111231'
 355:           GROUP BY 
 356:                SZD.ZD_FILIAL,
 357:                SZD.ZD_CLIENTE,
 358:                SZD.ZD_LOJA,
 359:                SZD.ZD_LINHAP
 360:           UNION ALL
 361:           SELECT 
 362:                SZD.ZD_FILIAL,
 363:                SZD.ZD_CLIENTE,
 364:                SZD.ZD_LOJA,
 365:                SZD.ZD_LINHAP,
 366:                SUM(SE1.E1_VALOR) E1_VALOR 
 367:           FROM 
 368:                SZD030 SZD,
 369:                SE1030 SE1
 370:           WHERE
 371:                SZD.D_E_L_E_T_    <> '*'
 372:           AND
 373:                SZD.ZD_FILIAL     =  '  '     
 374:           AND
 375:                SE1.D_E_L_E_T_    <> '*'
 376:           AND
 377:                SE1.E1_FILIAL     =  '01'          
 378:           AND 
 379:                SZD.ZD_CLIENTE = SE1.E1_CLIENTE
 380:           AND 
 381:                SZD.ZD_LOJA    = SE1.E1_LOJA
 382:           AND
 383:                SZD.ZD_LINHAP  = '03'
 384:           AND 
 385:                SE1.E1_EMISSAO BETWEEN '20110101' AND '20111231'
 386:           GROUP BY 
 387:                SZD.ZD_FILIAL,
 388:                SZD.ZD_CLIENTE,
 389:                SZD.ZD_LOJA,
 390:                SZD.ZD_LINHAP
 391:      ) SZD
 392: WHERE
 393:      SZD030.D_E_L_E_T_    <> '*'
 394: AND
 395:      SZD030.ZD_FILIAL     = SZD.ZD_FILIAL
 396: AND
 397:      SZD030.ZD_CLIENTE    = SZD.ZD_CLIENTE
 398: AND
 399:      SZD030.ZD_LOJA       = SZD.ZD_LOJA
 400: AND
 401:      SZD030.ZD_LINHAP     = SZD.ZD_LINHAP
 402: ORDER BY 
 403:      SZD.ZD_FILIAL,
 404:      SZD.ZD_CLIENTE,
 405:      SZD.ZD_LOJA,
 406:      SZD.ZD_LINHAP

[]s
иαldσ dj

Comentários

Postagens mais visitadas