Group by

Anonymous — Tue, 14 Feb 2017 13:24:45 GMT

Bom dia

preciso agrupar essa tabela por mês podem me ajudar ?

FOR Each vPeriodoBaseCargaIncremental in $(vAnoMesCarregar)

LET vQtdPeriodosCarregar = Round((MonthStart(Today()-1) - Date#(vPeriodoBaseCargaIncremental, 'YYYYMM')) / 30.5) +1;

FOR a=1 to vQtdPeriodosCarregar

IF a > 1 then

LET vPeriodoBaseCargaIncremental = Date(AddMonths(Date#(vPeriodoBaseCargaIncremental, 'YYYYMM'), 1), 'YYYYMM');

ENDIF

Concatenate(Vendas)

CMV:

// first 100000

LOAD

Num#(COD_CLIENTE_SAP,'#########',0) as COD_CLIENTE_SAP,

COD_MATERIAL&'-'&DES_CD as [Key Cod Mat CD],

//COD_MATERIAL,

If(WILDMATCH(COD_TIPO_OFERTA, '*OL*'), 'OL',

If(WILDMATCH(COD_TIPO_OFERTA, '*SC*'), 'CF', 'OFL')) as TIPOVENDA,

date(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'),'DD/MM/YYYY') as Dta,

date(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'),'YYYY-MM') as AnoMês,

Year(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as Ano,

Month(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as Month,

MonthName(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as MonthName,

day(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as DAY,

DES_CD,

VLR_CMV

FROM

[..\5.QVDs\BW_CMV_$(vPeriodoBaseCargaIncremental).QVD](qvd);

Obrigado.

Re: Group by

nicolett_yuri — Tue, 14 Feb 2017 18:51:16 GMT

Guilherme, faça uma nova leitura com resident e utilize o group by conforme o desejado:

Tabela_Tmp:

LOAD

Num#(COD_CLIENTE_SAP,'#########',0) as COD_CLIENTE_SAP,

COD_MATERIAL&'-'&DES_CD as [Key Cod Mat CD],

//COD_MATERIAL,

If(WILDMATCH(COD_TIPO_OFERTA, '*OL*'), 'OL',

If(WILDMATCH(COD_TIPO_OFERTA, '*SC*'), 'CF', 'OFL')) as TIPOVENDA,

date(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'),'DD/MM/YYYY') as Dta,

date(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'),'YYYY-MM') as AnoMês,

Year(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as Ano,

Month(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as Month,

MonthName(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as MonthName,

day(date#(DTA_CALENDARIO_CMV,'YYYYMMDD'))as DAY,

DES_CD,

VLR_CMV

FROM

[..\5.QVDs\BW_CMV_$(vPeriodoBaseCargaIncremental).QVD](qvd);

Tabela:

LOAD

COD_CLIENTE_SAP,

[Key Cod Mat CD],

TIPOVENDA,

AnoMês,

Ano,

Month,

MonthName,

DES_CD,

SUM(VLR_CMV) as VLR_CMV

RESIDENT Tabela_Tmp

GROUP BY

COD_CLIENTE_SAP,

[Key Cod Mat CD],

TIPOVENDA,

AnoMês,

Ano,

Month,

MonthName,

DES_CD;

DROP TABLE Tabela_Tmp;

topic Re: Group by in Brasil

Group by

Re: Group by