Didn't you post something just like this a day or two ago? I know I saw some other thread trying to do this exact same thing. I can't however find...

Not to sound unhelpful this has been solved thousands of times all over the internet. A quick google search brought me right to this site even. It all depends on...

Thanks having ddl and data makes this a lot easier to deal with. I think you want something like this.

;with MyNewAverage as

(

select Action, Duration, Curr_date, DATEPART(hour, Curr_date) as HourOfDay, AVG(Duration)/1000...

Hi and welcome to SSC. It is really difficult to offer any assistance without something to work with. By that I mean it would be far better if you could...

If I add an identity to your table variable this works.

Declare @temp Table

(

MyKey int identity,

MyProc INT,

MySeq INT,

MyType VARCHAR(2),

MyTask VARCHAR(20)

)

INSERT INTO @TEMP

SELECT '1','1','a1','this is ' UNION ALL

SELECT '1','2','b2','supposed ' UNION...

Actually my code doesn't work because you don't have anything you can use to sort with. There appears to be nothing you can use as a primary key or even...

This works for your sample data. I changed the column names because dealing with reserved words as column names drives me nutty. 😉

Declare @temp Table

(

MyProc INT,

MySeq INT,

MyType VARCHAR(2),

MyTask VARCHAR(20)

)

INSERT...

Please don't post duplicate threads. The answer that Lowell gave you in the other thread is exactly the way to do this.

Please direct all replies here. http://www.sqlservercentral.com/Forums/Topic1436978-391-1.aspx

Ken Davis (3/29/2013)

---

I want to push my luck and add one more wrinkle. Say the Curr_Date column is DATETIME and the output of the MyAverage CTE express is grouped...

ScottPletcher (3/29/2013)

---

Until you get to SQL 2012, you also have the "quick-and-dirty" version of that, using COMPUTE:

SELECT Curr_date, Action, AVG(Duration)/1000 as Avg_Duration

FROM Table_1

WHERE Curr_date = CAST(GETDATE() as DATE)

GROUP BY...

Ken Davis (3/29/2013)

---

That's nice and elegant. Thanks!

Starting a sql statement with WITH is new to me. I will have to look into that closer but it...

balasach82 (3/29/2013)

---

ColA -- nvarchar

SELECT * from TABLENAME where isdate(ColA)=1 and (ColA)<>'' and DATEDIFF(year,cast(ColA as datetime), GETDATE()) = 1

SELECT * from TABLENAME where isdate(ColA)=1 and (ColA)<>'' and CAST(ColA AS DATETIME)...

I would ask why do you need a function for this at all? All you need to do is directly join to your calendar table. I don't see that this...

Not totally sure how you want the output but this should at least get you started.

;with MyAverage as

(

SELECT Curr_date, Action, AVG(Duration)/1000 as Avg_Duration

FROM Table_1

WHERE Curr_date = CAST(GETDATE() as DATE)

GROUP BY...

You're welcome. Glad that worked for you and thanks for letting me know. 😀