Month and year query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Month and year query

Author

Topic

bcanonica
Starting Member

35 Posts

Posted - 2009-08-05 : 15:50:50

Not sure if this is possible with my requirements, but thought I would post in case someone has done something similar before. I have a record with title, startDate, endDate, and value. What I need to do without a temporary table or while loop is generate (for performance reasons since I have a large number of these records) a view with title, month, year, and value for each month between the startDate and EndDate of the original record. Below is a pictured example. Top is the record I have and the second table below is the output I am looking for.

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-08-05 : 16:20:59

I'll be honest, that seems like it requires some row-by-row looping to me. You might be able to do a CROSS APPLY on a user-defined table-valued function based off the StartDate and EndDate, but I think you'll find that is significantly slower than a loop.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-05 : 16:31:26

Here are a couple of ways:

--------------------------------------------------
-- Test Data
--------------------------------------------------
DECLARE @Agreement TABLE (Agreement VARCHAR(50), StartDate DATETIME, EndDate DATETIME, Amount MONEY)

INSERT @Agreement
SELECT 'A1', '20090101', '20090401', $100.00

--------------------------------------------------
-- Cross Apply with spt_values table
--------------------------------------------------
SELECT 
	A.Agreement,
	MONTH(M.Date) AS Month,
	YEAR(M.Date) AS Year,
	A.Amount
FROM
	@Agreement AS A
CROSS APPLY
(
	SELECT DATEADD(MONTH, Number, 0) AS Date
	FROM Master..spt_values
	WHERE type = 'P'
	AND Number BETWEEN DATEDIFF(MONTH, 0, A.StartDate) AND DATEDIFF(MONTH, 0, A.EndDate)
) AS M

--------------------------------------------------
-- Join using Table Variable
--------------------------------------------------
DECLARE @DateMonth TABLE (Date DATETIME, PRIMARY KEY (Date))

DECLARE @MinDateMonth DATETIME
DECLARE @MaxDateMonth DATETIME

SELECT
	@MinDateMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, MIN(StartDate)), 0),
	@MaxDateMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, MAX(EndDate)), 0)
FROM 
	@Agreement

WHILE @MinDateMonth <= @MaxDateMonth
BEGIN
	INSERT @DateMonth
	SELECT @MinDateMonth
	
	SET @MinDateMonth = DATEADD(MONTH, 1, @MinDateMonth)
END

SELECT 
	A.Agreement,
	MONTH(M.Date) AS Month,
	YEAR(M.Date) AS Year,
	A.Amount
FROM
	@Agreement AS A
INNER JOIN
	@DateMonth AS M
	ON M.Date BETWEEN A.StartDate AND A.EndDate

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 02:53:21

And there is the CTE approach

;WITH Yak(Agreement, StartDate, EndDate, Amount)
AS (
	SELECT	Agreement,
		StartDate,
		EndDate,
		Amount
	FROM	@Agreement

	UNION ALL

	SELECT	Agreement,
		DATEADD(MONTH, 1, StartDate),
		EndDate,
		Amount
	FROM	Yak
	WHERE	DATEADD(MONTH, 1, StartDate) < EndDate
)

SELECT	Agreement,
	DATEPART(MONTH, StartDate) AS [Month],
	DATEPART(YEAR, StartDate) AS [Year],
	Amount
FROM	Yak

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-06 : 02:54:53


DECLARE @Agreement TABLE (Agreement VARCHAR(50), StartDate DATETIME, EndDate DATETIME, Amount MONEY)

INSERT @Agreement
SELECT 'A1', '20090101', '20090401', $100.00

SELECT 
	A.Agreement,
	month(dateadd(month,number,StartDate)) AS Month,
	year(dateadd(month,number,StartDate)) AS Year,
	A.Amount
FROM
	@Agreement AS A,
	Master..spt_values as M
WHERE type = 'P'
	AND Number BETWEEN 0 and DATEDIFF(MONTH, A.StartDate, A.EndDate)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources