pull max(Date) from each month

[jyothish8807]

Hello All,

I need a small help from you regarding a logic.

I am trying to write a where clause to pull the max date for each month in a table.

Suppose i have 10 dates in month jan and 12 dates for month feb, so i want to pull only the max date for jan and feb using a where clause.

i have only one date field and rest are data fields.

Date	Daily
21/9/2015	1
5/9/2015	2
12/9/2015	3
1/10/2015	4
5/10/2015	5

So in out put i want 21/9/2015 and 5/10/2015 to be pulled using a where clause.

Thanks

KC

[Kushal_Chawda · Accepted solution]

try like this

//Create MonthYear from Date & calculate Max date by MonthYear

Data:

LOAD *,

monthname(Date) as MonthYear;

LOAD date(MakeDate(2015)+IterNo()-1,'DD-MM-YYYY') as Date,

ceil(Rand()*100+1000) as Amount

AutoGenerate(1)

While MakeDate(2015)+IterNo()-1<=Today();

Inner Join

LOAD MonthYear,

date(max(Date),'DD-MM-YYYY') as Date

Resident Data

Group by MonthYear;

[Kushal_Chawda]

Want to load max date while loading data from source?

[sunny_talwar]

May be this:

Table:

LOAD Date,

  Daily,

  MonthName(Date) as MonthYear;

LOAD * Inline [

Date, Daily

21/9/2015, 1

5/9/2015, 2

12/9/2015, 3

1/10/2015, 4

5/10/2015, 5

];

Join(Table)

LOAD MonthYear,

  Max(Date) as Date,

  1 as Flag

Resident Table

Group By MonthYear;

ExtractedData:

LOAD Date as Date1

Resident Table

Where Flag = 1;

Output:

Data Structure (can be modified as per your need)

[MK_QSL]

Temp:
Load 
  Date(Date#(Date,'D/M/YYYY')) as Date,
  MonthStart(Date#(Date,'D/M/YYYY')) as MonthYear
Inline
[
  Date
  21/9/2015
  5/9/2015
  12/9/2015
  1/10/2015
  5/10/2015
];


Left Join (Temp)
oad 
  Date(Max(Date)) as MaxDate, 
  MonthYear
Resident Temp
Group By MonthYear;


Final:
Load Date Resident Temp
Where Date = MaxDate;


Drop Table Temp;

[jyothish8807]

Thanks a lot khus, it worked.

Regards

KC

[Anonymous]

Hi Kush,

Technically you need max date while grouping on month.

try below sql :

SELECT MONTH, MAX(DATE)

FROM TABLE

GROUP BY MONTH

Above query will give month wise max date available in the table.

Ex  :

Month	maxDate
1	1/31/2015 0:00
2	2/28/2015 0:00
3	3/31/2015 0:00

Thanks

Sattya

[jyothish8807]

Hi Sunny,

Is there any way to achieve this without doing any join ?

Currently my load time has increased due to this logic, i am dealing with huge amount of data and i have implemented this logic in  almost all tables.

Br,

KC

[jyothish8807]

Hi Kushal,

Is there any way to achieve this without doing any join ?

Currently my load time has increased due to this logic, i am dealing with huge amount of data and i have implemented this logic in  almost all tables.

Br,

KC

[sunny_talwar]

I think the problem might be the group by rather than the join itself (unless it is a many to many join)... may try to optimize your group by using the approach suggested here

Optimize Group By Performance