SQL query to show what has been paid each month
- by Tommy Jakobsen
I'm looking for help to create a query, to solve the following problem:
Let's imagine the row:
Name StartDate EndDate Payed
James 10-10-2010 17-02-2011 860
And heres the schema for the table as requested:
payment_details (name VARCHAR(50) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
payed FLOAT NOT NULL)
Now I need a way to split this row up, so I can see what he pays every month, for his period, a query that returns:
Name Year Month Payed
James 2010 10 172
James 2010 11 172
James 2010 12 172
James 2011 01 172
James 2011 02 172
There are lots of different customers with different StartDate/EndDate and amount payed, so the query has to handle this aswell. How can I do this in SQL (MS SQL Server 2005)?
Help will be much appreciated!
