Table links

[#1]

I have the following in my load

Table1

SQL SELECT

InvoiceNo

CustNo

DueDate

CASE when [DueDate]is NOT NULL then GETDATE() end AS Today,

CASE WHEN DATEDIFF(DAY,DueDate,GETDATE())>183 THEN

DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY,-183,GETDATE())) + 1, 0) - 1

ELSE

DATEADD(MONTH, DATEDIFF(MONTH, 0, DueDate) + 1, 0) - 1

END AS EndOfMonth,

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) - 1 AS EndOfCurrentMonth

Then I have a resident table based on the above:

Status:

LOAD

IF ([EndOfMonth]<[Today],'Overdue',

IF ([EndOfMonth]<=[EndOfCurrentMonth], 'FALLING DUE BY END OF MONTH','Not Yet Due'))AS Status

Resident table1;

How do I join the two to get the status against each line ?

Thanks!

[sunny_talwar · Accepted solution]

Try Preceding Load

Table1:

LOAD *,

           If([EndOfMonth]<[Today],'Overdue',

           If([EndOfMonth]<=[EndOfCurrentMonth], 'FALLING DUE BY END OF MONTH','Not Yet Due')) as Status;

SQL SELECT

InvoiceNo

CustNo

DueDate

CASE when [DueDate]is NOT NULL then GETDATE() end as Today,

CASE WHEN DATEDIFF(DAY,DueDate,GETDATE())>183 THEN

DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY,-183,GETDATE())) + 1, 0) - 1

ELSE

DATEADD(MONTH, DATEDIFF(MONTH, 0, DueDate) + 1, 0) - 1

END AS EndOfMonth,

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) - 1 AS EndOfCurrentMonth

UPDATE: Added the * in the preceding load as pointed out by Colin

[Colin-Albert]

I think you missed the * from the preceeding load Sunny.

Table1:

LOAD If([EndOfMonth]<[Today],'Overdue',

           If([EndOfMonth]<=[EndOfCurrentMonth], 'FALLING DUE BY END OF MONTH','Not Yet Due')) as Status,

           *;

SQL SELECT

InvoiceNo

CustNo

DueDate

CASE when [DueDate]is NOT NULL then GETDATE() end as Today,

CASE WHEN DATEDIFF(DAY,DueDate,GETDATE())>183 THEN

DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY,-183,GETDATE())) + 1, 0) - 1

ELSE

DATEADD(MONTH, DATEDIFF(MONTH, 0, DueDate) + 1, 0) - 1

END AS EndOfMonth,

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) - 1 AS EndOfCurrentMonth

[sunny_talwar]

Yes, I did

[#5]

Thank you for your help today.

It has been invaluable.

[sunny_talwar]

Not a problem. We are glad that we are able to help you out