DATEDIFF Group BY Month
-
I've been going over and over this in my head-- maybe somebody else can shed some light on how you'd resolve this-----
I have several rows in a table. Each row has a StartDate and an EndDate. I need to find the difference in dates. DATEDIFF works great and gives me the correct number of elapsed days BUT...
I need to group by months. So if I had 5/15/04 to 7/13/04, I need more than just 59, but something like
5 16
6 30
7 13
This is difficult because there are not rows that I summing up or grouping by-- only a single row with a date range.
Any ideas would be appreciated.
Bryan
-
One way is to find the date difference in date format and then usnig datepart(mm, <calc date>
to use the group by. -
This may seem like a lot, but here it is.
/*********************************************************************************************/
IF (OBJECT_ID('tempdb..#my_table') IS NOT NULL) DROP TABLE #my_table
CREATE TABLE #my_table
(
[id] INTEGER IDENTITY(1,1)
, start_date DATETIME
, end_date DATETIME
 
/*********************************************************************************************/
INSERT INTO #my_table VALUES('5/15/04','7/13/04')
INSERT INTO #my_table VALUES('4/7/04','6/30/04')
INSERT INTO #my_table VALUES('6/10/04','7/1/04')
INSERT INTO #my_table VALUES('5/1/04','6/30/04')
INSERT INTO #my_table VALUES('3/2/04','6/24/04')
/**********************************************************************************************/
IF (OBJECT_ID('tempdb..#temp_results') IS NOT NULL) DROP TABLE #temp_results
CREATE TABLE #temp_results
(
[month] INTEGER
, days INTEGER
, start_date VARCHAR(20)
, end_date VARCHAR(20)
 
/**********************************************************************************************/
DECLARE @loop_start_date DATETIME
DECLARE @loop_end_date DATETIME
DECLARE @incrementing_date DATETIME
DECLARE @this_days_month INTEGER
DECLARE @last_days_month INTEGER
DECLARE @day_counter INTEGER
/**********************************************************************************************/
SET @loop_start_date = NULL
/**********************************************************************************************/
SELECT TOP 1
@loop_start_date = start_date
, @loop_end_date = end_date
FROM
#my_table
ORDER BY
start_date
/**********************************************************************************************/
WHILE @loop_start_date IS NOT NULL
BEGIN
/******************************************************************************/
PRINT 'Start: ' + CONVERT(VARCHAR(10), @loop_start_date, 101)
PRINT 'End: ' + CONVERT(VARCHAR(10), @loop_end_date, 101)
/******************************************************************************/
SET @day_counter = 0
SET @incrementing_date = @loop_start_date
SET @last_days_month = DATEPART(m, @incrementing_date)
SET @this_days_month = DATEPART(m, @incrementing_date)
/******************************************************************************/
WHILE @incrementing_date <= @loop_end_date
BEGIN
SET @this_days_month = DATEPART(m, @incrementing_date)
IF @this_days_month <> @last_days_month
BEGIN
INSERT
#temp_results
VALUES
(
@last_days_month
, @day_counter
, CONVERT(VARCHAR(10), @loop_start_date ,101)
, CONVERT(VARCHAR(10), @loop_end_date ,101)
 
SET @day_counter = 0
END
SET @day_counter = @day_counter + 1
SET @incrementing_date = DATEADD(d, 1, @incrementing_date)
SET @last_days_month = @this_days_month
END
/******************************************************************************/
INSERT
#temp_results
VALUES
(
@last_days_month
, @day_counter
, CONVERT(VARCHAR(10), @loop_start_date ,101)
, CONVERT(VARCHAR(10), @loop_end_date ,101)
 
/******************************************************************************/
DELETE
#my_table
WHERE
start_date = @loop_start_date
AND
end_date = @loop_end_date
/******************************************************************************/
SET @loop_start_date = NULL
/******************************************************************************/
SELECT TOP 1
@loop_start_date = start_date
, @loop_end_date = end_date
FROM
#my_table
ORDER BY
start_date
END
/**********************************************************************************************/
SELECT * FROM #temp_results
/**********************************************************************************************/
-- dropping temp table(s) if needed
IF (OBJECT_ID('tempdb..#temp_results') IS NOT NULL) DROP TABLE #temp_results
IF (OBJECT_ID('tempdb..#my_table') IS NOT NULL) DROP TABLE #my_table
-
/*
Not Bad. What I actually ended up doing was creating a table of every day like this:
OneDate OneMonth OneYear YearMonth
1/1/2000 01 2000 200001
1/2/2000 01 2000 200001
1/3/2000 01 2000 200001
1/4/2000 01 2000 200001
1/5/2000 01 2000 200001
1/6/2000 01 2000 200001
1/7/2000 01 2000 200001
It wasn't too much since there's only 365 days in a year-- 3650 rows for 10 years-- 10950 for 30 years-- not a big table.
Then I looped through each row in the table I needed the calculations for.
*/
IF (OBJECT_ID('tempdb..#my_table') IS NOT NULL) DROP TABLE #my_table
CREATE TABLE #my_table
(
[id] INTEGER IDENTITY(1,1)
, StartDate DATETIME
, EndDate DATETIME
 
SET NOCOUNT ON
/*********************************************************************************************/
INSERT INTO #my_table VALUES('5/15/04','7/13/04')
INSERT INTO #my_table VALUES('4/7/04','6/30/04')
INSERT INTO #my_table VALUES('6/10/04','7/1/04')
INSERT INTO #my_table VALUES('5/1/04','6/30/04')
INSERT INTO #my_table VALUES('3/2/04','6/24/04')
--Then I opened a cursor and looped.
IF (OBJECT_ID('tempdb..#tempDays') IS NOT NULL) DROP TABLE #tempDays
CREATE TABLE #tempDays (
YearMonth varchar(6)
, DayCount int
 
DECLARE @StartDate datetime , @EndDate datetime
DECLARE curInsert cursor for
SELECT StartDate, EndDate
FROM #my_table
open curInsert
fetch next from curInsert into @StartDate , @EndDate
while @@fetch_status = 0
BEGIN
INSERT #tempDays (YearMonth, DayCount)
SELECT YearMonth, Count(*)
FROM tblDays WHERE OneDate BETWEEN @StartDate AND @EndDate
GROUP BY YearMonth
fetch next from curInsert into @StartDate , @EndDate
END
CLOSE curInsert
DEALLOCATE curInsert
select YearMonth, SUM(DayCount) AS DateCount
from #tempDays
GROUP BY YearMonth
-
GOOD JOB!!
I'm glad you got it working
-
I just realized I don't even have to use a cursor, just:
SELECT YearMonth, Count(*) as DayCount
FROM tblDays JOIN #my_table
ON OneDate BETWEEN StartDate AND EndDate
GROUP BY YearMonth
Bryan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply