date granularity in script and storing as table

[manoranjan_d]

Key	Created date	amount
Key1	10-Oct-16	100
Key1	11-Oct-16	2000
Key1	12-Oct-16	300
Key1	13-Oct-16	15456
Key1	14-Oct-16	1564
key2	15-Oct-16	45455
key2	16-Oct-16	100
key2	10-Nov-16	2000
key2	11-Nov-16	300
key2	12-Nov-16	15456
key2	13-Nov-16	1564
key3	14-Nov-16	45455
key3	15-Nov-16	100
key3	16-Nov-16	2000
key3	17-Nov-16	300
key3	18-Nov-16	15456
key3	19-Nov-16	1564
Key1	10-Oct-15	100
Key1	11-Oct-15	2000
Key1	12-Oct-15	300
Key1	13-Oct-15	15456
Key1	14-Oct-15	1564
key2	15-Oct-15	45455
key2	16-Oct-15	100
key2	10-Nov-15	2000
key2	11-Nov-15	300
key2	12-Nov-15	15456
key2	13-Nov-15	1564
key3	14-Nov-15	45455
key3	15-Nov-15	100
key3	16-Nov-15	2000
key3	17-Nov-15	300
key3	18-Nov-15	15456
key3	19-Nov-15	1564

hi above is my data,

I need the distinct key with max date for the week , for the month , for the quarter , for the year.

These (week, month, quater and year)  has to be retrieved  form the above table and these has to stored  as separate table as week, year, quarter, month.

can you help me how these can achieved in the script level

[Peter_Cammaert]

If you want three different tables, replace the JOIN of the second GROUP BY LOAD with a new table name and give the Month and Year fields unique names.

Best,

Peter

[manoranjan_d]

peter u r almost right there but quater is missing, let me check this,

below s the output required.

Key	Created date	amount	Weeknumber
Key1	14-Oct-16	1564	xx week
Key1	14-Oct-15	1564	xx week
key2	15-Oct-16	45455	xx week
key2	16-Oct-16	100	xx week
key2	12-Nov-16	15456	xx week
key2	13-Nov-16	1564	xx week
key2	16-Oct-15	100	xx week
key2	13-Nov-15	1564	xx week
key3	19-Nov-16	1564	xx week
key3	14-Nov-15	45455	xx week
key3	19-Nov-15	1564	xx week

Quarter output
Key	Created date	amount
Key1	14-Oct-16	1564
Key1	14-Oct-15	1564
key2	13-Nov-16	1564
key2	13-Nov-15	1564
key3	19-Nov-16	1564
key3	19-Nov-15	1564

Year output
Key	Created date	amount
Key1	14-Oct-16	1564
Key1	14-Oct-15	1564
key2	13-Nov-16	1564
key2	13-Nov-15	1564
key3	19-Nov-16	1564
key3	19-Nov-15	1564

[manoranjan_d]

month output
Key	Created date	amount
Key1	14-Oct-16	1564
Key1	14-Oct-15	1564
key2	16-Oct-16	100
key2	13-Nov-16	1564
key2	16-Oct-15	100
key2	13-Nov-15	1564
key3	19-Nov-16	1564
key3	19-Nov-15	1564

[manoranjan_d]

peter i cant see the quater wise data

[Peter_Cammaert]

All data matches, except for the weeks. How do you calculate the week number? Not by using 'x'-numbers I guess

[manoranjan_d]

I thought the week start from Sunday, so it got mismatch, i agree that week number is calculated from monday,

I cant take manually calculation for the weeknumber, so to show the output i just shown it as xxx 

can I know quaterly wise max date data?

[manoranjan_d]

Hi peter,

I m waiting for your reply