Jayden Horten
unread,Jan 23, 2012, 4:23:23 PM1/23/12You do not have permission to delete messages in this group
Either email addresses are anonymous for this group or you need the view member email addresses permission to view the original message
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])