Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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 |
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 |
peter i cant see the quater wise data
All data matches, except for the weeks. How do you calculate the week number? Not by using 'x'-numbers I guess
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?
Hi peter,
I m waiting for your reply