Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Help understanding sub-query results

9 views
Skip to first unread message

Jayden Horten

unread,
Jan 23, 2012, 4:23:23 PM1/23/12
to
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.

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

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


0 new messages