Help with query that includes data for a trendline

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with query that includes data for a trendline

Author

Topic

jhorten2011
Starting Member

14 Posts

Posted - 2012-01-20 : 10:49:10

Hi guys,

I have the following query that worked perfectly for producing the results I needed for a average daily report. I now have a requirement to modify the query so that when it is used in SSRS2005 the resulting chart will include a trend line.

I've tried the following, so that I get a single resultset that includes colums called [Previous_columnname] to be used for the trend line. The problem is that the Previous_Reject and Previous_Void values are duplicated for each row, while the values for Previous_Paid are different, but I don't believe are correct. These there columns are suppose to return the counts for the previous week.

Can someone please help me to modify the query, so that it works properly?

SELECT
LEFT([Day],3) AS [Day],
AVG([Total Volume M_Active]) AS [Paid],
AVG([Total Volume M_Active_Previous]) AS [Previous_Paid],
AVG([Total Volume M_Reject]) AS [Reject],
AVG([Total Volume M_Reject_Previous]) AS [Previous_Reject],
AVG([Total Volume M_Void]) AS [Void],
AVG([Total Volume M_Void_Previous]) AS [Previous_Void],
AVG([Total Volume M_Active]) + AVG([Total Volume M_Reject]) + AVG([Total Volume M_Void]) AS [Total]

FROM
(
SELECT
DATEPART(weekday,[TimeStamp]) AS [DayNo],
DATENAME(weekday,[TimeStamp]) AS [Day],
COUNT(*) AS [Total Volume M_Active],
(SELECT COUNT(*) FROM CLAIMS.M_Active WHERE [TimeStamp] BETWEEN CAST('2011-03-06 00:00:00.000' AS DATETIME) AND CAST('2011-03-12 23:59:59.999' AS DATETIME)) AS [Total Volume M_Active_Previous],
CAST(NULL AS int) AS [Total Volume M_Reject],
CAST(NULL AS int) AS [Total Volume M_Reject_Previous],
CAST(NULL AS int) AS [Total Volume M_Void],
CAST(NULL AS int) AS [Total Volume M_Void_Previous]
FROM
CLAIMS.M_Active
WHERE
[TimeStamp] BETWEEN CAST('2011-03-13 00:00:00.000' AS DATETIME) AND CAST('2011-03-19 23:59:59.999' AS DATETIME)
GROUP BY
DATEPART(weekday,[TimeStamp]),
DATENAME(weekday,[TimeStamp])
UNION ALL
SELECT DATEPART(weekday,[TimeStamp]),
DATENAME(weekday,[TimeStamp]),
NULL,
COUNT(*),
COUNT(*),
(SELECT COUNT(*) FROM CLAIMS.M_Reject WHERE [TimeStamp] BETWEEN CAST('2011-03-06 00:00:00.000' AS DATETIME) AND CAST('2011-03-12 23:59:59.999' AS DATETIME)),
NULL,
NULL

FROM
CLAIMS.M_Reject
WHERE
[TimeStamp] BETWEEN CAST('2011-03-13 00:00:00.000' AS DATETIME) AND CAST('2011-03-19 23:59:59.999' AS DATETIME)
GROUP BY
DATEPART(weekday,[TimeStamp]),
DATENAME(weekday,[TimeStamp])
UNION ALL
SELECT
DATEPART(weekday,[TimeStamp]),
DATENAME(weekday,[TimeStamp]),
NULL,
NULL,
NULL,
NULL,
COUNT(*),
(SELECT COUNT(*) FROM CLAIMS.M_Void WHERE [TimeStamp] BETWEEN CAST('2011-03-06 00:00:00.000' AS DATETIME) AND CAST('2011-03-12 23:59:59.999' AS DATETIME))
FROM
CLAIMS.M_Void
WHERE
[TimeStamp] BETWEEN CAST('2011-03-13 00:00:00.000' AS DATETIME) AND CAST('2011-03-19 23:59:59.999' AS DATETIME)
GROUP BY
DATEPART(weekday,[TimeStamp]),
DATENAME(weekday,[TimeStamp])
)t

GROUP BY [DayNo],[Day]
ORDER BY [DayNo], [Day] DESC

jhorten2011
Starting Member

14 Posts

Posted - 2012-01-23 : 10:05:26

Can someone help me resolve this?

jhorten2011
Starting Member

14 Posts

Posted - 2012-01-23 : 14:33:04

I've reduced the query in an effort to resolve why this is not giving the correct results. Can anyone see anything wrong with this query and tell me why I get the same value for the Total Volume M_Active_Previous column? I don't understand why it is just repeating the same value rather giving me separate counts for each row like the outer query.

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(*) AS [Total Volume M_Active],
(SELECT COUNT(*) 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])

Results:

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources