how to build a variable for current datamonth

[#1]

i need to pull the data from database for the current data month - 2 so for this i need to automate the process

so in the script i need to give month= 8/01/2013  this should change every month

can any one help me in building the variable

thanks in advance

[Clever_Anjos · Accepted solution]

LET vData = date(monthstart(addmonths(Today(),-2)));

[#3]

thank you very much Anjos

[Clever_Anjos]

you´re welcome

[javier_florian]

Hi Apache77,

If you are loading data, set variable according db engage:

1. flat file (xls, csv,...) -> Month(Date(Date_Field_Database, 'MM/DD/YYYY')) <= Month(today())-2;

2. sql server -> Convert(Datetime, Date_Field_Database) >= Convert(Datetime, Dateadd(month,-2,getdate());

But, if you have an object, you can refer to Clever's answer.

Regards,

JFlorian

[#6]

when we do this my qvd will have only current month data right

if i reload it in next month then i will have the data of September but i will loose data of august

now if i dont want to loose the data which i loaded last month what will be the condition

[Clever_Anjos]

Check your manual for "Incremental Load", there´s what you need

[#8]

is there a way you can give me small script

i went through it but unable to make it

[Clever_Anjos]

LET Ontem = date(Today()-1,'YYYY-MM-DD');
Contratacao:
SQL SELECT 
  "COD_ANALISTA_CONTRATADO" as "Contratacao.COD_ANA_CONTR",
    "COD_CONTRATACAO" as "Contratacao.COD_CONTRATACAO",
    "COD_DEMANDA" as "Contratacao.COD_DEMANDA",
    "COD_EMPRESA_CONTRATADA" as "Contratacao.COD_EMP_CONTR",
    "COD_USR_INCLUSAO" as "Contratacao.COD_USR_INCLUSAO",
    "DAT_FIM_DESENVOLVIMENTO" as "Contratacao.DAT_FIM_DESENV",
    "DAT_INCLUSAO" as "Contratacao.DAT_INCLUSAO",
    "DAT_INICIO_DESENVOLVIMENTO" as "Contratacao.DAT_INI_DESENV",
    "DES_CONTRATACAO" as "Contratacao.DES_CONTRATACAO",
    "FLG_BANCO_HORAS" as "Contratacao.FLG_BANCO_HORAS",
    "TOTAL_HORAS_CONTRATADAS" as "Contratacao.TOTAL_H_CONTR",
    "VALOR_HOMOLOGACAO" as "Contratacao.VALOR_HOMO",
    "VALOR_HORA_CONTRATADO" as "Contratacao.VALOR_H_CONTR",
    "VALOR_IMPLANTACAO" as "Contratacao.VALOR_IMPLANT",
    "VALOR_INFRA_ESTRUTURA" as "Contratacao.VALOR_INFRA",
    "VALOR_OUTROS" as "Contratacao.VALOR_OUTROS",
    "VALOR_TOTAL_CONTRATADO" as "Contratacao.VALOR_TOT_CONTR",
    "VALOR_TREINAMENTO" as "Contratacao.VALOR_TREINAMENTO"
FROM Contratacao
WHERE
  DAT_INCLUSAO >= '$(Ontem)';


Concatenate


LOAD Contratacao.COD_ANA_CONTR, 
     Contratacao.COD_CONTRATACAO, 
     Contratacao.COD_DEMANDA, 
     Contratacao.COD_EMP_CONTR, 
     Contratacao.COD_USR_INCLUSAO, 
     Contratacao.DAT_FIM_DESENV, 
     Contratacao.DAT_INCLUSAO, 
     Contratacao.DAT_INI_DESENV, 
     Contratacao.DES_CONTRATACAO, 
     Contratacao.FLG_BANCO_HORAS, 
     Contratacao.TOTAL_H_CONTR, 
     Contratacao.VALOR_HOMO, 
     Contratacao.VALOR_H_CONTR, 
     Contratacao.VALOR_IMPLANT, 
     Contratacao.VALOR_INFRA, 
     Contratacao.VALOR_OUTROS, 
     Contratacao.VALOR_TOT_CONTR, 
     Contratacao.VALOR_TREINAMENTO
FROM $(Path)\Contratacao.qvd (qvd)
WHERE NOT Exists(Contratacao.COD_CONTRATACAO);


STORE Contratacao INTO $(Path)\Contratacao.qvd;


DROP TABLE Contratacao;

[#10]

can you please help me is this for incremental load