How to split the date into monthwise

Question

Using SQL Server 2000

I want to save the details in month wise

User Entry

ID = 001
Fromdate = 01/01/2012
Todate = 29/03/2012

ID = 002
Fromdate = 01/05/2012
Todate = 19/06/2012

ID = 003
Fromdate = 01/04/2012
Todate = 30/04/2012
.....

From the user entry, i want to insert the details in to table1 with the condition like fromdate and todate split in to monthwise (mm/yyyy)

Expected Output

ID period fromdate todate

001 01/2012 01/01/2012 31/01/2012
001 02/2012 01/02/2012 29/02/2012
001 03/2012 01/03/2012 29/03/2012
002 05/2012 01/05/2012 31/05/2012
002 05/2012 01/06/2012 19/06/2012
003 04/2012 01/04/2012 30/04/2012
....
....

fromdate, todate fomat is dd/mm/yyyy period format is mm/yyyy

How to do this in sql server.

Need sql Query Help

Answer 1

First, create and populate a calendar table that has the following columns (at least):

CREATE TABLE dbo.Calendar (
    BaseDate datetime NOT NULL PRIMARY KEY,
    Period char(7) NOT NULL,
    YearNumber int NOT NULL,
    MonthNumber int NOT NULL,
    IsFirstDayOfMonth bit NOT NULL,
    IsLastDayOfMonth bit NOT NULL
)

Then this query will return the data you need, for one pair of fromdate and todate but you can of course put it into a stored procedure and call it once per user input or whatever way you execute SQL from your application:

declare @ID char(3), @FromDate datetime, @ToDate datetime

select @ID ='001', @FromDate = '20120107', @ToDate = '20120917'

select
    @ID as 'ID',
    c.Period,
    case when c.IsFirstDayOfMonth = 0x0 then @FromDate else c.BaseDate end as 'FromDate',
    case when @ToDate < c1.BaseDate then @ToDate else c1.BaseDate end as 'ToDate'
from
    dbo.Calendar c
    join dbo.Calendar c1
    on c.YearNumber = c1.YearNumber and c.MonthNumber = c1.MonthNumber
where
    c.BaseDate between @FromDate and @ToDate and
    (c.IsFirstDayOfMonth = 0x1 or c.BaseDate = @FromDate) and
    c1.IsLastDayOfMonth = 0x1