Friday, March 27, 2015

Assigning avg to columns and grouping that info by date


DECLARE @TEST1 TABLE ( ACCT INT NOT NULL, D_TIME DATE NULL, TOKEN INT NULL)
INSERT INTO @TEST1 VALUES (1,'01/01/2015', 5),(1,'02/15/2015', 6) ,(2,'02/18/2015', 4) ,(3,'01/11/2015', 3) ,(2,'03/30/2015', 1) ,(3,'03/01/2015', 4) ,(4,'03/01/2015', 1) ,(5,'02/21/2015', 3) ,(6,'02/28/2015', 2)
DECLARE @Start_DT DATE, @End_DT DATE
SET @Start_DT = '01/01/2015'
SET @End_DT = '03/23/2015'

SELECT COUNT(ACCT) AS count, DATEADD(m,DATEDIFF(m,0,D_TIME),0) AS monthStart, CONVERT(DECIMAL(10,2),(AVG(CONVERT(DECIMAL(10,2), NULLIF(TOKEN,0))))) AS avgToken
FROM @TEST1
WHERE (D_TIME BETWEEN @Start_DT AND @End_DT)
AND D_TIME IS NOT null
GROUP BY DATEADD(m,DATEDIFF(m,0,D_TIME),0)
ORDER BY DATEADD(m,DATEDIFF(m,0,D_TIME),0) DESC

All I did here was remove your parameters, and alias your columns.


Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.



No comments:

Post a Comment