Help understanding query results

jhorten2011 · #1 (**permalink**) 01-24-12, 09:01

I am getting the following results from my query that contains a subquery, but I don't understand why the values in the [Total Volume M_Active_Previous] are being repeated with the same value. I should be getting different values returned for each row like in the [Total Volume M_Active] column.

Can someone please shed some light on why the values are all the same and how I can fix this?

DayNo Day Total Volume M_Active Total Volume M_Active_Previous
6 Friday 11161 72491
7 Saturday 5687 72491
2 Monday 14354 72491
1 Sunday 3966 72491
4 Wednesday 12340 72491
3 Tuesday 12018 72491
5 Thursday 11833 72491

Here is the SQL I'm using.

Code:

DECLARE @StartDate datetime, @EndDate datetime, @Prev_StartDate datetime, @Prev_EndDate datetime
SET @StartDate = '2011-03-13 00:00:00.000'
SET @EndDate = '2011-03-19 23:59:59.999'
SET @Prev_StartDate = '2011-03-06 00:00:00.000'
SET @Prev_EndDate = '2011-03-12 23:59:59.999'

SELECT
	DATEPART(weekday,[TimeStamp]) AS [DayNo],
	DATENAME(weekday,[TimeStamp]) AS [Day],
	COUNT(Claimant_ID) AS [Total Volume M_Active],
	(
		SELECT
			COUNT(Claimant_ID)
		FROM 
			CLAIMS.M_Active 
		WHERE 
			[TimeStamp] BETWEEN @Prev_StartDate AND @Prev_EndDate
	) AS [Total Volume M_Active_Previous]
FROM
	CLAIMS.M_Active
WHERE
	[TimeStamp] BETWEEN @StartDate AND @EndDate
GROUP BY
	DATEPART(weekday,[TimeStamp]),
	DATENAME(weekday,[TimeStamp])

Wim · #2 (**permalink**) 01-24-12, 09:19

Try this:

Code:

SELECT
	DATEPART(weekday,[TimeStamp]) AS [DayNo],
	DATENAME(weekday,[TimeStamp]) AS [Day],
	COUNT(Claimant_ID) AS [Total Volume M_Active],
	T.counter AS [Total Volume M_Active_Previous]
FROM
	CLAIMS.M_Active
	LEFT OUTER JOIN (
			SELECT
				COUNT(Claimant_ID) as counter, 
				DATEPART(weekday,[TimeStamp]) as [DayNo],
				DATENAME(weekday,[TimeStamp]) as [Day]
			FROM 
				CLAIMS.M_Active 
			WHERE 
				[TimeStamp] BETWEEN @Prev_StartDate AND @Prev_EndDate
			GROUP BY
				DATEPART(weekday,[TimeStamp]),
				DATENAME(weekday,[TimeStamp])
			) AS T ON
		DATEPART(weekday,[TimeStamp]) = T.DayNo AND
		DATENAME(weekday,[TimeStamp]) = T.[Day]
WHERE
	[TimeStamp] BETWEEN @StartDate AND @EndDate
GROUP BY
	DATEPART(weekday,[TimeStamp]),
	DATENAME(weekday,[TimeStamp])

jhorten2011 · #3 (**permalink**) 01-24-12, 09:34

Thanks for the quick reply. I tried what you suggested, but it's now giving me the following error.

Quote:

Column 'CLAIMS.M_Active.TimeStamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'm not sure why this is happening, because TimeStamp is already within the GROUP BY.

Wim · #4 (**permalink**) 01-24-12, 09:40

I have corrected my previous post. Give it another try.

jhorten2011 · #5 (**permalink**) 01-24-12, 09:46

I think it is getting closer, I now get the following.

Quote:

Column 'T.counter' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

jhorten2011 · #6 (**permalink**) 01-24-12, 09:48

I added to into the GROUP BY and it now works.

Thank you so much for your help!!!

Internet Services

Web Development

Digital Marketing

Consumer Tech