Declare @date table(d datetime)
Declare @startdate datetime
Declare @enddate datetime
set @startdate='20090101'
set @enddate='20090301'
While @startdate<=@enddate
Begin
	Insert into @date values (@startdate)
	set @startdate=@startdate+1
End
Select count(d) as NoOfDays,datename(month,d) as monthname from @date group by datename(month,d) 

quote:

---

Originally posted by gjohnso7

I am comparing 2 Dates (Start_Datetime and Stop_Datetime). I am trying to isolate how much of the distance in Days occurred in a particular month.

For example: Start_Datetime 1/1/2009 and Stop_Datetime 3/1/2009.

The Breakout in each column would be:
January = 31
February = 28
March = 1

---

Select max(Case When monthname ='January' then NoofDays Else Null End)January,
max(case........,
max(case....
from
(Select count(d) as NoOfDays,datename(month,d) as monthname 
from @date 
Group by datename(month,d))Z

quote:

---

Originally posted by gjohnso7

I am comparing 2 Dates (Start_Datetime and Stop_Datetime). I am trying to isolate how much of the distance in Days occurred in a particular month.

For example: Start_Datetime 1/1/2009 and Stop_Datetime 3/1/2009.

The Breakout in each column would be:
January = 31
February = 28
March = 1

---

DECLARE @StartDate datetime,@EndDate= datetime

SELECT @StartDate='1/1/2009',@EndDate='3/1/2009'

;WIth Date_CTE (DateVal)
AS
(
SELECT @StartDate
UNION ALL
SELECT DATEADD(dd,1,DateVal)
FROM Date_CTE
WHERE DATEADD(dd,1,DateVal)<=@EndDate
)

SELECT DATENAME(mm,DateVal),COUNT(*)
FROM Date_CTE
GROUP BY DATENAME(mm,DateVal),DATEPART(mm,DateVal)
ORDER BY DATEPART(mm,DateVal)

OPTION (MAXRECURSION 0)

quote:

---

Originally posted by gjohnso7

How do I populate the Days that fall into each months results into the following table. Each row would represent a individual account and each row may have results that fall under multiple month columns.

TABLE [dbo].[Monthy_Days](
[Account_Number] [varchar](80) NULL,
[Admit_Date] [datetime] NULL,
[Disch_Date] [datetime] NULL,
[Jan_Days] [float] NULL,
[Feb_Days] [float] NULL,
[Mar_Days] [float] NULL

Results should look like so:

[Account_Number] | [Admit_Date]|[Disch_Date]|[Jan_Days]|[Feb_Days]|[Mar_Days]
A12345 | 1/1/2009 | 3/1/2009 | 31 | 28 | 1


I have already attempted a SQL View, but have been unable to get to where I need to be. In the end, the results will be used to show volume trends month over month.

---

;WIth Date_CTE ([Account_Number],
	[Admit_Date],
	[Disch_Date],DateVal)
AS
(
SELECT [Account_Number],
	[Admit_Date],
	[Disch_Date],
        [Admit_Date]
FROM   [dbo].[Monthy_Days]
UNION ALL
SELECT [Account_Number],
	[Admit_Date],
	[Disch_Date],
        DATEADD(dd,1,DateVal)
FROM Date_CTE
WHERE DATEADD(dd,1,DateVal)<=[Disch_Date]
)

SELECT [Account_Number],
	[Admit_Date],
	[Disch_Date],
[January] AS Jan_days,
[February] AS Feb_days,
....
FROM
(SELECT [Account_Number],
	[Admit_Date],
	[Disch_Date],
        DateVal,
        DATENAME(mm,DateVal) as Mnth
 FROM Date_CTE)m
PIVOT(COUNT(DateVal) FOR Mnth IN ([January],[February],[March],...))p

OPTION (MAXRECURSION 0)

quote:

---

Originally posted by gjohnso7

I receive the following error message:

Msg 325, Level 15, State 1, Line 33
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

Query is as follows:

SELECT
Account_Number,
Admit_Date,
Disch_Date
FROM Monthy_Days
UNION ALL
SELECT
Account_Number,
Admit_Date,
Disch_Date,
DATEADD(dd,1,DateVal)
FROM Date_CTE
WHERE DATEADD(dd,1,DateVal)<=[Disch_Date])

SELECT [Account_Number],
[Admit_Date],
[Disch_Date],
[January] AS Jan_days,
[February] AS Feb_days

FROM
(SELECT [Account_Number],
[Admit_Date],
[Disch_Date],
DateVal,
DATENAME(mm,DateVal) as Mnth
FROM Date_CTE)m
PIVOT(COUNT(DateVal) FOR Mnth IN ([January],[February],[March],...))p

OPTION (MAXRECURSION 0)

---

EXEC sp_dbcmptlevel 'yourdb',90
GO

quote:

---

Originally posted by visakh16

by ... i meant other columns. did you replace it by other month names in both places?

---

quote:

---

Originally posted by visakh16

read about PIVOT and CTE in books online

---

DECLARE	@Start DATETIME,
	@Stop DATETIME

SELECT	@Start = '20090101',
	@Stop = '20090301'

SELECT	SUM(CASE
		WHEN Number = 1 AND @Start BETWEEN p1 AND p2 THEN DATEDIFF(DAY, @Start, p2) + 1
		WHEN Number = 1 AND @Stop BETWEEN p1 AND p2 THEN DATEDIFF(DAY, p1, @Stop) + 1
		WHEN Number = 1 AND @Start < p1 AND @Stop > p2 THEN DATEDIFF(DAY, p1, p2) + 1
		ELSE 0
	END) AS January,
	SUM(CASE
		WHEN Number = 2 AND @Start BETWEEN p1 AND p2 THEN DATEDIFF(DAY, @Start, p2) + 1
		WHEN Number = 2 AND @Stop BETWEEN p1 AND p2 THEN DATEDIFF(DAY, p1, @Stop) + 1
		WHEN Number = 2 AND @Start < p1 AND @Stop > p2 THEN DATEDIFF(DAY, p1, p2) + 1
		ELSE 0
	END) AS February,
	SUM(CASE
		WHEN Number = 3 AND @Start BETWEEN p1 AND p2 THEN DATEDIFF(DAY, @Start, p2) + 1
		WHEN Number = 3 AND @Stop BETWEEN p1 AND p2 THEN DATEDIFF(DAY, p1, @Stop) + 1
		WHEN Number = 3 AND @Start < p1 AND @Stop > p2 THEN DATEDIFF(DAY, p1, p2) + 1
		ELSE 0
	END) AS March,
	SUM(CASE
		WHEN Number = 4 AND @Start BETWEEN p1 AND p2 THEN DATEDIFF(DAY, @Start, p2) + 1
		WHEN Number = 4 AND @Stop BETWEEN p1 AND p2 THEN DATEDIFF(DAY, p1, @Stop) + 1
		WHEN Number = 4 AND @Start < p1 AND @Stop > p2 THEN DATEDIFF(DAY, p1, p2) + 1
		ELSE 0
	END) AS April,
	SUM(CASE
		WHEN Number = 5 AND @Start BETWEEN p1 AND p2 THEN DATEDIFF(DAY, @Start, p2) + 1
		WHEN Number = 5 AND @Stop BETWEEN p1 AND p2 THEN DATEDIFF(DAY, p1, @Stop) + 1
		WHEN Number = 5 AND @Start < p1 AND @Stop > p2 THEN DATEDIFF(DAY, p1, p2) + 1
		ELSE 0
	END) AS May,
	SUM(CASE
		WHEN Number = 6 AND @Start BETWEEN p1 AND p2 THEN DATEDIFF(DAY, @Start, p2) + 1
		WHEN Number = 6 AND @Stop BETWEEN p1 AND p2 THEN DATEDIFF(DAY, p1, @Stop) + 1
		WHEN Number = 6 AND @Start < p1 AND @Stop > p2 THEN DATEDIFF(DAY, p1, p2) + 1
		ELSE 0
	END) AS June,
	SUM(CASE
		WHEN Number = 7 AND @Start BETWEEN p1 AND p2 THEN DATEDIFF(DAY, @Start, p2) + 1
		WHEN Number = 7 AND @Stop BETWEEN p1 AND p2 THEN DATEDIFF(DAY, p1, @Stop) + 1
		WHEN Number = 7 AND @Start < p1 AND @Stop > p2 THEN DATEDIFF(DAY, p1, p2) + 1
		ELSE 0
	END) AS July,
	SUM(CASE
		WHEN Number = 8 AND @Start BETWEEN p1 AND p2 THEN DATEDIFF(DAY, @Start, p2) + 1
		WHEN Number = 8 AND @Stop BETWEEN p1 AND p2 THEN DATEDIFF(DAY, p1, @Stop) + 1
		WHEN Number = 8 AND @Start < p1 AND @Stop > p2 THEN DATEDIFF(DAY, p1, p2) + 1
		ELSE 0
	END) AS August,
	SUM(CASE
		WHEN Number = 9 AND @Start BETWEEN p1 AND p2 THEN DATEDIFF(DAY, @Start, p2) + 1
		WHEN Number = 9 AND @Stop BETWEEN p1 AND p2 THEN DATEDIFF(DAY, p1, @Stop) + 1
		WHEN Number = 9 AND @Start < p1 AND @Stop > p2 THEN DATEDIFF(DAY, p1, p2) + 1
		ELSE 0
	END) AS September,
	SUM(CASE
		WHEN Number = 10 AND @Start BETWEEN p1 AND p2 THEN DATEDIFF(DAY, @Start, p2) + 1
		WHEN Number = 10 AND @Stop BETWEEN p1 AND p2 THEN DATEDIFF(DAY, p1, @Stop) + 1
		WHEN Number = 10 AND @Start < p1 AND @Stop > p2 THEN DATEDIFF(DAY, p1, p2) + 1
		ELSE 0
	END) AS October,
	SUM(CASE
		WHEN Number = 11 AND @Start BETWEEN p1 AND p2 THEN DATEDIFF(DAY, @Start, p2) + 1
		WHEN Number = 11 AND @Stop BETWEEN p1 AND p2 THEN DATEDIFF(DAY, p1, @Stop) + 1
		WHEN Number = 11 AND @Start < p1 AND @Stop > p2 THEN DATEDIFF(DAY, p1, p2) + 1
		ELSE 0
	END) AS November,
	SUM(CASE
		WHEN Number = 12 AND @Start BETWEEN p1 AND p2 THEN DATEDIFF(DAY, @Start, p2) + 1
		WHEN Number = 12 AND @Stop BETWEEN p1 AND p2 THEN DATEDIFF(DAY, p1, @Stop) + 1
		WHEN Number = 12 AND @Start < p1 AND @Stop > p2 THEN DATEDIFF(DAY, p1, p2) + 1
		ELSE 0
	END) AS December
FROM	(
		SELECT	Number,
			DATEADD(MONTH, 12 * DATEPART(YEAR, @Start) - 22801 + Number, 0) AS p1,
			DATEADD(MONTH, 12 * DATEPART(YEAR, @Start) - 22800 + Number, -1) AS p2
		FROM	master..spt_values
		WHERE	Type = 'P'
			AND Number BETWEEN 1 AND 12
	) AS d