add row conditionally on join

[cuniberto]

Hi everyone,

i have a table with warehouse movement.

I need to add the begin and end period value if not present.

begin date 01-10-2016

end date 30-09-2017

movement table:

Code               date               movement quantity

COD1               03-10-2016     -3

COD1               03-06-2017     -7

COD1               03-07-2017     +20

COD1               30-09-2017     -3

COD2               03-10-2016     -3

COD2               10-05-2017     -7

COD2               20-06-2017     +20

COD3               30-09-2016     -3

COD3               25-03-2017     -7

COD3               11-04-2017     +20

COD3               30-09-2017     -3

in the movement table i have to add for the COD1 the date of the beginning period and not the date of the ending period (in that date there is a movement), for the COD2 both date, for COD3 none.

[Anil_Babu_Samineni]

Are you going to have this?

movement table:

Code              date              movement quantity

COD1              01-10-2016    0

......

COD1              03-10-2016    -3

COD1              03-06-2017    -7

COD1              03-07-2017    +20

COD1              30-09-2017    -3

COD2              01-10-2016    0

.....

COD2              03-10-2016    -3

COD2              10-05-2017    -7

COD2              20-06-2017    +20

COD3              30-09-2016    -3

COD3              25-03-2017    -7

COD3              11-04-2017    +20

COD3              30-09-2017    -3

[sunny_talwar]

Why does COD3 not need the begin date?

[cuniberto]

yes, i need the final result is like you suggest

Code              date              movement quantity

COD1              01-10-2016    0

......

COD1              03-10-2016    -3

COD1              03-06-2017    -7

COD1              03-07-2017    +20

COD1              30-09-2017    -3

COD2              01-10-2016    0

.....

COD2              03-10-2016    -3

COD2              10-05-2017    -7

COD2              20-06-2017    +20

....

COD2              30-09-2017    0

COD3              01-10-2016    -3

COD3              25-03-2017    -7

COD3              11-04-2017    +20

COD3              30-09-2017    -3

[cuniberto]

sorry, there was a mistake...

the final table i need to hawe is:

Code              date              movement quantity

COD1              01-10-2016    0

......

COD1              03-10-2016    -3

COD1              03-06-2017    -7

COD1              03-07-2017    +20

COD1              30-09-2017    -3

COD2              01-10-2016    0

.....

COD2              03-10-2016    -3

COD2              10-05-2017    -7

COD2              20-06-2017    +20

....

COD2              30-09-2017    0

COD3              01-10-2016    -3

COD3              25-03-2017    -7

COD3              11-04-2017    +20

COD3              30-09-2017    -3

[sunny_talwar · Accepted solution]

I would do something like this

Table:

LOAD Code,

     date,

     [movement quantity],

     AutoNumber(Code&Num(date)) as Key

FROM ....;

TempTable:

LOAD * INLINE [

date

01-10-2016

30-09-2017

];

Left Join (TempTable)

LOAD DISTINCT Code

Resident Table;

Concatenate (Table)

LOAD Code,

     date,

     0 as [movement quantity]

Resident TempTable

Where not Exists(Key, AutoNumber(Code&Num(date)))

DROP Table TempTable;

[cuniberto]

thanks a lot!