the following gets first date of the week

DECLARE	@Year SMALLINT = 2011

;WITH cteSource(firstdate)
AS (
	SELECT		CONVERT(varchar(50), DATEADD(DAY, 7 * v.Number, o.Origin), 101) AS firstdate
	FROM		(
				SELECT	DATEADD(DAY, DATEDIFF(DAY, '18991230', STR(@Year, 4, 0) + '0101') / 7 * 7, '18991230')
			) AS o(origin)
	INNER JOIN	master.dbo.spt_values AS v ON v.Type = 'P'
				AND v.Number BETWEEN 0 AND 52
)
SELECT firstdate
FROM	cteSource
WHERE	DATEPART(YEAR, firstdate) = @Year

while the following gets last date

DECLARE	@Year SMALLINT = 2011

;WITH cteSource(lastdate)
AS (
	SELECT		CONVERT(varchar(50), DATEADD(DAY, ((7 * v.Number)-1), o.Origin), 101) AS lastdate
	FROM		(
				SELECT	DATEADD(DAY, DATEDIFF(DAY, '18991223', STR(@Year, 4, 0) + '0101') / 7 * 7, '18991223')
			) AS o(origin)
	INNER JOIN	master.dbo.spt_values AS v ON v.Type = 'P'
				AND v.Number BETWEEN 0 AND 52
)
SELECT	lastdate
FROM	cteSource
WHERE	DATEPART(YEAR, lastdate) = @Year

i wanted to join the query to get the output in 1 table with 2 columns. while i tried using

select fistdate,lastdate from(query1)a,(query2)b

am getting the following output :

firstdate | lastdate
01/01/2011 | 01/07/2011
01/08/2011 | 01/07/2011
01/15/2011 | 01/07/2011
. | .
. | .
01/01/2011 | 01/14/2011
01/08/2011 | 01/14/2011


but my expected output is :

firstdate | lastdate
01/01/2011 | 01/07/2011
01/08/2011 | 01/14/2011
01/15/2011 | 01/21/2011


please help.. thanks in advance.

This is a cartesian product. It happens because you didn't use a criteria when joining your 2 queries, so every value from query1 gets "joined" to every value from query2.

Either find a common field to use as key for your join or create one.
Read here on how you can create a field to use: http://msdn.microsoft.com/en-us/library/ms186734.aspx

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.