August 18th, 2011, 06:40 AM
SQL Query question (count dates for each month)
Hope you can help me with the following, i have the following view availble:
ENTITY | StartDate | EndDate | CodeA | CodeB | Revenue | Currency
AZERT | 01/01/2011 | 02/01/2011 | SU | BOLD | 100 | EUR
AZERT | 28/01/2011 | 02/02/2011 | SU | BOLD | 500 | EUR
Can someone help with a query to pull the data so that I get the following summed?
ENTITY | YYYY.MM | CodeA | CodeB | DAYS | TIMES | Revenue | Currency
AZERT | 2011.01 | SU | BOD | 5 | 2 | 500 | EUR
AZERT | 2011.02 | SU | BOD | 1 | 0 | 100 | EUR
Where YYYY.MM is created depending on the difference between Sdate and EDate.
And DAYS is the variance between the start and end day in the right month
And TIMES is the number of times that the StartDate occurs in that month
Revenue splitted depening how many days there are.