Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    24

    Unanswered: Help understanding query results

    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])

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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])
    Last edited by Wim; 01-24-12 at 10:39.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2012
    Posts
    24
    Thanks for the quick reply. I tried what you suggested, but it's now giving me the following error.

    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.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I have corrected my previous post. Give it another try.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2012
    Posts
    24
    I think it is getting closer, I now get the following.

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

  6. #6
    Join Date
    Jan 2012
    Posts
    24
    I added to into the GROUP BY and it now works.

    Thank you so much for your help!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •