Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey all ,
If I have a table with a daily Date Example:
the table contain ID Balance and Date and the date has form as (01/01/2016)
I need to extract for previous month the Balance as at end of month and for the maximum for current month from the script since the table from database is too large and I want to extract to table with these condition .
How can I write the "Where" Condition ?
Hope you can help
Thanks,
Would you be able to share few rows of data and explain what you want to extract from it?
Hey Sunny , Thank you for Your Response. unfortunately I can't share my data here but here is an example and hope this will be clear:
let's say each ID has a balance evry day but I need to extract from my database to table that contain the balance only for end of each month and for current month the maximum date (not the whole month) below the table and my output would be red cells hope you can help thank you so much :
ID | Date | Balance |
1 | 01/01/2016 | 1000 |
1 | 12/01/2016 | 12000 |
1 | 26/01/2016 | 26000 |
1 | 27/01/2016 | 27000 |
1 | 28/01/2016 | 28000 |
1 | 29/01/2016 | 29000 |
1 | 30/01/2016 | 30000 |
1 | 31/01/2016 | 31000 |
1 | 01/02/2016 | 32000 |
1 | 16/02/2016 | 47000 |
1 | 17/02/2016 | 48000 |
1 | 18/02/2016 | 49000 |
1 | 19/02/2016 | 50000 |
1 | 20/02/2016 | 51000 |
1 | 21/02/2016 | 52000 |
1 | 22/02/2016 | 53000 |
1 | 23/02/2016 | 54000 |
1 | 24/02/2016 | 55000 |
1 | 25/02/2016 | 56000 |
1 | 26/02/2016 | 57000 |
1 | 27/02/2016 | 58000 |
1 | 28/02/2016 | 59000 |
1 | 29/02/2016 | 60000 |
1 | 01/03/2016 | 61000 |
1 | 02/03/2016 | 62000 |
1 | 03/03/2016 | 63000 |
1 | 04/03/2016 | 64000 |
1 | 05/03/2016 | 65000 |
1 | 06/03/2016 | 66000 |
1 | 07/03/2016 | 67000 |
1 | 08/03/2016 | 68000 |
1 | 09/03/2016 | 69000 |
1 | 10/03/2016 | 70000 |
1 | 11/03/2016 | 71000 |
1 | 12/03/2016 | 72000 |
1 | 13/03/2016 | 73000 |
1 | 14/03/2016 | 74000 |
1 | 15/03/2016 | 75000 |
1 | 16/03/2016 | 76000 |
May be something like this:
Table:
LOAD ID,
Date,
Date(MonthStart(Date), 'MMM-YYYY') as MonthYear,
Balance
FROM .....;
Right Join (Table)
LOAD ID,
MonthYear,
Max(Date) as Date
Resident Table
Group By ID, MonthYear;
There are multiple other ways to do this... but this should give you a table with only max date for each MonthYear and ID....
Ok sunny thanks , let me try is and get bet to you.