January 25, 2007 at 4:49 am
How can I break a date range into multiple parts?
Here is the DDL for it....
IF OBJECT_ID('tempdb..#myTable') IS NOT NULL
DROP TABLE #myTable
CREATE TABLE #myTable
(
RecordID int identity(1,1) not null primary key clustered,
EmpID int not null,
Sal varchar(10) not null,
PeriodFrom smalldatetime not null,
PeriodTo smalldatetime not null,
)
INSERT INTO #myTable( EmpID, Sal, PeriodFrom, PeriodTo )
SELECT 1, 'PAY_1', '20060101', '20060114'
UNION ALL
SELECT 1, 'PAY_1', '20060115', '20060331'
SELECT * FROM #myTable ORDER BY EmpID, Sal, PeriodFrom
IF OBJECT_ID('tempdb..#myTable') IS NOT NULL
DROP TABLE #myTable
I need the break up of these periods for each month...i.e
The first record which is from 1-Jan-2006 to 14-Jan-2006 will stay the same is it falls in the same month. Now the second record i.e. 15-Jan-2006 to 31-Mar-2006 which spans over 2 months. For this, I need to break this period for each month...
15-Jan-2006 to 31-Jan-2006
1-Feb-2006 to 28-Feb-2006
1-Mar-2006 to 31-Mar-2006
--Ramesh
January 25, 2007 at 7:04 am
Join to CALENDAR table.
_____________
Code for TallyGenerator
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy